MySQL InnoDB 锁 – For Update 加锁分析：
1. InnoDB锁 简单介绍
一、InnoDB锁
1、全局锁
全局读锁，flush tables with read lock，整库处于只读状态。全局锁的一个典型场景：全库逻辑备份，–single-transaction实现一致性读。
2、表级锁
表锁，lock tables…read/write，主动在表上加读锁或写锁；
元数据锁（meta data lock，MDL），访问表时自动加上，防止DDL和DML并发的冲突，保证读写正确性；
自增锁，表中auto_increment字段的自增值生成控制，内存自增计数器，8.0之后通过redo进行持久化；
意向锁，只会和表级锁发生冲突，不会阻止除表锁请求之外的锁，表明有事务即将、或正锁定某N行；
-
意向共享锁（IS）：SELECT … LOCK IN SHARE MODE，在对应记录行上加锁之前，在表上加意向共享锁；
-
意向排它锁（IX）：SELECT .. FOR UPDATE，悲观锁，对表所有扫描过的行都会被加上意向排它锁，若扫描行其中有行锁，则会被阻塞；对SELECT索引加排它锁，阻塞其他事务修改或SELECT … FOR SHARE（在8.0新增的方式）；
3、行锁
行锁，事务锁，只有发生行锁冲突，才会出现事务锁的排队等待。
两阶段锁协议：行锁在需要时加上，事务结束时释放。
行锁的3种算法：record lock、gap lock、next-key lock
-
记录锁 record lock：添加在索引上，表中没有索引时会默认添加在默认创建的聚集索引上；
-
间隙锁 gap lock：锁定一个范围，可重复读 隔离级别下，行锁会变成gap锁（范围锁），降低并发性，当前读(dml、select for update)，若where条件列上有索引，加gap lock在索引上，实现可重复读；
-
Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身；
① 唯一索引或主键，Next-Key Lock 降为 Record Lock，即仅锁住索引本身，而不是范围。
②
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
### session 1root@test 15:51 > begin; root@test 15:51 > show create table student;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `birthday` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `ix_name` (`name`), KEY `ix_birthday` (`birthday`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+root@test 15:52 > select * from student;+----+-------+---------------------+| id | name | birthday |+----+-------+---------------------+| 1 | abcd | 1995-06-27 00:00:00 || 2 | abef | 1995-01-24 00:00:00 || 3 | abg | 1995-07-26 00:00:00 || 4 | cdmn | 1995-06-13 00:00:00 |+----+-------+---------------------+root@test 15:52 > select * from student where birthday > '1995-06-27 00:00:00' and birthday < '1995-07-26 00:00:00' for update; Empty set (0.02 sec) ### session 2root@test 15:51 > begin; # 左开 root@test 15:54 > update student set name = 'abcd' where birthday = "1995-06-27 00:00:00"; # 右闭(阻塞更新) root@test 15:55 > update student set name = 'abg' where birthday = '1995-07-26 00:00:00'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
分析不同隔离级别，当前读（dml、select … for update）的加锁情况
1、REPEATABLE-READ 可重复读
|
1 |
root@test 15:06 > show global variables like "%iso%";+-----------------------+-----------------+| Variable_name | Value |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ || tx_isolation | REPEATABLE-READ |+-----------------------+-----------------+ |
1.1、表无显式主键和索引
|
1 2 3 4 5 |
root@test 15:30 > show create table t_student;+-----------+----------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-----------+----------------------------------------------------------------------------------------------------------------------------+| t_student | CREATE TABLE `t_student` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-----------+----------------------------------------------------------------------------------------------------------------------------+root@test 15:30 > begin; root@test 15:31 > select * from t_student for update;+----+-------+| id | name |+----+-------+| 1 | jack || 2 | kuzma || 3 | linda |+----+-------+ |
如上，可重复读，表上没有主键（会自动生成隐式主键聚集组织表），也没有索引，全表SELECT的当前读的加锁情况：
① 对表添加 IX 锁
② 在”supremum”上添加 Next-Key Lock（supremum表示高于表中任何一个索引的值），即最大索引值之后的间隙锁住
③ 在三条记录上分别添加 Next-Key Lock
|
1 2 3 4 5 6 7 8 9 10 11 |
root@test 16:08 > begin; # where上带条件 id = 3root@test 16:08 > select * from t_student where id = 3 for update;+----+-------+| id | name |+----+-------+| 3 | linda |+----+-------+root@test 16:09 > show engine innodb statusG # 锁信息如上，表上IX，supremum 和 三条记录上添加 Next-Key Lock # 另开一个session root@test 16:09 > begin; root@test 16:13 > insert into t_student values(2,"tom"); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionroot@test 16:15 > insert into t_student values(4,"tom"); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
如上，insert为什么会被阻塞呢？delete呢？update呢？
每次插入记录时，所生成的聚集索引(DB_ROW_ID)是自增的，每次都会在表的最后插入，因此就有可能插入id=10这条记录，所以需要添加”supremum pseudo-record”防止数据插入。
|
1 2 3 4 5 6 7 8 |
---TRANSACTION 582122, ACTIVE 3874 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 6 row lock(s) MySQL thread id 12529, OS thread handle 123145486712832, query id 94463 localhost root updateinsert into t_student values(5,"tom") Trx read view will not see trx with id >= 582123, sees < 582121------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;------------------TABLE LOCK table `test`.`t_student` trx id 582122 lock mode IX RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; |
即使是不满足where条件的记录上，也会添加Next-Key Lock，目的是为了防止幻读。因此的，其他会话事务执行delete或者update都会造成幻读，也就被阻塞的。
|
1 2 3 4 5 6 7 |
---TRANSACTION 582122, ACTIVE 3788 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 12529, OS thread handle 123145486712832, query id 94461 localhost root updatingupdate t_student set name = "linda" where id = 3Trx read view will not see trx with id >= 582123, sees < 582121------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000000090a; asc ;; 1: len 6; hex 00000008e1c8; asc ;; 2: len 7; hex f4000001e40110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 6a61636b; asc jack;;------------------TABLE LOCK table `test`.`t_student` trx id 582122 lock mode IX RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000000090a; asc ;; 1: len 6; hex 00000008e1c8; asc ;; 2: len 7; hex f4000001e40110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 6a61636b; asc jack;; |
1.2、表有显式主键无索引
-
不带where条件，表上IX，主键最大索引值之后的间隙锁住，表所有记录对应主键加上记录锁
-
where条件是主键，表上IX，主键添加记录锁（X，REC_NOT_GAP），并且只锁住条件记录，因为主键保证唯一，不需要间隙锁
|
1 2 3 4 5 |
---TRANSACTION 582139, ACTIVE 12 sec2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 12528, OS thread handle 123145486434304, query id 94472 localhost rootTABLE LOCK table `test`.`t_student` trx id 582139 lock mode IX RECORD LOCKS space id 169 page no 3 n bits 72 index PRIMARY of table `test`.`t_student` trx id 582139 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000008e1c8; asc ;; 2: len 7; hex f4000001e4011f; asc ;; 3: |