For Update 加锁分析

一、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，即仅锁住索引本身，而不是范围。

　　　　② 辅助索引（二级索引），默认使用Next-Key Locking加锁，锁定范围是前一个索引到后一个索引之间范围，左开右闭。

### session 1
root@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 2
root@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

GeaoZhang