mysql innodb中的锁

最新推荐文章于 2021-03-24 05:23:02 发布

CodeForCode

最新推荐文章于 2021-03-24 05:23:02 发布

阅读量220

点赞数

分类专栏：数据库文章标签： mysql 数据库数据库锁 sql

本文链接：https://blog.csdn.net/zibaihe007/article/details/112899466

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.MySQL中的锁

锁是MySQL中对多会话/事务并发安全支持的一种悲观机制，MySQL服务支持表锁（LOCK TABLES），这种粗粒度的锁消耗较小，但并发度不高，innodb存储引擎为了提高并发度，引入了意向锁，在意向锁之上实现了行级锁/区间锁/间隙锁/邻键锁/自增锁等。

2.表锁

MySQL官方文档对锁的释义如下，大意为

MySQL使客户端会话能够显式获取表锁，以便与其他会话协作访问表，或防止其他会话在会话需要互斥访问时修改表。会话只能为其自身获取或释放锁。一个会话无法获取另一会话的锁，也不能释放另一会话持有的锁。

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

2.1 加锁/解锁的方式

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...

lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE } UNLOCK TABLES

加读锁(共享锁)

LOCK TABLES t READ;

对一张表加了读锁后，其他会话可以对该表加读锁，但不能加写锁；其他会话可以对该表进行查询，但不能新增/删除/修改/DROP/TRUNCATE/ALTER；读锁是可重入的，一个会话多次加读锁效果一致；对该表添加读锁后，任何会话不允许对该表进行写操作；锁升级，在持有表的读锁后，在无任何表对该表加锁的情况下，可以对该表加写锁，读锁释放。

加写锁(排他锁)

LOCK TABLE t WRITE;

对一张表加了写锁后，其他会话不可以对该表加读锁，也不能加写锁；其他会话不允许对该表进行任何操作，包括SELECT/DELETE/UPDATE/TRUNCATE/ALTER；写锁是可重入的，一个会话多次加写锁效果一致；锁降级，在持有表的写锁后，可以对该表加读锁，写锁释放。

释放锁

UNLOCK TABLES;

该语句会释放当前会话中对所有表的读锁与写锁。

2.2 限制

2.2.1 对多张表的操作

对多表的操作，必须对每张表都进行加锁，否则会报错。

LOCK TABLE t READ;
SELECT * FROM t;
SELECT * FROM w; -- error

锁多张表

LOCK TABLE t READ, w READ;
SELECT * FROM t;
SELECT * FROM w;

2.2.2 同一张表加锁多次

对同一张表查询/修改多次时，必须加多次锁，且需要定义别名。

LOCK TABLE t READ, t AS s READ;
SELECT * FROM t WHERE id IN (SELECT id FROM t) --error
SELECT * FROM t WHERE id IN (SELECT id FROM t AS s);

LOCK TABLE t WRITE, t AS s READ;
INSERT INTO t SELECT * FROM t; --error
INSERT INTO t SELECT * FROM t AS s;

2.2.3 使用别名

单独使用别名加锁时，无法再使用表名查询。

LOCK TABLE t AS s READ;
SELECT * FROM t; -- error
SELECT * FROM t AS s;

2.3 锁释放

如果一个会话持有一些表的锁后，再次获取其他表的锁之前，会隐式地释放之前获取到的锁。

LOCK TABLE t READ; -- will be release
LOCK TABLE w READ;
SELECT * FROM t; -- error

一些ALTER操作可能会导致锁的释放，如rename，因为表名发生了改变。

LOCK TABLE w READ;
ALTER TABLE w RENAME s; -- lock on w will be release

会话连接中断（主动/被动）都会导致锁的释放。

2.4 事务

表锁不是事务安全的，unlock tables 会提交任何当前会话中活跃的事务。

-- Serializable isolation
BEGIN;
LOCK TABLE t READ;
SELECT * FROM t;
UNLOCK TABLES; -- it will submit transaction implicitly

上述情况仅会发生在之前获取过锁的情况，若无加锁操作，则不会隐式提交事务。

-- Serializable isolation
BEGIN;
-- LOCK TABLE t READ;
SELECT * FROM t;
UNLOCK TABLES; -- it will NOT submit transaction implicitly

开启一个新事务会隐式提交当前会话中活跃的事务并且隐式地释放获取到的锁。

-- Serializable isolation
BEGIN; -- tx1
LOCK TABLE t READ;
SELECT * FROM t;
BEGIN; -- implicit commit tx1 and release lock on t

官方推荐的事务与锁合用方案。

SET AUTOCOMMIT=0;
LOCK TABLE t READ; -- without begin/start
SELECT * FROM t;
UPDATE ...
COMMIT;
UNLOCK TABLES;

Rollback不会隐式释放锁。

3.意向锁与行级锁

3.1 读写锁

InnoDB实现了两种标准的行级锁：共享锁/读锁(S)和排他锁/写锁(X)。多个事务可以同时获取某行数据的共享锁，只能有一个事务可以获取某行数据的排他锁。事务T1在持有数据r的排他锁时，其他事务对数据r的读写必须要等到T1对数据r的锁释放后才能开始。

3.2 意向锁

InnoDB为了实现多粒度锁，引入了意向锁的概念，意向锁为指示事务稍后对表中的行需要哪种类型的锁（共享锁/排他锁），分为两类：意向共享锁(IS)，意向排他锁(IX)。意向锁都是表级锁，官方定义如下。

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.

An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

加意向锁的方式:

SELECT * FROM t LOCK IN SHARE MODE;

SELECT * FROM t FOR UPDATE;

意向锁协议：

Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.

Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

在一个事务获取表中某一行的共享锁时，必须先获取到这张表的意向共享锁；在一个事务获取表中某一行的排它锁时，必须先获取这张表的意向排他锁。

表级锁的兼容性矩阵：

	`X`	`IX`	`S`	`IS`
`X`	Conflict	Conflict	Conflict	Conflict
`IX`	Conflict	Compatible	Conflict	Compatible
`S`	Conflict	Conflict	Compatible	Compatible
`IS`	Conflict	Compatible	Compatible	Compatible

意向锁除了全表请求（例如LOCK TABLES ... WRITE）外，不阻止任何其他内容。意向锁的主要目的是表明某人正在锁定表中的行或要锁定表中的行。

3.3 行级锁

3.3.1 记录锁

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

记录锁是基于索引记录的，对于没有索引的表，innodb会默认创建基于ID的聚簇索引。

-- T1
BEGIN;
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;

SELECT * FROM t WHERE id=10 LOCK IN SHARE MODE;
COMMIT;

-- T2
BEGIN;
SELECT * FROM t WHERE id=1 FOR UPDATE; -- pending
UPDATE t SET value='D' WHERE id=1; -- pending
DELETE t WHERE id=1; -- pending
TRUNCATE t; -- pending
INSERT INTO t(id,value) VALUES(10,'a'); -- what will happen
COMMIT;

在T1执行完查询且未提交时，T2基于id=1的任何写意向锁或更新/删除操作都会pending。

思考：当T1锁住的id是一个不存在的值，T1能否对其进行插入操作？

3.3.2 间隙锁

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

间隙锁也是基于索引记录的锁，可以锁住一个区间，对修改，只对现存id进行锁定，对新增，锁定区间内的所有值。

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

在这里还值得注意的是，可以通过不同的事务将冲突的锁保持在间隙上。例如，事务A可以在间隙上保留一个共享的间隙锁（间隙S锁），而事务B可以在同一间隙上保留排他的间隙锁（间隙X锁）。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保留在记录上的间隙锁。

-- T1
BEGIN;
SELECT * FROM t WHERE id>1 LOCK IN SHARE MODE;

SELECT * FROM t WHERE id>3 LOCK IN SHARE MODE; -- lock conflict 

COMMIT;

-- T2
BEGIN;
SELECT * FROM t WHERE id>0 FOR UPDATE; -- pending
SELECT * FROM t WHERE id>1 FOR UPDATE; -- pending
SELECT * FROM t WHERE id>2 FOR UPDATE;
SELECT * FROM t WHERE id=1 FOR UPDATE;
SELECT * FROM t WHERE id<1 FOR UPDATE;
SELECT * FROM t WHERE id<2 FOR UPDATE;
SELECT * FROM t WHERE id<3 FOR UPDATE;
INSERT INTO t(id,value) values(10,'a'); -- pending

SELECT * FROM t WHERE id>3 FOR UPDATE; -- lock conflict

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

间隙锁定可以显式禁用。如果将事务隔离级别更改为READ COMMITTED，则会发生这种情况。在这种情况下，将禁用间隙锁定来进行搜索和索引扫描，并且间隙锁定仅用于外键约束检查和重复键检查。

3.3.3 邻键锁

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

邻键锁是记录锁与记录锁之前的间隙锁结合而成的锁。

表t现有ID为(1,2,3,10) ，在RR隔离级别下邻键为(-∞,1],(1,2],(2,3],(3,10],(3,+∞)，当锁住id为2时，不会影响其他邻键；锁住id为10的数据时，会锁住(4,5,6,7,8,9)，不影响其他邻键。

-- T1
BEGIN;
SELECT * FROM t WHERE id=10 LOCK IN SHARE MODE;
COMMIT;

-- T2
BEGIN;
SELECT * FROM t WHERE id=3 FOR UPDATE;
INSERT INTO t(id,value) values(4,'a'); -- pending
INSERT INTO t(id,value) values(3,'b'); -- duplicate key
INSERT INTO t(id,value) values(-1,'b'); -- success
COMMIT;