在事务中执行select…for update,update,delete会引起锁，对于事务的修改，事务中会使用X锁，X锁是行级锁，InnDB行锁是通过给索引上的索引项加锁实现的（只有通过索引条件检索数据（即explain sql语句，type=index或range），InnoDB才使用行级锁，否则使用表锁）
为了验证这个，我做了一个小测验
数据表t_bitfly：
CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL DEFAULT '0',
`value` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
session1:
start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_bitfly where id < 4 for update;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+-------+
3 rows in set (0.00 sec)
session2:
start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_bitfly where id >= 4 for update;
select * from t_bitfly where id >= 4 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
出现死锁
此刻我的心情
想不明白啊，不是应该只锁住<4的那几行吗
explain:
mysql> explain select * from t_bitfly where id < 4\G;
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: t_bitfly
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where; Using index
type=range，使用上索引了啊，为什么，为什么，不明白啊
我的心情
这个问题困扰自己好久，终于在今天晚上找到答案了，恍然大悟的感觉真的是贼爽
原因：在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。
继续做测验
session2:
start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_bitfly where id > 5 for update;
mysql> select * from t_bitfly where id > 4 for update;
+----+-------+
| id | value |
+----+-------+
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | i |
| 10 | j |
| 11 | k |
+----+-------+
7 rows in set (0.00 sec)
现在就不出现死锁的情况下，因为此时没有包括4
出现next-key有两种情况，一是上面的使用索引范围，二是使用相等条件请求给一个不存在的记录：
例如：
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_bitfly where id = 17 for update;
Empty set (0.00 sec)
session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_bitfly values(17,'n');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
一般情况下相等情况不会出现锁争用问题
使用next-key的目的：
1）解决幻读
2）满足mysql的复制和恢复
带来的问题
造成严重的锁等待
如何解决：
尽量避免使用范围条件，使用相等条件来访问和更新数据
行锁的使用还需要注意的几点，否则很有可能会在执行的时候出现死锁或获取不到锁的情况（比如我遇到的）：
1）在MySQL中，行级锁并不是直接锁记录，而是锁索引。
2）索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引（自定义索引+主键），MySQL会先锁定该非主键索引，再锁定相关的主键索引
3）当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁
网友评论