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

1，锁的种类

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

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

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

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

2，锁的扩展

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

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

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

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

解析：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的所有a<5的数据行，然后去第二个窗口开启第二个事务t2，如下，会看到update语句一直在等待事务t1释放锁资源，过了几秒后，会有报错信息，如下t2事务窗口界面：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test.t1 set b='t2' where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

3.2，通过3个系统表来查看锁信息

l 1，INNODB_TRX表

先看下表的重要字段以及记录的信息

a) trx_id：innodb存储引擎内部事务唯一的事务id。

b) trx_state：当前事务的状态。

c) trx_started：事务开始的时间。

d) trx_requested_lock_id：等待事务的锁id，如trx_state的状态为LOCK WAIT，那么该值代表当前事务之前占用锁资源的id，如果trx_state不是LOCK WAIT的话，这个值为null。

e) trx_wait_started：事务等待开始的时间。

f) trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在innodb的存储引擎中，当发生死锁需要回滚时，innodb存储引擎会选择该值最小的事务进行回滚。

g) trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。

h) trx_query：事务运行的sql语句，在实际中发现，有时会显示为null值，当为null的时候，就是t2事务中等待锁超时直接报错（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就显示为null值

……

因为前面模拟了事务锁场景，开启了t1和t2事务，现在去查看这个表信息，会有2条记录如下：

mysql> select * from INNODB_TRX\G
*************************** 1. row ***************************
 trx_id: 3015646
 trx_state: LOCK WAIT
 trx_started: 2014-10-07 18:29:39
 trx_requested_lock_id: 3015646:797:3:2
 trx_wait_started: 2014-10-07 18:29:39
 trx_weight: 2
 trx_mysql_thread_id: 18
 trx_query: update test.t1 set b='t2' where a=1
 trx_operation_state: starting index read
 trx_tables_in_use: 1
 trx_tables_locked: 1
 trx_lock_structs: 2
 trx_lock_memory_bytes: 376
 trx_rows_locked: 1
 trx_rows_modified: 0
 trx_concurrency_tickets: 0
 trx_isolation_level: READ COMMITTED
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
 trx_id: 3015645
 trx_state: RUNNING
 trx_started: 2014-10-07 18:29:15
 trx_requested_lock_id: NULL
 trx_wait_started: NULL
 trx_weight: 2
 trx_mysql_thread_id: 17
 trx_query: NULL
 trx_operation_state: NULL
 trx_tables_in_use: 0
 trx_tables_locked: 0
 trx_lock_structs: 2
 trx_lock_memory_bytes: 376
 trx_rows_locked: 4
 trx_rows_modified: 0
 trx_concurrency_tickets: 0
 trx_isolation_level: READ COMMITTED
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
 
mysql>

这里只是记录了一些当前正在运行的事务，比如事务t2正在运行trx_query: update test.t1 set b='t2' where a=1的sql语句，t1先执行，所以是trx_state: RUNNING先申请的资源一直在运行，而t2后run的所以是trx_state: LOCK WAIT一直在等待t1执行完后释放资源。 但是并不能仔细判断锁的一些详细情况，我们需要再去看INNODB_LOCKS表数据。

l 2，INNODB_LOCKS表

a) lock_id：锁的id以及被锁住的空间id编号、页数量、行数量

b) lock_trx_id：锁的事务id。

c) lock_mode：锁的模式。

d) lock_type：锁的类型，表锁还是行锁

e) lock_table：要加锁的表。

f) lock_index：锁的索引。

g) lock_space：innodb存储引擎表空间的id号码

h) lock_page：被锁住的页的数量，如果是表锁，则为null值。

i) lock_rec：被锁住的行的数量，如果表锁，则为null值。

j) lock_data：被锁住的行的主键值，如果表锁，则为null值。

mysql> select * from INNODB_LOCKS\G
*************************** 1. row ***************************
 lock_id: 3015646:797:3:2
lock_trx_id: 3015646
 lock_mode: X
 lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: PRIMARY
 lock_space: 797
 lock_page: 3
 lock_rec: 2
 lock_data: 1
*************************** 2. row ***************************
 lock_id: 3015645:797:3:2
lock_trx_id: 3015645
 lock_mode: X
 lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: PRIMARY
 lock_space: 797
 lock_page: 3
 lock_rec: 2
 lock_data: 1
2 rows in set (0.00 sec)
 
mysql>

这里我们可以看到当前的锁信息了，2个事务都锁定了，看相同的数据lock_space: 797、lock_page: 3、lock_rec: 2可以得出事务t1和事务t2访问了相同的innodb数据块，再通过lock_data字段信息lock_data: 1，看到锁定的数据行都是主键为1的数据记录，可见2个事务t1和t2都申请了相同的资源，因此会被锁住，事务在等待。

通过lock_mode: X值也可以看出事务t1和t2申请的都是排它锁。

PS：当执行范围查询更新的时候，这个lock_data的值并非是完全准确。当我们运行一个范围更新时，lock_data只返回最先找到的第一行的主键值id；另外如果当前资源被锁住了，与此同时由于锁住的页因为InnoDB存储引擎缓冲池的容量，而导致替换缓冲池页面，再去查看INNODB_LOCKS表时，这个lock_data会显示未NULL值，意味着InnoDB存储引擎不会从磁盘进行再一次查找。

l 3，INNODB_LOCK_WAITS表

当事务量比较少，我们可以直观的查看，当事务量非常大，锁等待也时常发生的情况下，这个时候可以通过INNODB_LOCK_WAITS表来更加直观的反映出当前的锁等待情况：

INNODB_LOCK_WAITS var cpro_id = "u6292429";