浅析MySQL的锁机制

简单来说，数据库锁定机制是数据库为保证数据的一致性而设计的规则，使各种共享资源在并发访问访问中有序；对于任何数据库，都需要有相应的锁定机制，Mysql也不例外。

Mysql几种锁定机制类型

MySQL 各存储引擎采用行级锁定、页级锁定和表级锁定三种锁定机制。

1.行级锁定

锁定对象的粒度很小，只锁定当前行程，因此锁定资源竞争的可能性最小，可以给应用程序尽可能大的并发处理能力；缺点是获取锁释放锁更频繁，系统消耗更大，行级锁也最有可能发生死锁； Innnodb存储引擎和NDB主要是行级锁定 Cluster存储引擎；

2.页级锁定

锁定粒度介于行级锁定和表级锁之间，每页有多行数据，并发处理能力和获取锁定所需的资源成本在两者之间； 页面锁定主要是BerkeleyDBB 存储引擎；

3.表级锁定

整个表一次锁定。锁定机制的最大特点是实现逻辑非常简单，对系统的负面影响最小，可以避免死锁问题；缺点是锁定资源的概率最高，并发处理能力最低； MyISAM主要用于表级锁定，Memory，一些非事务性存储引擎，如CSV。

本文重点介绍了Innodb存储引擎使用的行级锁定；

两段锁协议(2PL)

两个锁协议规定了所有事务应遵守的规则： 1.在读写任何数据之前，首先要申请并封锁数据； 2.释放封锁后，事务不再申请或获得任何其他封锁；

即事务的执行分为两个阶段：

第一阶段是获得封锁的阶段，称为扩展阶段；第二阶段是释放封锁的阶段，称为收缩阶段；

begin; insert ... 加锁1 update ... 加锁2 commit; 提交事务时，释放锁1和锁2

若加锁2时，加锁不成功，则进入等待状态，直至加锁成功后才继续执行； 如果另一个事务在获得锁时顺序恰恰相反，则可能导致死锁；因此，有一次性封锁法，要求事务必须一次性锁定所有需要使用的数据，否则不能继续执行；

定理:如果所有事务都遵守两个锁协议，那么这些事务的所有交叉调度都是可串行的(串行非常重要，尤其是在数据恢复和备份时)；

行级锁定(悲观锁)

1.共享锁和排他锁

Innnodb的行级锁也分为两种类型：共享锁和排他锁； 共享锁：当一个事务获得共享锁时，它只能读取，所以共享锁也被称为读取锁，多个事务可以同时获得一行数据的共享锁； 排他锁：当一个事务获得一行数据的排他锁时，就可以读写这行数据，所以排他锁也叫写锁，排他锁与共享锁和其他排他锁不兼容；

由于数据库提供共享锁和排他锁，具体用途在哪里： 1.1在数据库操作中，为有效保证和读取数据的正确性，提出的事务隔离级别采用锁定机制； 1.2提供相关SQL，在程序中使用方便；

2.事务隔离级别与锁的关系

数据库隔离级别：未提交读取(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)和可串行化(Serializable)； 未提交读(Read uncommitted)：可读取其他会话中未提交事务修改的数据，会出现脏读(Dirty Read)； 已提交读(Read committed)：只能读取已提交的数据，不能重复读取(NonRepeatable Read)； 可重复读(Repeatable read)：InnnoDB默认级别不会重复读取(NonRepeatable Read)，但是会有幻读(Phantom Read); 可串行化(Serializable)：强制事务排序，使之不可能相互冲突，从而解决幻读问题，使用表级共享锁，读写相互阻塞；

两个常用的隔离级别是：已提交阅读(Read committed)和可重复读(Repeatable read)；

3.已提交读

3.1准备测试表 CREATE TABLE `test_lock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `type` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 mysql> insert into test_lock values(null,'zhaohui',1); mysql> insert into test_lock values(null,'zhaohui2',2); 3.2检查和设置隔离等级 mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set mysql> set session transaction isolation level read committed; Query OK, 0 rows affected mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 3.3模拟多个事务的交叉执行 Session1执行查询

mysql> begin; Query OK, 0 rows affected mysql> select * from test_lock where id=1; +----+---------+------+ | id | name | type | +----+---------+------+ | 1 | zhaohui | 1 | +----+---------+------+ 1 row in set Session2更新数据

mysql> begin; Query OK, 0 rows affected mysql> update test_lock set name='zhaohui_new' where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected Session1执行查询

mysql> select * from test_lock where id=1; +----+-------------+------+ | id | name | type | +----+-------------+------+ | 1 | zhaohui_new | 1 | +----+-------------+------+ 1 row in set mysql> commit; Query OK, 0 rows affected Session1中出现了不可重复的阅读(NonRepeatable Read)，也就是说，在查询过程中没有锁定相关数据，导致无法重复读取，但写入、修改和删除数据仍然被锁定，如下所示：

Session1更新数据

mysql> begin; Query OK, 0 rows affected mysql> update test_lock set name='zhaohui_new2' where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Session2更新数据

mysql> begin; Query OK, 0 rows affected mysql> update test_lock set name='zhaohui_new3' where id=1; 1205 - Lock wait timeout exceeded; try restarting transaction Session2更新同一数据时超时，在更新数据时添加排他锁；

4.可重复读

4.1检查和设置隔离等级 mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set 4.2模拟多个事务的交叉执行 Session1执行查询

mysql> begin; Query OK, 0 rows affected mysql> select * from test_lock where type=2; +----+----------+------+ | id | name | type | +----+----------+------+ | 2 | zhaohui2 | 2 | +----+----------+------+ 1 row in set Session2更新数据

mysql> begin; Query OK, 0 rows affected mysql> update test_lock set name='zhaohui2__new' where type=2; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected Session1执行查询

mysql> select * from test_lock where type=2; +----+----------+------+ | id | name | type | +----+----------+------+ | 2 | zhaohui2 | 2 | +----+----------+------+ 1 row in set 可以找到两次查询的数字结果是一样的，实现了可重复阅读(Repeatable read)，再来看看有没有幻读。(Phantom Read)的问题；

Session3插入数据

mysql> begin; Query OK, 0 rows affected mysql> insert into test_lock values(null,'zhaohui3',2); Query OK, 1 row affected mysql> commit; Query OK, 0 rows affected Session1执行查询

mysql> select * from test_lock where type=2; +----+----------+------+ | id | name | type | +----+----------+------+ | 2 | zhaohui2 | 2 | +----+----------+------+ 1 row in set 可以发现可以重复读(Repeatable read)在隔离等级下，幻读不会出现；

分析原因：如何通过悲观锁实现可重复读取和无幻读现象，读取数据加共享锁，同一数据更新操作只能等待，确保可重复读取，但无幻读现象不能通过锁定行数据解决； 最终看到的现象是没有幻读的问题，同时，如果读取的数据加上共享锁，则应等待更新相同的数据，等待没有出现在上面的例子中，所以mysql内部应该有其他锁定机制——MVCC机制；

5.使用悲观锁SQL

使用5.1共享锁(lock in share mode) Session1查询数据

mysql> begin; Query OK, 0 rows affected mysql> select * from test_lock where type=2 lock in share mode; +----+--------------+------+ | id | name | type | +----+--------------+------+ | 2 | zhaohui2__new | 2 | | 3 | zhaohui3 | 2 | +----+--------------+------+ 2 rows in set Session2查询数据

mysql> begin; Query OK, 0 rows affected mysql> select * from test_lock where type=2 lock in share mode; +----+--------------+------+ | id | name | type | +----+--------------+------+ | 2 | zhaohui2__new | 2 | | 3 | zhaohui3 | 2 | +----+--------------+------+ 2 rows in set Session3更新数据

mysql> begin; Query OK, 0 rows affected mysql> update test_lock set name='zhaohui3___________________new' where id=3; 1205 - Lock wait timeout exceeded; try restarting transaction Session1和Session2使用共享锁，所以可以有很多，不冲突，但是Session3更新操作需要加排他锁，共享锁不能同时存在；

5.2排他锁使用(for update) Session1查询数据

mysql> begin; Query OK, 0 rows affected mysql> select * from test_lock where type=2 for update; +----+--------------+------+ | id | name | type | +----+--------------+------+ | 2 | zhaohui2__new | 2 | | 3 | zhaohui3 | 2 | +----+--------------+------+ 2 rows in set Session2查询数据

mysql> begin; Query OK, 0 rows affected mysql> select * from test_lock where type=2 for update; Empty set Session3更新数据

mysql> begin; Query OK, 0 rows affected mysql> update test_lock set name='zhaohui3___________________new' where id=3; 1205 - Lock wait timeout exceeded; try restarting transaction 排他锁只能同时存在，所有Session2和Sesssion3都将等待超时；

MVCCCC多版本并发控制

并发控制多版本(Multiversion Concurrency Control)：每个写作操作将创建一个新版本的数据，读取操作将直接从有限的多个版本的数据中选择最合适的结果；读写操作之间的冲突不再需要关注，管理和快速选择数据版本已经成为MVC需要解决的主要问题。 为什么要引入这个机制？首先，通过悲观锁处理阅读请求是非常划算的。其次，数据库中的大部分事务只阅读。阅读请求是写作请求的许多倍。最后，如果没有并发控制机制，最糟糕的情况是阅读请求读取已写入的数据，这对许多应用程序来说是完全可以接受的；

再来看看可重复读(Repeatable read)通过MVCC机制读取操作，只读取事务开始前数据库的快照(snapshot), 这样，在阅读操作中不需要阻塞写作操作，写作操作不需要阻塞阅读操作，同时避免脏阅读和不重复阅读；

当然，这并不意味着悲观锁是无用的。当数据更新时，数据库默认使用悲观锁，因此MVCC可以集成在一起使用(MVCC+2PL)用于解决读写冲突的无锁并发控制； MVCC使用快照阅读来解决不可重复阅读和幻读的问题，如上述例子所示：select一直在查询快照信息，无需添加任何锁； 上述实例中使用的select方法称为快照阅读(snapshot read)，事实上，阅读事务的隔离级别还有另一个含义：阅读数据库当前版本的数据–当前读(current read)；

当前阅读和Gap锁

不同于普通的select查询，目前阅读相应的sql包括：

select ...for update, select ...lock in share mode, insert,update,delete; 上述sql本身会增加悲观锁，因此不存在不可重复读取的问题，其余的是幻读问题； Session1执行当前阅读

mysql> select * from test_lock where type=2 for update; +----+----------------+------+ | id | name | type | +----+----------------+------+ | 2 | zhaohui2__new | 2 | | 3 | zhaohui3___________________new_1 | 2 | +----+----------------+------+ 2 rows in set Session2执行插入执行插入

mysql> begin; Query OK, 0 rows affected mysql> insert into test_lock values(null,'zhaohui_001',1); 1205 - Lock wait timeout exceeded; try restarting transaction 为什么显然锁定type=2的数据？当插入type=1时，也会锁定等待，因为InnoDB使用Next-Key锁查询线路，锁定的不是单个值，但是一个范围(GAP); 如果当前的type类型包括：1、2、4、6、8、10锁type=2，那么type=1、2、3就会被锁定，后面不会，锁定的是一个区间；这也保证了当前的阅读不会出现幻读现象； 注：type字段添加索引，如果不添加索引，gap锁将锁定整个表；

乐观锁

乐观锁是一种思想，认为事务间没有那么多争议，与悲观锁相比，乐观锁广泛应用于java并发包；一般采用以下方式:使用版本号(version)为了实现机制，版本号是为数据添加一个版本标志，通常在表中添加一个version字段；读取数据时，取出version，然后version+1，更新数据库时，比较第一次取出的version与数据库中的version是否一致。如果一致，更新将成功，否则将失败进入重试。具体使用情况如下：

begin; select id,name,version from test_lock where id=1; ... update test_lock set name='xxx',version=version+1 where id=1 and version=${version}; commit; 先查询后更新，需要保证原子性，或者用悲观锁锁锁整个事务；或者用乐观锁。如果乐观锁在读多写少的系统中性能更好；

总结

本文首先从Mysql的悲观锁开始，然后介绍了悲观锁与事务隔离水平的关系，分析了为什么不使用悲观锁来实现隔离水平；然后介绍了MVCC和Gap锁是如何解决不可重复阅读和幻读的问题；最后，乐观锁经常用于阅读数据远远大于编写数据的系统。