解决困扰自己已久的InnoDB存储引擎行级锁问题

来源：互联网发布：html页面写java代码编辑：程序博客网时间：2024/06/08 07:26

在事务中执行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: rangepossible_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

行锁的使用还需要注意的几点，否则很有可能会在执行的时候出现死锁或获取不到锁的情况（比如我遇到的）：
1）在MySQL中，行级锁并不是直接锁记录，而是锁索引。
2）索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引（自定义索引+主键），MySQL会先锁定该非主键索引，再锁定相关的主键索引
3）当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁

阅读全文

0 0