CREATE TABLE `t1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`i1` int DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
插入测试数据:
INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);
2. 可重复读
把事务隔离级别设置为 REPEATABLE-READ(如已设置,忽略此步骤):
SET transaction_isolation = 'REPEATABLE-READ';
-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
执行以下 select 语句:
begin;
select * from t1 ignore index(idx_i1)
where id >= 10 and id < 30 for share;
查看加锁情况:
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 10
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S
lock_status | GRANTED
lock_data | 20
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,GAP
lock_status | GRANTED
lock_data | 30
lock_data = 10、lock_mode = S,REC_NOT_GAP 表示对主键索引中
没有按照默认行为加共享 Next-Key 锁,是因为示例 SQL 使用主键索引进行范围扫描,从
示例 SQL 并不在意其它事务往
如果有其它事务往
这个是没问题的。
因为其它事务往
其它事务要往 t1 表中插入记录,id 大于等于 10、小于等于 19 的记录都会插入到
当然了,因为存在主键索引,t1 表中
lock_data = 20、lock_mode = S 表示对主键索引中
lock_data = 30、lock_mode = S,GAP 表示对主键索引中
没有按照默认行为加共享 Next-Key 锁,是因为
示例 SQL 不关心
3. 读已提交
把事务隔离级别设置为 READ-COMMITTED(如已设置,忽略此步骤):
SET transaction_isolation = 'READ-COMMITTED';
-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
执行以下 select 语句:
begin;
select * from t1 ignore index(idx_i1)
where id >= 10 and id < 30 for share;
查看加锁情况:
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 10
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 20
lock_data = 10、lock_mode = S,REC_NOT_GAP 表示对主键索引中
lock_data = 20、lock_mode = S,REC_NOT_GAP 表示对主键索引中
可重复读隔离级别对主键索引中
其实读已提交隔离级别下,InnoDB 从主键索引中读取
InnoDB 把这条记录返回给 server 层之后,server 层判断这条记录不匹配 where 条件,会通知 InnoDB 释放这条记录上刚刚加的共享普通记录锁。
我们最终看到的结果就是示例 SQL 没有对主键索引中
这种加了锁又释放的方式,一般情况下没什么影响,但是如果因为这种方式造成了死锁,我们不了解这个逻辑,就会有点摸不着头脑了。
4. 总结
可重复读隔离级别下,对某条记录加了锁,要等到事务提交或者回滚时才释放。
读已提交隔离级别下,对某条记录加了锁,如果 server 层或者 InnoDB 发现记录不匹配 where 条件,会马上释放锁。