MySQL 温故而知新--Innodb存储引擎中的锁

最近碰到很多锁问题，所以解决了后，仔细再去阅读了关于锁的书籍，整理如下：

1，锁的种类

Innodb存储引擎实现了如下2种标准的行级锁：

?  共享锁(S lock)，允许事务读取一行数据。

?  排它锁(X lock)，允许事务删除或者更新一行数据。

 

当一个事务获取了行r的共享锁，那么另外一个事务也可以立即获取行r的共享锁，因为读取并未改变行r的数据，这种情况就是锁兼容。但是如果有事务想获得行r的排它锁，则它必须等待事务释放行r上的共享锁—这种情况就是锁不兼容，二者兼容性如下表格所示：

排它锁和共享锁的兼容性
	X 排它锁	S 共享锁
X 排它锁	冲突	冲突
S 共享锁	冲突	兼容

 

2，锁的扩展

Innodb存储引擎支持多粒度锁定，这种锁定允许在行级别上的锁和表级别上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，就是意向锁。意向锁是表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。它也分为两种：

?  意向共享锁（IS Lock），事务想要获得一个表中某几行的共享锁。

?  意向排它锁（IX Lock），事务想要获得一个表中某几行的排它锁。

 

由于InnoDB支持的是行级别锁，所以意向锁其实不大会阻塞除了全表scan以下的任何请求。共享锁、排它锁、意向共享锁、意向排它锁相互之前都是有兼容/互斥关系的，可以用一个兼容性矩阵表示(y表示兼容，n表示不兼容)，如下所示:

	X 排它锁	S 共享锁	IX 意向排它锁	IS 意向共享锁
X 排它锁	冲突	冲突	冲突	冲突
S 共享锁	冲突	兼容	冲突	兼容
IX 意向排它锁	冲突	冲突	兼容	兼容
IS 意向共享锁	冲突	兼容	兼容	兼容

         解析：X和S的相互兼容关系step1描述过了，IX和IS的相互关系全部是兼容，这也很好理解，因为它们都只是“有意”，还处于YY阶段，没有真干，所以是可以兼容的；

剩下的就是X和IX，X和IS, S和IX， S和IS的关系了，我们可以由X和S的关系推导出这四组关系。

简单的说：X和IX的=X和X的关系。为什么呢？因为事务在获取IX锁后，接下来就有权利获取X锁。如果X和IX兼容的话，就会出现两个事务都获取了X锁的情况，这与我们已知的X与X互斥是矛盾的，所以X与IX只能是互斥关系。其余的三组关系同理，可用同样的方式推导出来。

 

 

3，模拟锁场景

在InnoDB Plugin之前，我们只能通过SHOW FULL PROCESSLIS和SHOW ENGINE INNODB STATUS来查看当前的数据库请求，然后再判断事务中锁的情况。新版本的InnoDB Plugin中，在information_schema库中添加了3张表，INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS。通过这3个表，可以更简单的监控当前的事务并且分析可能存在的锁问题。如果数据库正常运行，这3个表都是空的，没有任何记录。

3.1，开启事务t1、t2，模拟锁

开启2个session窗口，并且开启2个事务t1和t2。

在第一个窗口开启事务t1执行一个锁定操作，如下t1事务窗口界面：

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  开始执行锁定操作
mysql> select * from test.t1 where a<5 for update;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
| 2 | a  |    |
| 3 | r5 | r3 |
| 4 | r4 | r5 |
+---+----+----+
4 rows in set (0.00 sec)

 

mysql>

这个时候，事务t1已经锁定了表t1的所有a92e8d5d54b7938f0c0f9f2c32304850b update t1 set a=111 where a=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

此时，会话B中的事务2就终止了update t1 set a=111 where a=1;的dml请求操作。

 

6，自增长和锁

自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。

SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;

插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。

 

从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.

 

自增长的分类：

在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：

mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
 
mysql>

而在myisam表中，则没有这样的限制，如下所示：

mysql>  CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

 

mysql>

 

7，MySQL外键和锁

在innodb存储引擎中，对于一个外键列，如果没有显式的针对这个列添加索引Innodb存储引擎会自动的对其添加一个索引，这样可以避免表锁，这点比oracle做的较好一些，oracle需要自己手动添加外键锁。

 以上就是MySQL 温故而知新--Innodb存储引擎中的锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！