mysql 表锁 行锁 间隙锁 / 读锁 写锁

按照维度划分

1. 读锁(read lock)

2. 写锁(write lock)

按照粒度划分

1. 表锁(table lock)

2. 行锁(row lock)

3. 间隙锁(Gap Locking)

4.页锁(page lock)

实战演练开始,以下操作基于 mysql 8.0.26

-- 创建一张 用户表;
CREATE TABLE `t_user` (
  `id` int DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 创建  书本表;
CREATE TABLE `t_book` (
  `id` int NOT NULL,
  `nums` int DEFAULT NULL,
  `book_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- 插入模拟数据
insert into `t_user` 
(id,user_name) 
values 
(1,'小明'), 
(3,'小小'),
(4,'444'),
(5,'45699'),
(6,'6666');

insert into `t_book` 
(id,nums,book_name) 
values 
(1,2,'测试测试');

同时打开两个控制台窗口连接到mysql数据库,目前两个连接查询和更新操作都是没有问题的

接下来使用第一个连接给t_user表加一个读锁 命令为: lock table t_user read; (表/又称共享读锁)

发现第一个连接窗口进行读操作没有问题,但是进行写操作会出现一个提示.说明如果给表加了读锁,那么当前连接不允许对该表进行写操作

然后继续用当前连接操作其他表读写,发现也是出现了错误提示,然后继续用第二个窗口来操作t_user表,发现读操作是没有任何问题的

而进行写操作的时候出现了阻塞,接下来我们另外打开一个窗口使用show processlist; 查看当前正在执行操作,发现果然是在阻塞等待第一个锁;

然后第一个窗口连接使用(unlock tables;)解锁之后,发现第二个窗口也马上执行成功了,再去第三个窗口show processlist;也看不到阻塞

总结: 当一个连接对表加了读锁的时候,允许其他连接同时查看,会阻塞其他连接的写操作,并且当前连接也不允许操作其他表和对该表进行写操作.

但是不会影响其他连接操作其它表.

给表加写锁试试 lock table t_user write(独占式表写锁);

写锁总结:当前连接加了写锁之后,不允许操作其它表,可以对当前加锁的表进行读写操作,会阻塞其它连接对该表的读写操作;

发现第一个连接对该表进行读写操作都不会有问题,然后使用第二个连接窗口看到对t_user表进行读/写操作都会阻塞;

使用show processlist;也能看到因为加了写锁而阻塞在那里了.

写锁总结:当前连接加了写锁之后,不允许操作其它表,可以对当前加锁的表进行读写操作,会阻塞其它连接对该表的读写操作;

InndoDb引擎查询索引列值时会使用行锁,如果查询的列值不存在索引,或者索引失效,就会将行锁升级为表锁.

ALTER table `t_user` add index index_id (`id`); --  给id增加普通索引

打开连接之后关闭自动提交事务. set autocommit = 0; 不然mysql默认都是会事务自动提交;

-- 查看当前连接事务自动提交状态
show variables like 'autocommit'; 
-- 关闭事务自动提交
set autocommit=0; 

看看事务自动提交是否已经关闭,看看表索引是否建立成功;

行锁演示开始

我们发现第一个连接和第二个连接同时对一行进行查询操作并不会有问题但是在第一个连接对id为1的这一行进行更新操作

未提交事务的时候,接着第二个连接马上开始查询id为1数据发现查询出来的还是未更新的旧数据.所以发现Innodb实现了可

重复读隔离级别,不会出现脏读问题, 不可重复读其实也可以演示这边就没截图了,就是第一个连接commit;但是第二个还没进

行commit,也是读取不到第一个连接提交事务的数据的

然后连接一操作id为1的数据,不commit,我们发现连接二还是可以操作不同行id为2的数据;说明此时是不存在表锁冲突的

使用二个连接更新同一行数据,第二个链接会被阻塞等待 , 然后一直等待阻塞到默认等待超时时长第一个连接还未释放锁

资源的时候,就会出现等待超时的一个错误,放弃执行该语句;

总结: Innodb引擎下多个连接操作数据条件同一列为索引列值时,允许同时读,保证了不会出现脏读,如有一个连接在进行更新

操作,其他操作同一行数据的连接会被阻塞等待,直到其他事物被提交或者一直等到超时时间结束才会终止等待.

行锁升级为表锁演示.

上面已经演示过如果条件是索引列值的时候,锁住的只是当前操作行,如果条件不是索引列值或者索引失效的时候会导致锁表操作;

-- 给t_user表user_name字段也加上一个索引演示索引失效情况
ALTER table `t_user` add index index_user_name (`user_name`); --  给id增加普通索引

加上索引之后再尝试修改发现表锁不存在了,不同行操作之间不会有影响;然后再操作去掉单引号的时候又开始阻塞了,因为sql条件如果查的是字符列

但是不加单引号会导致索引失效.这就是索引失效导致行锁升级为表锁的一个例子;

间隙锁(gap Lock)

目前可以看到数据中一直都没有id为2的这一条数据,我用第一个连接窗口进行where id >1 and id < 5 范围操作的时候

然后用连接二尝试去插入id为2的一条数据,发现被阻塞住了,这就是next-key lock

总结: 使用单列索引在精准条件下只会使用到行锁,例如 id = 1 这种等值操作,使用单列索引在进行范围检索,或者精准检索而结果

不存在时会产生Next-Key Lock;

主动给一行加一个排它锁,select X for update;

set autocommit = 1;

bengin;
select * from t_user where id = 1 for update ;
update t_user set user_name = '小黄' where id = 1;
commit;

页锁开始 (~~~~ 找到资源在补上叭~害)

-- 一些资源占用查看命令,下篇再出详解~
show status like 'innodb_row_lock%'
show processlist; 

最后总结:一定要多动手多操作多验证