mysql的锁机制学习

1、不同引擎支持不同的锁机制

innodb支持表级也支持行级锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

2、查询表级锁争用情况

(root:oratest_0112:Tue Oct 20 13:00:39 2020)[sakila]> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 141   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 1     |
| Table_open_cache_misses    | 1     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+

如果Table_locks_waited 的值较高，说明存在着较为严重的表级锁争用。

3、并发事务处理带来的问题 

1）更新丢失
多个事务选择同一行更新，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题。比如两个人同时编辑同一份文档。

（2）脏读
一个事务读取到了另外一个事务中未提交的数据

（3）不可重复读
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据时，却发现读出的数据已经发生了改变或某些记录被删除。

（4）幻读
一个事务按相同的查询条件查询数据时，发现其他事务插入了满足其查询条件的新数据。

更新丢失是应用程序的责任，需要应用程序对要更新的数据加必要的锁解决。
脏读、不可重复读、幻读都是数据库读一致性的问题，必须由数据库提供机制解决。
数据库实现事务隔离的方式基本分两种：
1、在读取数据前，对其加锁，阻止其他事务对数据修改；
2、不加锁，通过一定机制生成一个数据请求时间点的一致性数据快照，用这个快照提供一定级别的一致性读取。MVCC。

4、获取innodb行锁争用情况

(root:oratest_0112:Tue Oct 20 13:44:11 2020)[sakila]> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

5、innodb的行锁模式及加锁方法

innodb实现的行锁类型
（1）共享锁（S）:允许一个事务去读取一行，阻止其它事务获得相同数据集的排他锁。
（2）排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他写锁。
（3）意向共享锁（IS）
（4）意向排他锁（IX）

显式加锁：
共享锁（S）：select * from table_name where ... lock in share mode
排他锁（X）：select * from table_name where ... for update

6、innodb行锁的实现方式

innodb行锁是通过给索引上的索引项加锁实现的。有3种情形
（1）record lock:对索引项加锁
（2）gap lock:对索引项之间的间隙，第一条记录前的间隙或是最后一条记录后的间隙加锁。
（3）next-key lock:前两种的组合，对记录及其前面的间隙加锁。

注意：innodb行锁的实现特点，如果不通过索引条件检索数据，则innodb将对表中的所有记录加锁，实际效果和表锁一样。

案例：不通过索引条件查询时，innodb会锁定表中所有记录

a.测试表结构如下：
(root:oratest_0112:Wed Oct 21 09:15:47 2020)[kafka_test]> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `utime` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


b.插入测试数据
insert into test1 select 1,now() from dual;
insert into test1 select 2,now() from dual;
insert into test1 select 3,now() from dual;
insert into test1 select 4,now() from dual;

(root:oratest_0112:Wed Oct 21 09:17:06 2020)[kafka_test]> select * from test1;
+------+---------------------+
| id   | utime               |
+------+---------------------+
|    1 | 2020-10-21 09:16:56 |
|    2 | 2020-10-21 09:17:01 |
|    3 | 2020-10-21 09:17:03 |
|    4 | 2020-10-21 09:17:06 |
+------+---------------------+
4 rows in set (0.00 sec)


c.会话1查询数据
(root:oratest_0112:Wed Oct 21 09:19:06 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Wed Oct 21 09:19:15 2020)[kafka_test]> select * from test1 where id=1 for update;
+------+---------------------+
| id   | utime               |
+------+---------------------+
|    1 | 2020-10-21 09:16:56 |
+------+---------------------+
1 row in set (0.00 sec)


d.会话2查询数据
(root:oratest_0112:Wed Oct 21 09:20:37 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Wed Oct 21 09:20:39 2020)[kafka_test]>
(root:oratest_0112:Wed Oct 21 09:20:40 2020)[kafka_test]> select * from test1 where id=2 for update;


等待的状态



e.在会话1提交之后，会话2的查询才会返回结果。

结论：会话2请求的是其他行的排他锁，但是出现了锁等待，原因就是在没有索引的前提下，innodb会对所有记录都加锁。
案例：测试通过索引条件查询，加锁的情形

a.给上述测试表test1增加主键属性
alter table test1 add primary key(id);

(root:oratest_0112:Wed Oct 21 09:24:10 2020)[kafka_test]> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `utime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


b.会话1查询数据
(root:oratest_0112:Wed Oct 21 09:21:27 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Wed Oct 21 09:24:43 2020)[kafka_test]> select * from test1 where id=1 for update;
+----+---------------------+
| id | utime               |
+----+---------------------+
|  1 | 2020-10-21 09:16:56 |
+----+---------------------+
1 row in set (0.00 sec)


c.会话2查询数据
(root:oratest_0112:Wed Oct 21 09:24:16 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Wed Oct 21 09:25:12 2020)[kafka_test]> select * from test1 where id=2 for update;
+----+---------------------+
| id | utime               |
+----+---------------------+
|  2 | 2020-10-21 09:17:01 |
+----+---------------------+
1 row in set (0.00 sec)


d.会话3查询数据
(root:oratest_0112:Wed Oct 21 09:25:17 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Wed Oct 21 09:31:21 2020)[kafka_test]> select * from test1 where id=1 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
(root:oratest_0112:Wed Oct 21 09:31:29 2020)[kafka_test]> select * from test1 where id=1 for update;


可以看到会话3请求的排他锁和会话1请求的排它锁访问的数据集是一样的，这个时候会话3需要等待。

结论：innodb的行锁是针对索引添加的，不是针对记录行的，所以访问数据使用的是相同的索引键，是会出现索引冲突的。

7.innodb next-key锁机制，以及innnodb使用next-key锁的原因

next-key锁：使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙，
innodb会对这个间隙加锁，这种锁机制就叫next-key锁。

案例：emp表有101条记录，empid的值为1,2,... 100,101

select * from emp where empid>100 for update;

这个sql是一个范围查询，innodb不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的间隙加锁。

加next-key锁可以防止幻读，以满足相关隔离级别的要求，比如上面的sql如果不加间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述的查询sql，就会有幻读。

innodb除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求一个不存在的记录加锁，innodb也会使用间隙锁。

案例：会话1

(root:oratest_0112:Fri Oct 23 09:30:08 2020)[(none)]> set session transaction isolation  level repeatable read;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:31:13 2020)[(none)]> select @@tx_isolation;
1 row in set (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:31:21 2020)[(none)]> nopager
PAGER set to stdout
(root:oratest_0112:Fri Oct 23 09:31:23 2020)[(none)]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
(root:oratest_0112:Fri Oct 23 09:25:33 2020)[kafka_test]> select * from test1;
+----+---------------------+
| id | utime               |
+----+---------------------+
|  1 | 2020-10-21 09:16:56 |
|  2 | 2020-10-21 09:17:01 |
|  3 | 2020-10-21 09:17:03 |
|  4 | 2020-10-21 09:17:06 |
+----+---------------------+
4 rows in set (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:25:36 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:25:53 2020)[kafka_test]> select * from test1 where id=6 for update;   （查询一个不存在的记录）
Empty set (0.00 sec)

会话2

(root:oratest_0112:Fri Oct 23 09:33:09 2020)[kafka_test]> set session transaction isolation  level repeatable read;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:33:10 2020)[kafka_test]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:33:14 2020)[kafka_test]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

(root:oratest_0112:Fri Oct 23 09:33:19 2020)[kafka_test]> insert into test1 values(8,now());


此时会话2阻塞等待的状态。

会话1

执行rollback

会话2

(root:oratest_0112:Fri Oct 23 09:33:19 2020)[kafka_test]> insert into test1 values(8,now());
Query OK, 1 row affected (29.80 sec)