同步操作将从 turnon/blog 强制同步,此操作会覆盖自 Fork 仓库以来所做的任何修改,且无法恢复!!!
确定后同步将在后台操作,完成时将刷新页面,请耐心等待。
title: Mysql 锁
date: 2020-09-07 07:54:19
categories:
- 数据库
- 关系型数据库
- Mysql
tags:
- 数据库
- 关系型数据库
- Mysql
- 锁
permalink: /pages/f1f151/
确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性,乐观锁和悲观锁是并发控制主要采用的技术手段。
悲观锁
- 假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作
COMMIT
)乐观锁
- 假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。
【示例】乐观锁示例
商品 goods 表中有一个字段 status,status 为 1 代表商品未被下单,status 为 2 代表商品已经被下单,那么我们对某个商品下单时必须确保该商品 status 为 1。假设商品的 id 为 1。
select (status,status,version) from t_goods where id=#{id}
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
更详细的乐观锁说可以参考:使用 mysql 乐观锁解决并发问题
从数据库的锁粒度来看,MySQL 中提供了两种封锁粒度:行级锁和表级锁。
应该尽量只锁定需要修改的那部分数据,而不是所有的资源。锁定的数据量越少,锁竞争的发生频率就越小,系统的并发程度就越高。但是加锁需要消耗资源,锁的各种操作(包括获取锁、释放锁、以及检查锁状态)都会增加系统开销。因此锁粒度越小,系统开销就越大。
在选择锁粒度时,需要在锁开销和并发程度之间做一个权衡。
在 InnoDB
中,行锁是通过给索引上的索引项加锁来实现的。如果没有索引,InnoDB
将会通过隐藏的聚簇索引来对记录加锁。
SELECT ... FOR UPDATE;
SELECT ... LOCK IN SHARE MODE;
写锁和读锁的关系,简言之:独享锁存在,其他事务就不能做任何操作。
InnoDB
下的行锁、间隙锁、next-key 锁统统属于独享锁。
当存在表级锁和行级锁的情况下,必须先申请意向锁(表级锁,但不是真的加锁),再获取行级锁。使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。
意向锁是 InnoDB
自动加的,不需要用户干预。
在存在行级锁和表级锁的情况下,事务 T 想要对表 A 加 X 锁,就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁,那么就需要对表 A 的每一行都检测一次,这是非常耗时的。
意向锁规定:
通过引入意向锁,事务 T 想要对表 A 加 X 锁,只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁,如果加了就表示有其它事务正在使用这个表或者表中某一行的锁,因此事务 T 加 X 锁失败。
各种锁的兼容关系如下:
- | X | IX | S | IS |
---|---|---|---|---|
X | ❌ | ❌ | ❌ | ❌ |
IX | ❌ | ✔️ | ❌ | ✔️ |
S | ❌ | ❌ | ✔️ | ✔️ |
IS | ❌ | ✔️ | ✔️ | ✔️ |
解释如下:
多版本并发控制(Multi-Version Concurrency Control, MVCC)可以视为行级锁的一个变种。它在很多情况下都避免了加锁操作,因此开销更低。不仅是 Mysql,包括 Oracle、PostgreSQL 等其他数据库都实现了各自的 MVCC,实现机制没有统一标准。
MVCC 是 InnoDB
存储引擎实现隔离级别的一种具体方式,用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行,要求很低,无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。
加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作,因此又引入了读写锁来避免不必要的加锁操作,例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的。
MVCC 的思想是:
CopyOnWrite
类似。InnoDB 的 MVCC 实现是:在每行记录后面保存两个隐藏列,一个列保存行的创建时间,另一个列保存行的过期时间(这里的时间是指系统版本号)。每开始一个新事务,系统版本号会自动递增,事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。
SYS_ID
:是一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。TRX_ID
:事务开始时的系统版本号。MVCC 的多版本指的是多个版本的快照,快照存储在 Undo 日志中,该日志通过回滚指针 ROLL_PTR
把一个数据行的所有快照连接起来。
例如在 MySQL 创建一个表 t,包含主键 id 和一个字段 x。我们先插入一个数据行,然后对该数据行执行两次更新操作。
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;
因为没有使用 START TRANSACTION
将上面的操作当成一个事务来执行,根据 MySQL 的 AUTOCOMMIT
机制,每个操作都会被当成一个事务来执行,所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外,还记录了一个 bit 的 DEL 字段,用于标记是否被删除。
INSERT
、UPDATE
、DELETE
操作会创建一个日志,并将事务版本号 TRX_ID
写入。DELETE
可以看成是一个特殊的 UPDATE
,还会额外将 DEL 字段设置为 1。
MVCC 维护了一个一致性读视图 consistent read view
,主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}
,还有该列表的最小值 TRX_ID_MIN
和 TRX_ID_MAX
。
这样,对于当前事务的启动瞬间来说,一个数据版本的 row trx_id,有以下几种可能:
在进行 SELECT
操作时,根据数据行快照的 TRX_ID
与 TRX_ID_MIN
和 TRX_ID_MAX
之间的关系,从而判断数据行快照是否可以使用:
TRX_ID
< TRX_ID_MIN
,表示该数据行快照时在当前所有未提交事务之前进行更改的,因此可以使用。TRX_ID
> TRX_ID_MAX
,表示该数据行快照是在事务启动之后被更改的,因此不可使用。TRX_ID_MIN
<= TRX_ID
<= TRX_ID_MAX
,需要根据隔离级别再进行判断:
TRX_ID
在 TRX_IDs
列表中,表示该数据行快照对应的事务还未提交,则该快照不可使用。否则表示已经提交,可以使用。在数据行快照不可使用的情况下,需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照,再进行上面的判断。
快照读
MVCC 的 SELECT 操作是快照中的数据,不需要进行加锁操作。
SELECT * FROM table ...;
当前读
MVCC 其它会对数据库进行修改的操作(INSERT、UPDATE、DELETE)需要进行加锁操作,从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁,而只是避免了 SELECT 的加锁操作。
INSERT;
UPDATE;
DELETE;
在进行 SELECT 操作时,可以强制指定进行加锁操作。以下第一个语句需要加 S 锁,第二个需要加 X 锁。
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;
行锁的具体实现算法有三种:record lock、gap lock 以及 next-key lock。
Record Lock
- 行锁对索引项加锁,若没有索引则使用表锁。Gap Lock
- 对索引项之间的间隙加锁。锁定索引之间的间隙,但是不包含索引本身。例如当一个事务执行以下语句,其它事务就不能在 t.c 中插入 15:SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;
。在 MySQL 中,gap lock 默认是开启的,即 innodb_locks_unsafe_for_binlog
参数值是 disable 的,且 MySQL 中默认的是 RR 事务隔离级别。Next-key lock
-它是 Record Lock
和 Gap Lock
的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙。它锁定一个前开后闭区间。只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock。在 Select
、Update
和 Delete
时,除了基于唯一索引的查询之外,其它索引查询时都会获取 gap lock 或 next-key lock,即锁住其扫描的范围。主键索引也属于唯一索引,所以主键索引是不会使用 gap lock 或 next-key lock。
MVCC 不能解决幻读问题,Next-Key 锁就是为了解决幻读问题。在可重复读(REPEATABLE READ
)隔离级别下,使用 MVCC + Next-Key 锁 可以解决幻读问题。
索引分为主键索引和非主键索引两种,如果一条 SQL 语句操作了主键索引,MySQL 就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL 会先锁定该非主键索引,再锁定相关的主键索引。在 UPDATE
、DELETE
操作时,MySQL 不仅锁定 WHERE
条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的 next-key lock
。
当两个事务同时执行,一个锁住了主键索引,在等待其他相关索引。另一个锁定了非主键索引,在等待主键索引。这样就会发生死锁。发生死锁后,InnoDB
一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。