MySQL温故而知新--Innodb存储引擎中的锁_MySQL
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
+---+----+----+
| 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
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表主要字段如下：
1) requesting_trx_id：申请锁资源的事务id。
2) requested_lock_id：申请的锁的id。
3) blocking_trx_id：阻塞的事务id。
4) blocking_lock_id：阻塞的锁的id。
去看下当前锁等待信息，如下所示：
mysql> select * from INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 3015646
requested_lock_id: 3015646:797:3:2
blocking_trx_id: 3015645
blocking_lock_id: 3015645:797:3:2
1 row in set (0.00 sec)
mysql>
这里我们可以看到事务t1(3015646)申请了锁资源，而事务t2(3015645)则阻塞了事务t1的申请。我们管理其他表，得到更直观的详细信息，如下所示：
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query, it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2 WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| 3015647 | 18 | update test.t1 set b='t2' where a>2 | 3015645 | 17 | NULL |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
mysql>
4，一致性的非锁定读操作
4.1，CNR原理解析
一致性的非锁定行读（consistent nonlocking read，简称CNR）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中运行的数据。如果读取的行正在执行delete、update操作，这时读取操作不会因此而会等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据，如下图所示：
非锁定读，是因为不需要等待访问的行上X锁的释放，快照数据是指该行之前版本的数据，该实现是通过Undo段来实现，而Undo用来在事务中回滚数据，因此快照本身是没有额外的开销，此外读取快照是不需要上锁的，因为没有必要对历史的数据进行修改。
非锁定读大大提高了数据读取的并发性，在InnoDB存储引擎默认设置下，这是默认的读取方式，既读取不会占用和等待表上的锁。但是不同事务隔离级别下，读取的方式不同，不是每一个事务隔离级别下的都是一致性读。同样，即使都是使用一致性读，但是对于快照数据的定义也不相同。
快照数据其实就是当前数据之前的历史版本，可能有多个版本。如上图所示，一个行可能不止有一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。
在Read Committed和Repeatable Read模式下，innodb存储引擎使用默认的非锁定一致读。在Read Committed隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据；而在Repeatable Read隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
4.2，CNR实例开启2个Session A和B。
Session A：
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1 where a=1;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c2 | c2 |
+---+----+----+
1 row in set (0.00 sec)
mysql>
Session A中事务已经开始，读取了a=1的数据，但是还没有结束事务，这时我们再开启一个Session B，以此模拟并发的情况，然后对Session B做如下操作：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set a=111 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Session 中将a=1的行修改为a=111，但是事务同样没有提交，这样a=1的行其实加了一个X锁。这时如果再在Session A中读取a=1的数据，根据innodb存储引擎的特性，在Read Committed和Repeatable Read事务隔离级别下，会使用非锁定的一致性读。回到Session A，节着上次未提交的事务，执行select * from t1 where a=1;的操作，显示的数据应该都是原来的数据：
mysql> select * from t1 where a=1;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c2 | c2 |
+---+----+----+
1 row in set (0.00 sec)
mysql>
因为当前a=1的数据被修改了1次，所以只有一个版本的数据，接着我们在Session B中commit上次的事务。如：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
Session B提交事务后，这时再在Session A中运行select * from t1 where a=1;的sql语句，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下，得到的结果就会不一样，对于READ-COMMITTED模事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行的最新一个快照(fresh snapshot)。因此在这个例子中，因为Session B已经commit了事务，所以在READ-COMMITTED事务隔离级别下会得到如下结果，查询a=1就是为null记录，因为a=1的已经被commit成了a=111，但是如果查询a=111的记录则会被查到，如下所示：
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> select * from t1 where a=1;
Empty set (0.00 sec)
mysql> select * from t1 where a=111;
+-----+----+----+
| a | b | c |
+-----+----+----+
| 111 | c2 | c2 |
+-----+----+----+
1 row in set (0.01 sec)
mysql>
但是如果在REPEATABLE-READ事务隔离级别下，总是读取事务开始时的数据，所以得到的结果截然不同，如下所示：
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from t1 where a=1;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
mysql> select * from t1 where a=111;
Empty set (0.00 sec)
mysql>
对于READ-COMMITTED的事务隔离级别而言，从数据库理论的角度来看，其实违反了事务ACID的I的特性，既是隔离性，整理成时序表，如下图所示。
|
Time |
Session A |
Session B |
|
| time 1 |
Begin; Select * from t1 where a=1;有记录 |
|
|
| time 2 |
|
Begin; Update t1 set a=111 where a=1; |
|
| time 3 |
Select * from t1 where a=1;有记录 |
|
|
| time 4 |
|
Commit; |
|
| time 5 |
Select * from t1 where a=1; 无记录 |
|
|
V time 6 |
Commit; |
|
如果按照ACID原理中的I原理隔离性，在整个会话中Session A中，Select * from t1 where a=1;应该查询出来的数据保持一直，但是在time 5那一刻 Session A未结束的时候，查询出来的结果已经变化了和time 1、time 3已经不一致了，不满足ACID的隔离性。
5，SELECT ... FOR UPDATE && SELECT ... LOCK IN SHARE MODE
默认情况下，innodb存储引擎的select操作使用一致性非锁定读，但是在某些情况下，需要对读操作进行加锁。Innodb存储引擎对select语句支持2种添加锁操作;
SELECT ... FOR UPDATE 对于读取的行记录加一个X排它锁，其他事务如果要对这些行进行dml或者select操作都会被阻塞。
SELECT ... LOCK IN SHARE MODE 对于读取的行记录添加一个S共享锁。其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。
对于一致性 非锁定读，即使读取的行数已经被SELECT ... FOR UPDATE了，但是也是可以进行读取的。
PS：… FOR UPDATE以及LOCK IN SHARE MODE必须在一个事务中，如果事务commit了，锁也就释放了，所以在使用的时候务必加上begin;start transaction或者set autocommit=0;
例子如下：
会话A：开启事务，执行LOCK IN SHARE MODE;锁定
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
mysql>
同时在另外一个窗口开启会话B，执行dml操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set a=111 where a=1;
…这里会卡住，没有信息。
再开启一个会话C，查询INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表，就会看到锁的详细信息：
mysql> select * from INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 3015708:797:3:2 | 3015708 | X | RECORD | `test`.`t1` | PRIMARY | 797 | 3 | 2 | 1 |
| 3015706:797:3:2 | 3015706 | S | RECORD | `test`.`t1` | PRIMARY | 797 | 3 | 2 | 1 |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 3015708 | 3015708:797:3:2 | 3015706 | 3015706:797:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_state` AS waiting_trx_status,it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query,
-> it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query
-> FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2
-> WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_trx_status | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| 3015708 | LOCK WAIT | 18 | update t1 set a=111 where a=1 | 3015706 | 21 | NULL |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
mysql>
会话A开启的事务1（事务id：3015706）执行了SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
语句已经在a=1的行上加了S锁，所以会话B开启的事务2（事务id：23015708）执行的update t1 set a=111 where a=1;sql语句往a=1的行上加X锁，就被会话A的事务1阻塞了，所以事务2的状态值就是LOCK WAIT，一直在等待。直到事务等待超时，报错如下：
mysql> 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需要自己手动添加外键锁。
上一篇： 总结SQL中的group by 和 having 用法相关总结
下一篇： 什么是js的栈堆