MySQL--行级锁与表级锁

​

简介

说明

        本文介绍MySQL的行级锁与表级锁。

锁的类型

锁类型	死锁	锁定粒度（并发性）	适用场景
表级锁	不会死锁	最大，发生锁冲突的概率最大（并发性低）	场景1：读多写少； 场景2：写特别多。若用行锁，会导致事务执行效率低，可能造成其他事务长时间锁等待和锁冲突。
行级锁	会死锁	最小，发生锁冲突的概率最低（并发性最高）	并发量大。
页面锁	会死锁	居中，并发一般

不同的存储引擎的锁机制

存储引擎	支持的锁	说明
InnoDB	表级锁、行级锁（默认）。	InnoDB行级锁基于索引实现。若查询字段无索引或索引失效，则使用表锁。
MyISAM	表级锁
MEMORY	表级锁
BDB	表级锁、页面锁

行锁

概述

说明

        行锁只在InnoDB中。有两种锁：共享锁（Share Lock）（也称为：读锁）、独占锁（Exclusive Lock）。共享锁也称为：读锁；独占锁也称为：写锁。

InnoDB实现了以下两种类型的行锁。意向锁是InnoDB自动加的，不需用户干预。

• 共享锁（S）：允许获得共享锁的事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。（Intention Share）
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。（Intention Exclusive）

上述锁模式的兼容情况具体如下表所示。如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

共享锁与独占锁

项	共享锁（也称为：读锁）	独占锁（也称为：写锁）
加锁语句	SELECT ... LOCK IN SHARE MODE	SELECT ... FOR UPDATE
定义	在读取的行上设置一个共享锁。         共享锁允许其它session读数据但不允许修改（修改会导致等待锁超时）。         行读取的是最新的数据，如果它被其它事务使用中而没有提交，读锁将被阻塞直到那个事务结束。 官方文档： SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.	在读取行上设置一个排他锁。         阻止其他session读写行数据。 官方文档： SELECT ... FOR UPDATE sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing.
锁类型	对记录加共享锁。 在当前事务内，可再加X锁。 其他session可以读取这些记录，也可以继续添加S锁，不能加X锁。 其他session无法修改这些记录，修改时会阻塞，直到这加锁的事务结束(否则直接锁等待超时)	对记录加排它锁。 其他session不能加S锁或者X锁。 其他session不能锁定读，可以快照读。 快照读：纯select语句，不带IS锁或者IX锁。读取修改行之前的数据版本,会出现脏读，也称为非锁定读，因为不需要等待被访问行的锁的释放。非锁定读的方式极大提高了数据库的并发性。在InnoDB存储引擎中，这是默认的读取方式。

相同点

项	共享锁/独占锁
加锁方式	自动加锁。 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁； 对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：         共享锁：SELECT * FROM tableName WHERE ... + LOCK IN SHARE MODE     排他锁：SELECT * FROM tableName WHERE ... + FOR UPDATE
事务	必须在事务中使用。

不同点 

共享锁（也称为：读锁）	独占锁（也称为：写锁）
1. 会造成死锁。 2. 适用于本事务中不会修改数据的情况。 3. 只锁覆盖索引。如果要用lock in share mode必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。 4. 两个会话可以都用select ... lock in share mode锁定某行（某个间隙锁范围），可以读取，但不能修改（修改时会阻塞）。 说明： 1. 如果两个事务同时获取某行数据共享锁，事务A更新该行发生阻塞，事务B提交之后，事务A更新成功。 2. 如果两个事务同时获取某行数据共享锁，事务A更新该行发生阻塞，事务B更新也会发生阻塞，事务B提交之后，事务A更新失败。	1. 不会造成死锁。 2. 适用于本事务中会修改数据的情况。 3. 执行 for update 时， 系统会认为你接下来要更新数据，因此会顺便给主键索引上。 4. 两个会话不能都用select ... for update锁定某行（某个间隙锁范围）,对话A用了select ... for update锁定某行（某个间隙锁范围）后，对话B再用select ... for update锁定这行（这个间隙锁范围）会直接阻塞（select ...for update锁定其他行（其他间隙范围）不会阻塞，可以读取到。）。 说明： 若事务A使用select ... for update锁定某行（某个间隙锁范围），则事务B修改这行（这个间隙锁范围）会阻塞，事务A提交后，事务B更新成功。

注意事项

未加索引时，两种行锁情况为（使用表锁）： 

- 事务1获取某行数据共享锁，其他事务可以获取不同行数据的共享锁，不可以获取不同行数据的排他锁 

- 事务1获取某行数据排他锁，其他事务不可以获取不同行数据的共享锁、排他锁

加索引后，两种行锁为（使用行锁）：

- 事务1获取某行数据共享锁，其他事务可以获取不同行数据的排他锁

- 事务1获取某行数据排他锁，其他事务可以获取不同行数据的共享锁、排他锁

原理

        有一个先进先出的队列，队列中存放的都是所有锁，包括共享锁 和 排它锁。如下FIFO队列示意图，按照从左到右的先进先出顺序存放各种锁：

列头 < ①Read Lock == ②Read Lock < ③Write Lock < ④Read Lock < 列尾

        读锁① 和 读锁② 中间用等号，表示这两个锁可以同时进行数据查询。其余的小于号表示 右边的锁等待小于号左边的锁。

        上边队列中一共有4个锁，分别为读锁①、读锁②、写锁③、读锁④。并且每个锁都分别对用有1个事务进行获取，即有4个事务。

事务1(读锁)

SELECT * FROM trans WHERE id=2 LOCK IN SHARE MODE;      

事务2(读锁)

SELECT * FROM trans WHERE id=2 LOCK IN SHARE MODE;      

事务3(写锁)

UPDATE trans SET xxx=10 WHERE id=2; -- 加上写锁（独占锁）
或
SELECT * FROM trans WHERE id=2 FOR UPDATE; -- 加上独占锁。      

事务4(读锁)

SELECT * FROM trans WHERE id=2 LOCK IN SHARE MODE;      

请求锁顺序

事务1获得①读锁。

事务2尝试获取读锁②，发现前面已经有①锁并且是一个读锁，这时候获取成功,并可以读取到共享锁正在读取的数据。

事务3尝试用获取写锁③，但发现前面已经有两个写锁，所以等待直至读锁释放掉。

事务4常使用获取 读锁④，发现前面的是写锁③，所以等待事务3释放写锁③,而写锁③正在等待前面两个 读锁①、读锁②，所以事务4间接在等待 最前面两个读锁。

表锁

概述

说明

表锁有两种锁：共享锁（也称为：读锁）、独占锁（也称为：写锁）。

相同点

项	共享锁（也称为：读锁）	独占锁（也称为：写锁）
加锁方式	自动加锁。 查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显式加锁：     共享锁：lock table tableName read;     独占锁：lock table tableName write;

注意事项

表锁生效

        用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁。

        详解：LOCK TABLES虽然可以给InnoDB加表级锁，但表锁不是由InnoDB管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

表锁释放

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;      

其他网址