MySQL高级之锁

最新推荐文章于 2023-06-12 14:05:19 发布

原创最新推荐文章于 2023-06-12 14:05:19 发布 · 254 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

19 篇文章

订阅专栏

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)读锁(共享锁，S锁(Shared)):针对同一份数据，多个读操作可以同时进行而不会互相影响写锁(排它锁，X锁(eXclusive)):当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为全局锁、表锁和行锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。

表锁

每次操作锁住整张表。开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低;一般用在整表数据迁移的场景。

基本操作

手动加锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

案例分析

读锁：

时刻	会话1	会话2
T1	LOCK TABLE t1 READ;
T2	SELECT * FROM t1; --能查询
T3		SELECT * FROM t1; --能查询
T4		UPDATE t1 SET b = 10 WHERE id = 1; --阻塞
T5	UPDATE t1 SET b = 10 WHERE id = 1; -- Table 't1' was locked with a READ lock and can't be updated（异常）
T6	unlock tables;
T7		T4时刻会话2操作得以执行

总结：当前session和其他session都可以读该表当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

写锁：

时刻	会话1	会话2
T1	LOCK TABLE t1 WRITE;
T2	SELECT * FROM t1; --能查询
T3		SELECT * FROM t1; --阻塞
T4	UPDATE t1 SET b = 10 WHERE id = 1; --能更新
T5	unlock tables;
T6		T3时刻会话2操作得以执行
T7	LOCK TABLE t1 WRITE;
T8		UPDATE t1 SET b = 10 WHERE id = 1; --阻塞
T9	unlock tables;
T10		T8时刻会话2操作得以执行

总结：当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

行锁

每次操作锁住一行数据。开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低，并发度最高。

时刻	会话1	会话2
T1	BEGIN;
T2		BEGIN
T3	UPDATE t1 SET b = 10 WHERE id = 1;
T4		UPDATE t1 SET b = 9 WHERE id = 1; --阻塞
T5	COMMIT;
T6		T4 时刻会话2得以执行
T7
T8	BEGIN;
T9
T10		UPDATE t1 SET b = 9 WHERE id = 1; --能更新
T11	UPDATE t1 SET b = 9 WHERE id = 1; --能更新
T12	COMMIT；

从上面的例子可以看到在事物开启时，id=1这行在需要的时候才加锁(T3/T11执行时)。

间隙锁

间隙锁，锁的就是两个值之间的空隙。一个索引有 1 2 3 10 20 这几个值，当session1下执行update account set name = 'zhuge' where id > 8 and id <18;时，其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。

临健锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

锁升级

无索引行锁会升级为表锁锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。

死锁

案例

Session_1执行:select * from account where id=1 for update;
Session_2执行:select * from account where id=2 for update;
Session_1执行:select * from account where id=2 for update;
Session_2执行:select * from account where id=1 for update;
查看近期死锁日志信息:show engine innodb status\G;