一. 锁分类
lock 用来锁定数据库中的对象: 如表, 页, 行; 一般 lock 只在事务提交或回滚后释放. 锁分为共享锁(S)和排他锁(X). innoDB支持多粒度锁同时存在
1. Locking Read 示例
-
场景一 (for share):
- 假设想要在一个名叫 child 的表中插入新 row, 前提要确保在名叫 parent 的表中包含一个 parent row. 想要确保引用的完整性, 可以按照如下步骤组织代码:
- 首先, 不能使用 noblocking read 读取 parent row 的方式, 检查 parent row 是否存在. 因为其他的事务可能在你 select 和 insert 操作之间, 把 parent row 删除了. 而你这个事务却意识不到; 为了避免这个问题, 要使用
select ... for share锁定读读取 parent 表中 name = 'jones' 的行
SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;在
for share查询返回 'jones' 后, 就可以安全的在 child 表中 insert 一个 child 行, 最后提交事务. 任何想要在 parent 表的 'jones' 行上获取 X 锁的事务, 都要等待你这个事务完成. 换句话说: 其他事务要等到所有表到达一致性后侧能继续进行. -
场景二 (for update):
- 假设有一个计数表
CHILD_CODES, 每次从这个表获取 id 的最新值作为主键插入到 child 表中, 然后让CHILD_CODES表的计数值 +1; - 这种情况,
select ... for share就不适用了. 因为两个事务可能在同一时间读到相同的值, 从而导致 child 表的主键重复. 所以应该使用for update形式的锁定读, 最后在事务中 update 即可
SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1; - 假设有一个计数表
2. 让 Locking Read 并发 - NOWAIT 和 SKIP LOCKED
因为使用 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 锁定读时, 当被读取的 row 被其它事务加锁时, 就必须等待获得所的事务获取锁才能返回, 这种等待有时并不必要. 我们想让锁定读立刻返回, 或者仅仅是想看看查询的结果是否可用. 为了避免等待锁释放, 可以在锁定读上加上 NOWAIT 和 SKIP LOCKED 选项.
- NOWAIT
使用NOWAIT的锁定读不会等待获取锁, 会立刻返回. 如果被读的行被锁定, 则返回异常 - SKIP LOCKED
使用NOWAIT的锁定读不会等待获取锁, 会立刻返回. 返回的结果集中, 去除被锁定的行
注意: 这两个选项对于应用来说并不安全. 一个会返回异常, 另一个返回的结果集和数据库的状态并不一致
使用示例:
# 事务 1:
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+
# 事务 2:
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.
# 事务 3:
mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+
15.7.3 不同 sql 加什么锁
1. 什么操作会导致加锁
locking read, UPDATE, 或者 DELETE 操作, 一般会设置'记录锁' (record locks) 在 sql 语句 scan 到的每个索引记录上, 无论 scan 到的索引记录是否满足 where 条件. 因为 innodb 并不记得 where 条件是什么, 只是知道扫描到的索引范围. 所谓加锁, 一般是加 next-key lock, 这个锁会组织 insert 数据到记录之前. 但是
* 可以手动关闭 gapping lock, 从而让 next-key lock 也失效
* 其次, 事务隔离级别也会影响 sql 语句加什么锁
2. 索引对加锁的影响
如果查询动作中用到了二级索引, 且在二级索引上加了 x 锁, 则还会对其对应的聚簇索引加锁
如果查询动作没有找到合适的索引, 则会导致全表扫描, 且表中的每一行都会被锁定. 反过来也会组织所有其他用户插入到这张表. 因此, 建立合适的索引来减少扫描的行数是很重要的
3. 不同 sql 加的不同锁
-
SELECT ... FROM- 该语句属于非阻塞的 consistent read. 读的是数据库的快照, 且除非隔离级别为 SERIALIZABLE 否则不加锁.
- 对于 隔离级别, 对遇到的索引记录设置共享版 next-key lock (s版锁). 但是如果读取动作使用了唯一索引读取唯一行, 则只在结果索引上加记录锁
-
SELECT ... FOR UPDATE和SELECT ... FOR SHARE- 该语句属于 locking read, 会释放扫描中遇到的不符合条件的行锁
-
SELECT ... FOR UPDATE会阻止其他事务的SELECT ... FOR SHARE锁定读取. 非阻塞的Consistent reads会忽略记录上的锁
-
locking read,
update ... where和delete ... where操作, 根据查询条件是使用唯一索引还是执行范围查找来决定加不同结构的锁- 查询条件只有唯一索引: 只加 index lock 锁, 不在唯一结果前加 gap 锁
- 对于其他查询条件: innodb 锁 scan 到的索引范围. 对范围内的每个索引使用 gap locks 或 next-key locks 来组织其他事务插入到索引区间内
-
INSERT- 该操作会在被查入行上设置 x 锁(排它锁). 该排它锁是一个
index-record lock而不是通常的next-key lock, 就是说 insert 操作不会阻止其他事务在被查入行之前插入数据 - 在加上述
index-record lock之前, 先要加一个名叫insert intention gap lock的意向插入间隙锁. 这个锁表示, 即使不同事务要在同一个间隙内插入,只要插入位置不同, 2个事务就不用互相等待. 比如: 有2个索引记录值4和7, 2个事务想分别插入值5和6, 虽然他们的意向插入间隙锁都锁的(4,7)区间, 但因为获取的事不同的 x 锁 (上述index-record lock), 所以不会互相阻塞 -
insert操作会出现重复键错误. 当两个不同的事务插入同一个索引值时, 即使事务不提交, 后续等待插入的事务也会由于插入了相同的值而报重复键错误, 然后所有报错的事务都会进入队列等待获取一个该索引的 s 锁 (共享锁); 一旦原先的事务因删除该索引值或回滚而释放锁, 就会让队列中等待获取 s 锁的所有事务都获取 s 锁, 接下来这些事务尝试获取索引值上的 x 锁但都无法获取成功, 因为每个事务都享有索引上的 s 锁而无法再获取 x 锁, 从而导致死锁. - 示例:
CREATE TABLE t1（i INT，PRIMARY KEY（i））ENGINE = InnoDB; # 事务1: START TRANSACTION; DELETE FROM t1 WHERE i = 1; # 事务2: START TRANSACTION; INSERT INTO t1 VALUES(1); # 事务3: START TRANSACTION; INSERT INTO t1 VALUES(1); # 事务1: COMMIT;第一步: 事务1 获取了行上的 x 锁
第二步: 事务2 和 事务3 都因为重复键错误进入等待队列, 等待 VALUES(1) 这行的 s 锁.
第三步: 事务1 提交, 释放了 VALUES(1) 的 x 锁. 此时 事务2 和 事务3 获取了 VALUES(1) 的 s 锁, 接下来尝试获取 VALUES(1) 的 x 锁但都无法成功而进入思索状态, 因为对方都持有 VALUES(1) 的 s 锁 - 该操作会在被查入行上设置 x 锁(排它锁). 该排它锁是一个
-
INSERT ... ON DUPLICATE KEY
不同于上面的INSERT操作, 该操作在发生重复键错误时, 会在 row 上加 x 锁, 而不是 s 锁;- 如果是主键(pk)重复, 则加的是 x 版
index record lock(排它版索引记录锁) - 如果是唯一键(unique key)重复, 则加的是 x 版
next-key lock
- 如果是主键(pk)重复, 则加的是 x 版
REPLACE
因为REPLACE是根据唯一键来查找替换, 所以其执行类似于普通的INSERT操作 (先后加上insert intention gap 锁和index record 锁); 区别是如果发生重复键, 在REPLACE操作中表示找到要替换的行, 则在被替换的行上加排他的next-key 锁(x锁)-
INSERT INTO T SELECT ... FROM S WHERE ...- 该语句在要插入表 T 的每一行上设置 排它版
index record 锁 - 对于 select 语句部分, 不同隔离级别会加不同的锁
- 如果是
READ COMMITTED隔离级别, 对表 s 的查找当做非阻塞的consistent read, 所以不加锁 - 如果是其他隔离级别, 则在表 S 的查询行上加共享版的
next-key 锁
- 如果是
- 该语句在要插入表 T 的每一行上设置 排它版
CREATE TABLE ... SELECT ...语句
加锁同INSERT INTO T SELECT ... FROM S WHERE ...
`REPLACE INTO t SELECT ... FROM s WHERE ...或UPDATE t ... WHERE col IN (SELECT ... FROM s ...)
该语句在表 S 上加共享的next-key 锁自增列
innodb 会在自增列的末尾加一个排它锁, 当该条sql语句执行完毕就会释放排它锁, 不用等待事务完毕再释放
15.7.4 幽灵行 (Phantom Rows)
1. 什么是幽灵行
2. innoDB 使用 next-key locking 算法解决幻读
3. 为什么 next-key lock 可以做唯一性检查
# 分别测试, a 是索引, a不是索引, a是主键的情况, 体会 next-key lock , 全表扫描 lock, 和唯一键 lock index record
create table t(a int,key idx_a(a));
root@localhost : test 10:56:13>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
root@localhost : test 10:56:15>select * from t;
+------+
| a |
+------+
| 1 |
| 3 |
| 5 |
| 8 |
| 11 |
+------+
5 rows in set (0.00 sec)
section A:
root@localhost : test 10:56:27>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 10:56:29>select * from t where a = 8 for update;
+------+
| a |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
section B:
root@localhost : test 10:54:50>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 10:56:51>select * from t;
+------+
| a |
+------+
| 1 |
| 3 |
| 5 |
| 8 |
| 11 |
+------+
5 rows in set (0.00 sec)
root@localhost : test 10:56:54>insert into t values(2);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 10:57:01>insert into t values(4);
Query OK, 1 row affected (0.00 sec)
++++++++++
root@localhost : test 10:57:04>insert into t values(6);
root@localhost : test 10:57:11>insert into t values(7);
root@localhost : test 10:57:15>insert into t values(9);
root@localhost : test 10:57:33>insert into t values(10);
++++++++++
上面全被锁住，阻塞住了
root@localhost : test 10:57:33>insert into t values(8); 是否会阻塞?? (唯一性检查)
READ COMMITED: 一个事务内读到了其他事物提交的结果, 导致2次读结果不同, 为不可重复读
-- 普通字段版, 放弃对不符合where条件的行的锁
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
-- Session A
START TRANSACTION;
-- UPDATE t SET b = 5 WHERE b = 3;
SELECT * FROM t
-- Session B
START TRANSACTION;
UPDATE t SET b = 4 WHERE b = 2;
-- Session A
SELECT * FROM t -- 与上次结果不一致, 不可重复读
-- where条件包含索引字段, 则只有索引字段作为是否放弃 lock 的依据
CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;
-- Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
-- Session B
START TRANSACTION;
UPDATE t SET b = 4 WHERE b = 2 AND c = 4; -- 阻塞
https://www.cnblogs.com/rjzheng/p/9950951.html 什么隔离级别和什么查询条件配合 , 决定加什么锁
15.7.5 innoDB 处理死锁
死锁就是事务无法再记性下去. 因为每个事务都持有对方希望获得的锁, 只能互相等待
- 死锁示例
-- 表准备
CREATE TABLE t (i INT)
INSERT INTO t (i) VALUES(1);
-- 事务1
START TRANSACTION;
SELECT * FROM t WHERE i = 1 FOR SHARE; -- s锁 i = 1
-- 事务2
START TRANSACTION;
DELETE FROM t WHERE i = 1; -- 获取 x 锁, 但阻塞执行, 因为事务1 的 s锁不兼容
-- 事务1
DELETE FROM t WHERE i = 1; -- 此时发生死锁, 事务1无法获取x锁. x锁在事务1上
- 如何解决innoDB死锁?
- (1) 使用
SHOW ENGINE INNODB STATUS命令查看给出的死锁原因 - (2) 如果 innoDB 频繁发生死锁, 可以打开
innodb_print_all_deadlocks设置打印所有发生的死锁日志. 调试完毕再把该选项关闭 - (3) 如果死锁导致事务失败, 重启事务就好
- (4) 确保事务足够小, 足够短, 从而减少死锁发生的可能性. 尽早提交事务
- (5) 给表增加合适的索引, 让 query 扫描更少的索引记录从而加更少的锁 (比如RR隔离级别下的 next-key lock)
- (6) 如果允许 select 的结果集是旧版的快照数据, 就不要加
FOR UPDATE或FOR SHARE进行锁定读. 这种情形下使用READ COMMITTED隔离级别也是可以的, 让 select 每次拉取新快照 - (7) 如果上述方法都没用, 直接加表级锁, 防止多个事务同时更新表
SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES; - (8) 最后一个办法是增加一个只有一行的 semaphore 表, 让所有事务序列化的进行. 每次事务访问其他表前先更新这个表
- (1) 使用
