Mysql InnoDB的锁定机制实例详解

本文整理自网络，侵删。

1.InnoDB的锁定机制

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

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

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

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

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

数据库隔离级别的比较

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通过索引更新数据，也是上排他锁，行锁

阅读剩余部分

相关阅读 >>

mysql主从复制原理是什么

在 mysql 中，如何计算一组数据的中位数

mysql事务中四种隔离级别

mysql数学函数有哪些？

mysql update 语句一个“经典”的坑

mysql数据库压缩版本安装与配置详细教程

mysql存储过程太慢怎么办

通过grep 获取mysql错误日志信息的方法代码示例

使用phpmyadmin怎样修改mysql密码

mysql8忘记密码的快速解决方法

更多相关阅读请进入《mysql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » Mysql InnoDB的锁定机制实例详解