mysql limit 的妙用
更新无索引的列，会导致全表被锁，其它线程无法更新表中的数据，如果加入了limit N关键字，可以减少被锁定的行，在一定程度上可以提高并发
表结构及数据如下
create table `t` (
`id` int not null primary key,
`name` varchar(32) default null,
`age` int default null,
`ismale` tinyint default null
) engine=innodb;
insert into t values(1,'d001',30,1), (3,'d003',30,1);
insert into t values(5,'d005',30,1), (7,'d007',30,1);
insert into t values(9,'d009',30,1), (11,'d011',30,1);
select * from t;
+----+------+------+--------+
| id | name | age | ismale |
+----+------+------+--------+
| 1 | d001 | 30 | 1 |
| 3 | d003 | 30 | 1 |
| 5 | d005 | 30 | 1 |
| 7 | d007 | 30 | 1 |
| 9 | d009 | 30 | 1 |
| 11 | d011 | 30 | 1 |
+----+------+------+--------+不加入limit锁全表
| 时刻 | Session A | Session B |
|---|---|---|
| T1 | begin; | |
| update t set age=31 where name=’d003’; | ||
| T2 | Begin; | |
| update t set age=31 where name=’d005’;(blocked) | ||
| update t set age=31 where id=5;(blocked) | ||
| insert into t values(2,’d002’,30,1);(blocked) | ||
| insert into t values(12,’d012’,30,1);(blocked) |
SessionA的加锁范围
主键索引id锁的范围是[infimum，supremum]
由于name列没有索引，where name='d003'查询时走主键索引，全表扫描。先找到id=1的行，加next-key锁，发现name的值不匹配，继续向右查找，给id=3的行上锁，name匹配，返回结果给server层，然后继续向右查找直到最后一行id=11，查找过程中涉及到的行都被加了next-key锁(id=5,7,9,11的行)，由于事务没有提交，这些行锁没有被释放。
SessionB被阻塞在哪里
更新条件 where name='d005'，由于name列没有索引，查询时也要走主键索引。先找到id=1的行，加next-key锁，由于SessionA已经加了next-key锁，SessionB只能等待，它在此时发生了阻塞
update t set age=31 where name='d005'
# 被SessionA上的 id=1的行的 next-key锁阻塞
RECORD LOCKS space id 5 page no 4 n bits 80 index PRIMARY of table `t`
trx id 4366 lock_mode X waiting
0: len 4; hex 80000001; asc
1: len 6; hex 000000000d98; asc
2: len 7; hex 81000000a70110; asc
3: len 4; hex 64303031; asc d001
4: len 4; hex 8000001e; asc
5: len 1; hex 81; asc通过Id=5更新也会被SessinA的next-key锁阻塞，虽然SessionB加要的是行锁，这也从则面说明了next-key是由行锁和间隙锁组成
update t set age=31 where id=5
# 被id=5的记录上行锁阻塞
RECORD LOCKS space id 5 page no 4 n bits 80 index PRIMARY of table `t`
trx id 4367 lock_mode X locks rec but not gap waiting
0: len 4; hex 80000005; asc
1: len 6; hex 000000000d9e; asc
2: len 7; hex 82000000a80110; asc
3: len 4; hex 64303034; asc d004
4: len 4; hex 8000001e; asc
5: len 1; hex 81; asc插入数据也被阻塞，因为插入Id的值都落在了间隙锁[infimum，supremum]内，无法插入任何的值。
insert into t values(2,'d002',30,1);
# 插入id=2的值，被间隙锁(1,3)阻塞
RECORD LOCKS space id 5 page no 4 n bits 80 index PRIMARY of table `t`
trx id 4365 lock_mode X locks gap before rec insert intention waiting
0: len 4; hex 80000003; asc
1: len 6; hex 000000001106; asc
2: len 7; hex 01000001120488; asc
3: len 4; hex 64303033; asc d003
4: len 4; hex 8000001f; asc
5: len 1; hex 81; asc加入limit减少被锁的行
| 时刻 | Session A | Session B |
|---|---|---|
| T1 | begin; | |
| T2 | update t set age=31 where name=’d003’ limit 1; | |
| T3 | begin; | |
| T4 | update t set age=31 where name=’d005’;(blocked) | |
| insert into t values(2,’d002’,30,1);(blocked) | ||
| T5 | insert into t values(4,’d004’,30,1);(Query Ok) |
SessionA加锁的范围是怎么样的
主键索引id锁的范围是[infimum，3]
由于name列没有索引，查询时走主键索引，全表扫描。先找到id=1的行，加next-key锁，发现name的值不匹配，继续向右查找，给id=3的行上锁，name匹配，返回结果给server层，由于使用了limit 1，满足条件，不再向后查找，查询结束。记录(id:1),(id:3)的行上了next-key锁，之后的记录没有上锁。
SessionB被阻塞在哪里
更新条件 where name='d005'，由于name列没有索引，查询时也要走主键索引。先找到id=1的行，加next-key锁，由于SessionA已经加了next-key锁，SessionB只能等待，它在此时发生了阻塞
update t set age=31 where name='d005'
# 被SessionA上的 id=1的行的 next-key锁阻塞
RECORD LOCKS space id 5 page no 4 n bits 80 index PRIMARY of table `t`
trx id 4871 lock_mode X waiting
0: len 4; hex 80000001; asc
1: len 6; hex 000000000d98; asc
2: len 7; hex 81000000a70110; asc
3: len 4; hex 64303031; asc d001
4: len 4; hex 8000001e; asc
5: len 1; hex 81; asc因为id锁的范围是[infimum，3]，(id=5,7,9,11)的行没有被上锁，可以更新及插入id > 3的数据。
# Session B
insert into t values(4,'d004',30,1);
(Query Ok)
# 虽然不能通过 where name='d005' 修改
# 但可以通过where id=5 修改，也说明了id=5的行没有被锁
# 同时也绕过了需要等待id=1的锁的限制
update t set age=31 where id=5;
(Query Ok)
select * from t;
+----+------+------+--------+
| id | name | age | ismale |
+----+------+------+--------+
| 1 | d001 | 30 | 1 |
| 3 | d003 | 30 | 1 |
| 4 | d004 | 30 | 1 |
| 5 | d005 | 31 | 1 |
| 7 | d007 | 30 | 1 |
| 9 | d009 | 30 | 1 |
| 11 | d011 | 30 | 1 |
+----+------+------+--------+