锁

最新推荐文章于 2024-03-21 10:30:00 发布

命运的左岸

最新推荐文章于 2024-03-21 10:30:00 发布

阅读量780

点赞数 1

分类专栏： MySQL 文章标签：锁

本文链接：https://blog.csdn.net/mingyundezuoan/article/details/78808017

版权

MySQL 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

锁

InnoDB 存储引擎中的锁

锁分两类：悲观锁和乐观锁
悲观锁分为：共享锁和排它锁
排它锁：行级别锁，包含记录锁、间隙锁、防插入锁

DML、DQL与锁

select——不加锁
select lock in share mode——共享锁，不是意向共享锁
select for update——排它锁，不是意向排它锁
insert——排它锁
update——排它锁
delete——排它锁

示例： `select lock in share mode——共享锁，不是意向共享锁；select for update——排它锁，不是意向排它锁`，详情：示例十二

前提

开启事务后，锁才生效；即在事务中使用锁
存储引擎支持锁
MyISAM支持表级别的锁
InnoDB支持表级别的锁、支持行级别的锁
InnoDB的锁通过锁定索引来实现，如果查询条件中有主键则锁定主键，如果有索引则先锁定对应索引然后再锁定对应的主键（可能造成死锁），如果连索引都没有则会锁定整个数据表

锁的粒度

概述

作用：并发控制的基本方法
分类：表级锁、行级锁
表锁：MySQL独立于存储引擎提供表锁，即表锁与存储器类型无关
行锁：与存储引擎有关，如 InnoDB存储引擎支持行锁，MyISAM存储引擎不支持

排它锁`select * for update`是行锁 or 表锁

示例十一：select for update 行锁与表锁

示例总结

查询条件只有索引且查询内容存在：行锁
查询条件只有索引但查询内容不存在：无锁
查询条件只有索引且查询内容不确定：取决于查询SQL，如果是全表扫描，则为表锁；否则行锁
查询条件未使用索引：表锁
查询条件索引与非索引项混合：取决于查询SQL

总结

InnoDB的锁通过锁定索引来实现，如果查询条件中有主键则锁定主键，如果有索引则先锁定对应索引然后再锁定对应的主键（可能造成死锁），如果连索引都没有则会锁定整个数据表

// 1.ALTER TABLE 表锁；若某行数据被锁定，无法获取对整张表的锁定权限，则需要等待行锁的释放
// console 1 
mysql> begin ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 3 for update ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+

// console 2 
mysql> set autocommit = 0 ;
Query OK, 0 rows affected

mysql> alter table t_order modify column name varchar(32) ;
// 处于等待中，一直无响应

// console 1 
mysql> commit ;
Query OK, 0 rows affected
// console 2 
mysql> alter table t_order modify column name varchar(32) ;
Query OK, 0 rows affected // 直到 console 1中 commit 执行后才有此输出

行锁类型

共享锁(shared lock，S锁)：即读锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；在一事务中对某行数据添加共享锁后，事务未结束时，在另一个事务中可以获取同一行数据的共享锁，但不能获取排他锁
解释：

// 语法
SELECT ... LOCK IN SHARE MODE

// 示例
// 1. session 1 获取共享锁，session 2 依然可以获取共享锁
// session 1
mysql> set autocommit = 0 ;
Query OK, 0 rows affected

mysql> select * from t_order where id = 1 lock in share mode ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set
// session 2 
mysql> set autocommit = 0 ;
Query OK, 0 rows affected

mysql> select * from t_order where id = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

// 2.session 1 中获取共享锁，session 2 中不可以获取排它锁
mysql> commit ;
Query OK, 0 rows affected

mysql> set autocommit = 0 ;
Query OK, 0 rows affected

mysql> select * from t_order where id = 1 for update ;
1205 - Unknown error 1205

排它锁(exclusive lock，X)：即写锁，允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

// 语法
SELECT ... FOR UPDATE

示例十一

行锁级别

行锁分为三级，粒度从小到大依次是：
记录锁（Record Lock）：单行，锁直接加在索引记录上面
间隔锁（Gap Lock）：一个开区间内的多行，锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间
防插入锁（Next-Key Lock）：一个前开后闭区间内的多行，实际上是记录锁和间隔锁的结合；默认情况下，InnoDB工作在可重复读隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的
防插入锁的区间是根据索引来确定的。对于没有索引的列，则根据该列的聚簇索引（简单而言，聚簇索引是根据数据在物理空间中的分布疏密情况计算得出的聚集点）决定
均是排他锁
在可重复读级别下，InnoDB以Next-Key Lock的方式对索引加锁；在读已提交级别下，InnoDB以Index-Record Lock的方式对索引加锁
被加锁的索引如果不是聚族索引，那被锁的索引所指向的聚族索引以及其它指向相同聚族索引的索引也会被加锁
SELECT * FROM … LOCK IN SHARE MODE对索引加共享锁；SELECT * FROM … FOR UPDATE对索引加排他锁
SELECT * FROM … 是非阻塞式读，（除Serializable级别）不会对索引加锁。在读已提交级别下，总是查询记录的最新、有效的版本；在可重复读级别下，会记住第一次查询时的版本，之后的查询会基于该版本。例外的情况是在串行化级别，这时会以Next-Key Lock的方式对索引加共享锁。
UPDATE … WHERE 与DELETE … WHERE对索引加排他锁
INSERT INTO … 以Index-Record Lock的方式对索引加排他锁

insert——记录锁

update——防插入锁

delete——防插入锁

此外，若查询的列包含唯一索引或主键，则行锁将被自动降级到记录锁

表锁类型

概述

InnoDB支持多粒度加锁(multiple granularity locking)，从而允许对记录和表同时加锁。为此，InnoDB引入意向锁(intention locks)，意向锁是针对表的；“意向”表明并不是真的已经获取到了锁，而是未来极有可能会主动获取锁

操作语句

LOCK TABLE(S) {tableName} {lockType} [, {tableName} {lockType}, ...]

UNLOCK TABLES

锁类型lockType的取值有：

READ：持有该锁的事务只能读不能写，允许其它事务获取READ锁，其它事务也可以在不显示获取READ锁是情况下读表

READ LOCAL：对于InnoDB而言与READ相同，不推荐使用

WRITE：持有该锁的事务能读和写表，不允许其它事务操作该表，其它尝试获取该表WRITE锁的事务将被挂起直到锁释放

LOW PRIORITY WRITE：LOW PRIORITY修饰的锁表请求将在竞争WRITE锁时拥有较低的优先级

只提供了UNLOCK TABLES命令，一旦显式调用释放表锁，则释放全部表锁
LOCK TABLES命令将首先隐式释放事务当时持有的全部表锁，再进行锁表；这就要求应在一条LOCK TABLES语句中带上全部需要锁定的表，而不能逐条进行锁定
事务开启时（start transaction），一条UNLOCK TABLES语句将被隐式调用，确保当前没有表锁

作用

(1)事务在对表T中的记录获取S锁前，先要获取表T的IS锁或者更强的锁;
(2)事务在获取表T中记录的X锁前，先要获取表T的IX锁。

InnoDB的锁相容性矩阵

是否冲突	S锁	X锁	IS锁	IX锁
S锁	否	是	否	是
X锁	是	是	是	是
IS锁	否	是	否	是
IX锁	是	是	否	否

作用

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务;反之，如果两者不兼容，该事务就要等待锁释放。意向锁只会阻塞其它事务对表的请求，例如，LOCK TABLES …WRITE，意向锁的主要目的是表明该事务将要或者正在对表中的记录加锁。使用封锁机制来进行并发控制，一个比较重要的问题就是死锁

参考资料

命运的左岸

关注

1
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
锁

锁InnoDB 存储引擎中的锁锁分两类：悲观锁和乐观锁悲观锁分为：共享锁和排它锁排它锁：行级别锁，包含记录锁、间隙锁、防插入锁DML、DQL与锁select——不加锁select lock in share mode——共享锁，不是意向共享锁select for update——排它锁，不是意向排它锁insert——排它锁update——排它锁delete——排它锁示例：selec
复制链接

扫一扫