Mysql InnoDB的锁定机制实例详解

mysql innodb锁定 mysqlinnodb mysql排他锁 2022-05-18 01:05:25 531人浏览八月长安

摘要

1.InnoDB的锁定机制 InnoDB存储引擎支持行级锁，支持事务处理，事务是有一组sql语句组成的逻辑处理单元，他的ACID特性如下：原子性(Atomicity): 事务具有原子不可分割的特性，要么一起

1.InnoDB的锁定机制

InnoDB存储引擎支持行级锁，支持事务处理，事务是有一组sql语句组成的逻辑处理单元，他的ACID特性如下：

原子性(Atomicity): 事务具有原子不可分割的特性，要么一起执行，要么都不执行。
一致性(Consistency): 在事务开始和事务结束时，数据都保持一致状态。
隔离性(Isolation): 在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。
持久性(Durability): 在事务完成后，数据将会被持久化到数据库中。

并发事务能提高数据库资源的利用率，提高了数据库的事务吞吐量，但并发事务也存在一些问题，主要包括：

更新丢失(Lost Update): 两个事务更新同一条数据，但第二个事务中途失败退出，导致两个修改都失效了；因为此时数据库没有执行任何锁操作，并发事务并没有被隔离。(现代数据库已经不存在这种问题)
脏读(Dirty Reads): 一个事务读了某行数据，但是另一个事务已经更新了这行数据，这是非常危险的，很可能导致所有的操作被回滚。
不可重复读: 一个事务对一行数据重复读取两次(多次)，可是得到了不同的结果,在两次读取过程中，有可能存在另一个事务对数据进行了修改。
幻读:事务在操作过程中进行两次查询，第二次查询结果包含了第一次没有出现的数据。出现幻读的主要原因是两次查询过程中另一个事务插入新的数据。

数据库并发中的“更新丢失”通常应该是完全避免的，但防止更新丢失数据，并不能单靠数据库事务控制来解决，需要应用程序对要更新的数据加必要的锁来解决，而以上出现的数据库问题都必要由数据库提供一定的事务隔离机制来解决。为了避免数据库事务并发带来的问题，在标准SQL规范中定义了4个事务的隔离级别，不同的隔离级别对事务处理不一样。

数据库隔离级别的比较

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读 (Read uncommitted)	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读 (Read committed)	语句级	否	是	是
可重复读 (Repeatable read)	事务级	否	否	是
可序列化 (Serializable)	最高级别，事务级	否	否	否

InnoDB存储引擎实现了4中行锁，分别时共享锁(S)、排他锁(X)、意向共享锁(IS)、意向排他锁(IX)。

共享锁：大家都能读，但是不能改，只有其中一个独占共享锁时候才能改；
排它锁：我要改，你们都不能改，也不能读(但可以mvcC快照读)

理解意向锁

意向锁不会和行级的S和X锁冲突，只会和表级的S和X锁冲突

意向锁是为了避免遍历全部行锁

考虑这个例子：

事务A锁住了表中的一行，让这一行只能读，不能写。

之后，事务B申请整个表的写锁。

如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？

step1：判断表是否已被其他事务用表锁锁表

step2：判断表中的每一行是否已被行锁锁住。

注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。

于是就有了意向锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成

step1：不变

step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

1.1通过索引检索数据，上共享锁，行锁(如果不通过索引，会使用表锁)


1.1通过索引检索数据，上共享锁，行锁
SessionA                    SessionB
Mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
对主键索引上共享锁,其他事务也能获取到共享锁
mysql> select * from test where         
id=1 lock in share mode;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom |  100 |   1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------
                        事务B也能继续加共享锁
                        mysql> select * from test where         
                        id=1 lock in share mode;
                        +----+------+-------+-------+
                        | id | name | money | level |
                        +----+------+-------+-------+
                        | 1 | tom |  100 |   1 |
                        +----+------+-------+-------+
                        1 row in set (0.01 sec)
                        但无法更新，因为事务A也加了共享锁
                        mysql> update test set level=11 where id=1;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        MORE:
                        无法加排它锁
                        select *from test where id=1 for update;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        可以更新未加锁的，比如
                        mysql> update test set level=11 where id=2;
                        Query OK, 1 row affected (0.00 sec)
                        Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
事务A也无法更新，因为事务B加了共享锁
mysql> update test set level=11 where id=1;
ERROR 1205 (HY000): Lock wait timeout excee
ded; try restarting transaction
--------------------------------------------------------------------------------
                        任意一个释放共享锁，则独占共享锁的事务可以更新
                        mysql> commit;
                        Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------
事务B释放锁，事务A独占，可以更新了
mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

1.2通过索引检索数据，上排他锁，行锁


1.2通过索引检索数据，上排他锁，行锁
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
对主键索引上排他锁,其他事务也能获取到共享锁
mysql> select *from test where
id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom |  100 |   1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------
                        事务B则不能继续上排它锁，会发生等待
                        mysql> select *from test where id=1 for update;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        MORE:
                        也不能更新，因为更新也是上排它锁
                        mysql> update test set level=2 where id=1;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        也不能上共享锁
                        mysql> select * from test where level=1 lock in share mode;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
--------------------------------------------------------------------------------
事务A可以更新
mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
释放排它锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------
                        事务A释放锁，事务B就可以加排它锁了
                        mysql> select * from test where id=1 for update;
                        +----+------+-------+-------+
                        | id | name | money | level |
                        +----+------+-------+-------+
                        | 1 | tom |  100 |   1 |
                        +----+------+-------+-------+
                        1 row in set (0.00 sec)

1.3通过索引更新数据，也是上排他锁，行锁

对于 update,insert,delete 语句会自动加排它锁


1.3通过索引更新数据，也是上排他锁，行锁
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
更新id=1的行，就给该行上了排它锁，其他事务
无法更新该行
mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
                        事务B则不能更新id=1的行，会发生等待
                        mysql> update test set level=21 where id=1;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        MORE:
                        也不能上排它锁
                        mysql> select *from test where id=1 for update;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        也不能上共享锁
                        mysql> select * from test where level=1 lock in share mode;
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
--------------------------------------------------------------------------------
释放排它锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------
                        事务A释放锁，事务B就可以加排它锁了
                        mysql> select * from test where id=1 for update;
                        +----+------+-------+-------+
                        | id | name | money | level |
                        +----+------+-------+-------+
                        | 1 | tom |  100 |   11|
                        +----+------+-------+-------+
                        1 row in set (0.00 sec)

2.1脏读


//脏读
//2.1脏读
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
set session transaction isolation        set session transaction isolation level read uncommitted;
level read uncommitted;             Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)      
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
                        mysql> update test set level=100 where id=1;
                        Query OK, 1 row affected (0.00 sec)
                        Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
//脏读
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom |  100 |  100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
                        rollback;
                        Query OK, 0 rows affected (0.01 sec)
                        
                        mysql> select *from test where id=1;
                        +----+------+-------+-------+
                        | id | name | money | level |
                        +----+------+-------+-------+
                        | 1 | tom |  100 |   1 |
                        +----+------+-------+-------+
                        1 row in set (0.00 sec)

2.2不可重复读


2.2不可重复读
//脏读
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
set session transaction isolation        set session transaction isolation level read uncommitted;
level read uncommitted;             Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)      
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
                        mysql> update test set level=100 where id=1;
                        Query OK, 1 row affected (0.00 sec)
                        Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom |  100 |  100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
                        mysql> update test set level=1000 where id=1;
                        Query OK, 1 row affected (0.00 sec)
                        Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
//不可重复读
//读三次，第一次是level是1，第二次是100，第三次是1000
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom |  100 |  1000|
+----+------+-------+-------+
1 row in set (0.00 sec)

2.3幻读


//2.3幻读
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
set session transaction isolation        set session transaction isolation level read uncommitted;
level read uncommitted;             Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)      
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
                        mysql> update test set level=100 where id=1;
                        Query OK, 1 row affected (0.00 sec)
                        Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom |  100 |  100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
                        mysql> insert into test (name, money，level) VALUES                             ('tim',250，4);
                        Query OK, 1 row affected (0.01 sec)
--------------------------------------------------------------------------------
//幻读
//读两次，第二次多了tim的数据
//如果是rr级别，需要使用当前读select * from test lock in share mode;否则因为MVCC的缘故，是读不到tim的
mysql> select * from test;
+----+-------+-------+-------+
| id | name | money | level |
+----+-------+-------+-------+
| 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |
| 4 | tim  |  250 |   4 |
+----+-------+-------+-------+
4 row in set (0.00 sec)

3 间隙锁(Net-Key锁)

MVCC使RR级别下，事务当前读，来避免了读情况下的幻读问题，但如果写更新时候呢？在范围更新的同时，往范围内插入新数据，怎么办？

于是就有了间隙锁，在更新某个区间数据时，将会锁定这个区间的所有记录。例如update XXX where id between 1 and 100，就会锁住id从1到100之间的所有的记录。值得注意的是，在这个区间中假设某条记录并不存在，该条记录也会被锁住，这时，如果另一个事务往这个区间添加数据，就必须等待上一个事务释放锁资源。

使用间隙锁有两个目的，一是防止幻读；二是满足其恢复和赋值的需求。

3.1范围间隙锁，显式左开右闭区间


//间隙锁(Net-Key锁) 范围间隙锁，左开右闭区间
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
     
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=0
where money between 0 and 200;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
理论上应该锁定[0,300)这个区间
--------------------------------------------------------------------------------
                        插入money=0等待
                        mysql> insert into test (name, money,level) VALUES ('tim',0,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=90等待
                        mysql> insert into test (name, money,level) VALUES ('tim',90,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=100等待
                        mysql> insert into test (name, money,level) VALUES ('tim',100,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=299等待
                        mysql> insert into test (name, money,level) VALUES ('tim',299,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=300 ok
                        mysql> insert into test (name, money,level) VALUES ('tim',300,0);
                        Query OK, 1 row affected (0.00 sec)

3.2单个间隙锁隐式区间

上小节是指定update某个区间，那如果说是只update一个值呢？还会有间隙锁么？


//间隙锁(Net-Key锁) 单个间隙锁，左开右闭区间
SessionA                    SessionB
mysql> set autocommit=0;            mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)      Query OK, 0 rows affected (0.02 sec)
     
mysql> select * from test;           mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+         +----+-------+-------+-------+              
| id | name | money | level |         | id | name | money | level |
+----+-------+-------+-------+         +----+-------+-------+-------+
| 1 | tom  |  100 |   1 |         | 1 | tom  |  100 |   1 |
| 2 | jack |  200 |   2 |         | 2 | jack |  200 |   2 |
| 3 | lucas |  300 |   3 |         | 3 | lucas |  300 |   3 |
+----+-------+-------+-------+         +----+-------+-------+-------+
3 rows in set (0.00 sec)            3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=0
where money = 200;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
理论上应该锁定[0,300)这个区间
--------------------------------------------------------------------------------
                        插入money=0 ok
                        mysql> insert into test (name, money,level) VALUES ('tim',0,0);
                        Query OK, 1 row affected (0.00 sec)
                        
                        插入money=90 ok
                        mysql> insert into test (name, money,level) VALUES ('tim',90,0);
                        Query OK, 1 row affected (0.00 sec)
                        
                        插入money=100等待
                        mysql> insert into test (name, money,level) VALUES ('tim',100,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=150等待
                        mysql> insert into test (name, money,level) VALUES ('tim',150,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=200等待
                        mysql> insert into test (name, money,level) VALUES ('tim',200,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=240等待
                        mysql> insert into test (name, money,level) VALUES ('tim',240,0);
                        ERROR 1205 (HY000): Lock wait timeout exceeded;
                        try restarting transaction
                        
                        插入money=300 ok
                        mysql> insert into test (name, money,level) VALUES ('tim',300,0);
                        Query OK, 1 row affected (0.00 sec)

当不指定区间时，隐式的区间为索引B+数前后两个节点的值所确定的区间，也是左开右闭，对于上述例子，就是[0,300)这个区间。