mysql在select ······ for update 在什么情况下加什么锁
准备环境:
select @@version;
select @@autocommit;
set @@autocommit=0;
CREATE TABLE `user_info_tab` (
`id` int NOT NULL AUTO_INCREMENT,1. 1.
`user_name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`status` varchar(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570072 DEFAULT CHARSET=utf8mb3;
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('杰伦',18,'深圳','1');
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('奕迅',26,'湛江','0');
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('俊杰',28,'广州','1');
RC隔离级别
-- 设置隔离级别
select @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1 RC + 唯一索引
更新同一条记录
事务2被阻塞
更新其他记录
事务2正常执行
因此在RC隔离级别下,如果条件是唯一索引,那么select...for update加的是行锁
-- 查看加锁情况
SELECT * FROM performance_schema.data_locks;
select * from user_info_tab where user_name ='杰伦' for update
-- 语句一共加了3把锁 如下
1, IX 意向排他锁:当事务准备在某条记录上加上X锁时,需要在表级别加一个IX锁。如select ... for update,要给表设置IX锁;意向锁仅仅表明意向的锁,意向锁之间不会互斥,是可以并行的
2,这条记录的唯一索引和主键索引都加了X锁,因为如果并发的一个SQL,通过主键索引来更新,若没有将主键索引上的记录加锁,那么并发的update就会感知不到,违背了同一记录上的更新/删除需要串行执行的约束。
2 RC + 主键索引
更新同一条记录
事务2被阻塞
查看加锁情况
会加两把锁:分表是IX意向排他锁(表锁,不影响插入)、一把X排他锁(行锁,对于主键索引)
3 RC 隔离级别 + 普通索引
-- 添加普通索引
alter table user_info_tab add index idx_city (city);
事务2被阻塞
IX意向排他锁(表锁)、两把X排他锁(行锁,分别对应普通索引的X锁,对应主键的X锁)。
未命中数据库表的记录,只加 IX意向排他锁(表锁,不影响插入)
4 RC 隔离级别 + 无索引
IX意向排他锁(表锁)、一把X排他锁(行锁,对应主键的X锁)。
age列上没有索引,MySQL会走聚簇(主键)索引进行全表扫描过滤。每条记录都会加上X锁。但为了效率考虑,MySQL在这方面进行了改进,在扫描过程中,若记录不满足过滤条件,会进行解锁操作。同时优化违背了2PL原则。
RR 隔离级别
-- 设置RR隔离级别
set global transaction isolation level repeatable read;
select @@transaction_isolation;
1 RR + 唯一索引
-- RR + 唯一索引
## 事务1
begin;
select *from user_info_tab where user_name ='杰伦'for update;
## 事务2
begin;
update user_info_tab set age= '30' where id = '1570074';
update user_info_tab set age= '26' where id = '1570072';
查询条件是唯一索引,命中数据库表记录时,一共会加三把锁:一把IX意向排他锁 (表锁,不影响插入),一把对应主键的X排他锁(行锁),一把对应唯一索引的X排他锁 (行锁)
2 RR + 主键
跟RC隔离级别一样,会加两把锁:一把IX意向排他锁(表锁,不影响插入),一把对应主键的X排他锁(行锁,影响对应主键那一行的插入)。
3 RR + 普通索引
在RR隔离级别下,如果select...for update的查询条件是普通索引的话,除了会加X锁,IX锁,还会加Gap 锁。
Gap锁的提出,是为了解决幻读问题引入的,它是一种加在两个索引之间的锁。
事务A
事务B
4 RR + 无索引
IX锁(表级别,意向排他锁)+ 每一行的数据记录加X排他锁
RR隔离级别下,对于select...for update,查询条件无索引的话,会加一个IX锁(表锁,不影响插入),每一行实际记录行的X锁,还有对应于supremum pseudo-record的虚拟全表行锁。这种场景,通俗点讲,其实就是锁表了。
posted on 2024-05-16 18:23 little_mushroom 阅读(7) 评论(0) 编辑 收藏 举报