前言
在 MySQL 通过加锁的方式来保证事务的一致性和隔离性。而数据库又是一个需要承诺支持高并发的应用，加锁过度就会影响数据库的并发能力。同时，作为一名合格的CRUD 工程师，了解数据库的加锁机制，也能避免我们写出产生死锁的接口。
今天，我们来分析一些常见条件语句在 RC、RR 级别下，数据库对会加些什么锁？
RC 级别下，条件是主键
准备数据，id是主键
mysql> create table t1 (id int,name varchar(10));
mysql> alter table t1 add primary key (id);
mysql> insert into t1 values(1,'a'),(4,'c'),(7,'b'),(10,'a'),(20,'d'),(30,'b');
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 7 | b |
| 10 | a |
| 20 | d |
| 30 | b |
+----+------+
6 rows in set (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 1 row affected (0.00 sec)
会话2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 7 | b |
| 10 | a |
| 20 | d |
| 30 | b |
+----+------+
6 rows in set (0.00 sec)
mysql> update t1 set name='a1' where id=10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set name='a1' where id=11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> update t1 set name='a1' where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
结论：READ-COMMITTED隔离级别下， id为主键，SQL 语句delete/update from t1 where id = 10;只对 id = 10 这条记录加了排它锁（X锁）。
RC 级别下，条件是唯一索引
mysql> create table t1 (id int,name varchar(10));
Query OK, 0 rows affected (0.06 sec)
mysql> ALTER TABLE test.t1 ADD UNIQUE INDEX idx_id (id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE test.t1 ADD PRIMARY KEY (name);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(1,'f'),(2,'zz'),(3,'b'),(5,'a'),(6,'c'),(10,'d');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
会话一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 1 row affected (0.00 sec)
会话二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | f |
| 2 | zz |
| 3 | b |
| 5 | a |
| 6 | c |
| 10 | d |
+------+------+
6 rows in set (0.00 sec)
mysql> update t1 set id =100 where name='d';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id =100 where name='c';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id =101 where name='a';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
结论：READ-COMMITTED隔离级别下， id为唯一索引，SQL 语句delete/update from t1 where id = 10;对 id = 10 这条记录加了排它锁（X锁），并对主键索引（聚餐索引）也加了X锁。
RC 级别下，条件是普通索引
mysql> create table t1 (id int,name varchar(10));
mysql> ALTER TABLE test.t1 ADD PRIMARY KEY (name);
# 普通索引
mysql> alter table t1 add index idx_id (id);
mysql> insert into t1 values(2,'zz'),(6,'c'),(10,'b'),(10,'d'),(11,'f'),(15,'a');
会话一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 2 rows affected (0.00 sec)
会话二
select * from t1;
+------+------+
| id | name |
+------+------+
| 2 | zz |
| 6 | c |
| 10 | b |
| 10 | d |
| 11 | f |
| 15 | a |
+------+------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set id=11 where name='b';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=11 where name='d';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=11 where name='f';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> update t1 set id=11 where name='c';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
结论：READ-COMMITTED隔离级别下， id为普通索引，SQL 语句delete/update from t1 where id = 10;在普通索引上，对满足 id = 10 的记录加了排它锁（X锁），对应的聚簇索引上也加X锁。
RC 级别下，条件没有索引
mysql> create table t1 (id int,name varchar(10));
mysql> ALTER TABLE test.t1 ADD PRIMARY KEY (name);
mysql> insert into t1 values(5,'a'),(3,'b'),(10,'d'),(2,'f'),(10,'g'),(9,'zz');
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 2 rows affected (0.00 sec)
会话2
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 5 | a |
| 3 | b |
| 10 | d |
| 2 | f |
| 10 | g |
| 9 | zz |
+------+------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set id=6 where name='a';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id=6 where name='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id=6 where name='d';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=6 where name='f';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id=6 where name='g';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=6 where name='zz';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id=6 where name='zzf';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
结论：READ-COMMITTED隔离级别下， id没有索引，SQL 语句delete/update from t1 where id = 10;会扫描全表，对每条记录都加X锁返回，然后由 MySQL Sercver 层进行过滤。
但在实际的实现中，MySQL进行了一些改进，在 MySQL Server 过滤条件后，发现不满足，会调用 unlock_row 方法释放锁，从而提高性能。这也就解释了在实现时，发现只锁住了 id=2 的两行。
RR 级别下，条件是主键
mysql> create table t1 (id int,name varchar(10));
mysql> alter table t1 add primary key (id);
mysql> insert into t1 values(1,'a'),(4,'c'),(7,'b'),(10,'a'),(20,'d'),(30,'b');
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 7 | b |
| 10 | a |
| 20 | d |
| 30 | b |
+----+------+
6 rows in set (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
会话1
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> delete from t1 where id=10;
Query OK, 1 row affected (0.00 sec)
会话2
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 7 | b |
| 10 | a |
| 20 | d |
| 30 | b |
+----+------+
6 rows in set (0.00 sec)
mysql> update t1 set name='a1' where id=10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set name='a1' where id=11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> update t1 set name='a1' where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
结论：REPEATABLE-READ隔离级别下， id为主键，SQL 语句delete/update from t1 where id = 10; 与 RC 级别下的加锁逻辑一样。
RR 级别下，条件是唯一索引
准备数据
mysql> create table t1 (id int,name varchar(10));
mysql> ALTER TABLE test.t1 ADD UNIQUE INDEX idx_id (id);
mysql> ALTER TABLE test.t1 ADD PRIMARY KEY (name);
mysql> insert into t1 values(1,'f'),(2,'zz'),(3,'b'),(5,'a'),(6,'c'),(10,'d');
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 1 row affected (0.01 sec)
会话2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | f |
| 2 | zz |
| 3 | b |
| 5 | a |
| 6 | c |
| 10 | d |
+------+------+
6 rows in set (0.00 sec)
mysql> update t1 set id =100 where name='d';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id =100 where name='c';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id =101 where name='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
结论：REPEATABLE-READ隔离级别下， id为主键，SQL 语句delete/update from t1 where id = 10; 与 RC 级别下的加锁逻辑一样，会加两个X锁，id 唯一索引上满足条件的记录加上一个，对应的聚簇索引上的记录加上一个。
RR 级别下，条件是普通索引
准备数据
mysql> create table t1 (id int,name varchar(10));
mysql> ALTER TABLE test.t1 ADD PRIMARY KEY (name);
mysql> alter table t1 add index idx_id (id);
mysql> insert into t1 values(2,'zz'),(6,'c'),(10,'b'),(10,'d'),(11,'f'),(15,'a');
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 2 rows affected (0.00 sec)
会话2
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 2 | zz |
| 6 | c |
| 10 | b |
| 10 | d |
| 11 | f |
| 15 | a |
+------+------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(6,'aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(6,'bb');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(6,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(7,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(8,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(9,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(10,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(11,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(11,'ff');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(11,'g');
Query OK, 1 row affected (0.00 sec)
结论：REPEATABLE-READ隔离级别下， id为普通索引，SQL 语句delete/update from t1 where id = 10; 通过 id 索引定位到第一条满足条件的记录，在它前后加上GAP锁，然后在对应主键聚簇索引上加X锁；然后读取下一条记录，重复刚才的操作，直到进行到第一条不满足条件的记录时。
GAP 锁是用来解决RR隔离级别中的幻读问题，即保证同一个事务连续两次当前读得到的内容一样。幻读在 RC 级别中会发生，是因为另一个事务的插入语句有可能改变当前事务查询条件的结果，通过引入 GAP 锁锁住，不给其他事务插入操作的机会来解决。
RR 级别下，条件没有索引
准备数据
mysql> create table t1 (id int,name varchar(10));
mysql> ALTER TABLE test.t1 ADD PRIMARY KEY (name);
mysql> insert into t1 values(5,'a'),(3,'b'),(10,'d'),(2,'f'),(10,'g'),(9,'zz');
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id=10;
Query OK, 2 rows affected (0.00 sec)
begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 5 | a |
| 3 | b |
| 10 | d |
| 2 | f |
| 10 | g |
| 9 | zz |
+------+------+
6 rows in set (0.00 sec)
mysql> insert into t1 values(1,'j');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(2,'j');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(100,'j');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
结论：REPEATABLE-READ隔离级别下， id没有索引，SQL 语句delete/update from t1 where id = 10; 会对全表加X锁和 GAP 锁，也就是说其他事务的更新、删除、插入都会被锁住。不过跟RC级别的情况类似，可以通过开启semi-consistant read来提前释放锁。
semi-consistent read如何触发：
- read committed隔离级别；
- Repeatable Read隔离级别，同时设置了
innodb_locks_unsafe_for_binlog参数。
