mysql InnoDB事务锁

mysql InnoDB事务锁

Comments 0 Comment

一、什么是锁

  1. 保护数据的完整性和一致性
  2. 控制对共享资源进行并发访问

二、数据库中的锁

分为两大类

lock latch/mutex
对象 事务 线程
保护 数据库逻辑内容 内存数据结构
持续时间 事务的过程 临界资源争夺

三、事务锁粒度

  • 行锁（InnoDB, Oracle）
  • 页锁（SQL Server）一页可能是几行大小
  • 表锁（MyISAM，Memory）
  • 锁升级（SQL Server中会发生，由于页锁内容比较多，升级成表锁）

四、InnoDB中的锁模式与粒度

四种基本锁模式
共享锁（S）-读锁 行锁
排他锁（X）-写锁 行锁
意向共享锁（IS） 表级
意向排他锁（IX） 表级
  • 在对表中的记录施加读锁或写锁之前，首先会施加意向读锁和意向写锁。
  • 意向锁是告诉数据库，当前session是要添加什么锁，是行锁还是表锁。
  • 意向锁总是自动施加和释放的。
  • 意向锁和X/S锁的级别不同，除了会阻塞IX/IS锁外，一般不会阻塞其它操作，并且它的自动施加和释放的时间非常短

五、InnoDB锁模式互斥表

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

六、一般select语名不会加任何锁，也不会被任何事务锁阻塞。

  • select的隔离性由MVCC（读回滚段，多版本并发控制Multi-Version Concurrency Control）的方式实现。

七、

（1）S锁

手动施加：select * from tables lock in share mode;

自动施加：insert 前

（2）X锁

手动施加：select * from tables lock for update;

自动施加：update, delete, insert前

（3）事务中用select … lock in share mode 获得共享锁，主要用于确认数据依存关系，并确保没有人对数据进行更改。但是如果事务也要对该记录进行更新操作，则很有可能造成死锁。当死锁发生是InnoDB会回滚代价比较小的事务。

（4）事务的提交或回滚可以释放锁

（5）一般不用考虑IX和IS锁，所以：

X S
X Conflict Conflict
S Conflict Compatible

（6）在innoDB中由不同SQL语句施加的锁：

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

八、变量innodb_lock_wait_timeout用于设置当一条记录由于锁的原因被阻塞，要等多长时间。如果发生阻塞可以使用：
show processlist;
查看阻塞进程。
九、如果锁的争用较严重，innodb_row_lock_waits和innodb_row_lock_time_avg的值会比较高，可以使用如下命令查看：
show status like 'innodb_row_lock%'

十、InnoDB锁的实现

  1. InnoDB的行锁并不是像oracle一样真的对每行数据加锁，InnoDB的锁实际上是加在索引上的。所以如果索引有重复的话，那么重复的行会一起被锁定。如果表没有索引，那么锁会加到整个表上而不是行上。在实际应用中，要特别注意InnoDB行锁这一特性，不然的话可能会导至大量的锁冲突。
  2. 即使表中使用了索引，但是否使用索引来检索记录是由MySql通过判断不同的执行计划的代价来决定的。因为有时Mysql会认为使用全表检查效率更高，如condition列进行过类型转换，那么列上的索引就不会被使用，检查执行计划方法：
    explain select * from table_name where condition

十一、小心GAP LOCK

什么是GAP？在INNODB的索引数据结构中，能插入新值的地方就叫做GAP！

当我们通过条件请求共享（S）或排他（X）锁时，得到的结果果是一个范围内的结果集，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁。

如我们只锁定记录4：

1，4，4，4，4，4，6，6，6

这时会把1至6都锁定，这时想插入2，3，5都是不行的。间隙锁产生的方式很多，对不存在的记录进行加锁也会形成间隙锁。而mysql也是通过GAP LOCK在repeatable-read隔离级别消除幻读的。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks
gap lock是会锁住索引记录之间的“间隙”，或锁住第一条索引之前或最后一条索引之后。例如：<code>SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;</code>不能插入15到c1列，无论是否有存在的值在其中，因为10~20之前的“间隙”已经上锁。

gap可能存在于单独的索引值，多个索引值，甚至是空索引。

GAP LOCK是性能和并行化的折中方案，并用于某些事务隔离等级但不是所有的都使用。

GAP LOCK并不用于唯一索引查找唯一行的时候（这并不包含搜索条件是多列索引中某几列；在这种情况GAP LOCK还是会发生）。 例如，如果id是唯一索引SELECT * FROM child WHERE id = 100 for update;只锁住一行，向其它行插入是不会锁住的。 如果id不是索引，或者还有非唯一索引，这条语句会锁住它的“间隙”。

有一点值得注意，不同的事务之前没有GAP LOCK锁冲突。例如：事务A可以在事务B已经给GAP上了排它锁（X）的同时上一个共享锁（S）。 因为如果记录从索引中删除，但是不同事务记录上的gap lock必须融合到一起。

GAP LOCK在InnoDB中是“purely inhibitive”，这意味着它们只是停止其他事务向GAP中插入数据。 它们并不会阻止其它事务向一个相同的GAP上GAP LOCK。因此，一个 GAP X-LOCK和GAP S-LOCK具有同样效果。

GAP LOCK可以被显示的禁止，如果你改变了事务的隔离级别到READ COMMITTED或开启 innodb_locks_unsafe_for_binlog 系统变量（这个值现在不推荐使用）。在这些条件下，GAP LOCK是关闭的，不被用于查找和索引扫描，并且只被用于外键约束和重复键检查。（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 隔离级别或开启了innodb_locks_unsafe_for_binlog还有其它影响。MYSQL评估过WHERE条件后，对于不匹配的行，记录锁会被释放。对于UPDATE语句，InnoDB做了一个“半同步”读（semi-consistent read），在提交之前就如同返回了一个最终提交后的版本，以至于MYSQL能决定该行是否匹配到（另一事务的）UPDATE的WHERE条件（译者注：也就是在B事务中能读到A事务提交COMMIT后的结果，而不是只看到旧的快照。）。

十二、NEXT-KEY lock

next-key锁是结合了索引上的记录锁和索引记录之前的gap lock。

InnoDB执行“行级锁”，对匹配的索引记录设置共享（S）锁或排它锁（X）。因此，行级锁实际上是索引记录锁。next-key锁同样影响索引记录之前的gap。所以，一个next-key锁是一个索引记录锁加上 一个gap lock。如果一个会话在R记录上有一个共享或排它锁，另一个会话不能在R记录之前（索引的排序方式）的gap上插入新的索引记录。

假设一个索引包含10，11，12和20这几个值。对于这个索引可能存在的next-key lock涵盖如下区间（圆括号不包含，方括号是包含该值）：

(负无穷, 10]
(10, 11]
(11, 13]
(13, 20]
(20, 正无穷)

默认情况下，InnoDB操作在 REPEATABLE READ 事务隔离级别下。在这情况下，InnoDB使用next-key lock用于查找和索引扫描，从而消除了幻读行。

十三、插入意向锁

插入意向锁是gap lock的一种，由 INSERT 操作在插入行之前设置。这个锁信号用于如果多个事务要插入记录的位置不是在同一个GAP，那么无需等待其它事务。假设索引记录值是4和7。两个不同的事务意图分别插入5和6。两个事务都会锁住4和7之间的GAP，用于在被插入的行获得排它锁，但是因为插入的行没有冲突，所以不会互相阻塞对方。

下面的例子演示了在获得排它锁之前施加插入意向锁的方法。例子调用了两个客户端A和B。

A创建的表包含两个索引记录（90和102），然后开始一个事务，放置一个ID大于100的排它锁在索引记录上。排它锁包含了102之前的GAP LOCK：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

B开始一个事特用于插入一条记录到GAP。当事务等待获得排它锁的同时，事务使用了一个插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意向锁显示类似于SHOW ENGINE INNODB STATUS的输出：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC锁

AUTO-INC锁是一种特殊的表级锁，由事务插入到表中代有AUTO_INCREMENT的列时加入。举个最简单的例子，如果一个事务向表中插入值，任何其它事务必须等待访插入事件结束，以至于被插入的行要等待第一个事务收到连续的主键值才行。

innodb_autoinc_lock_mode选项控制着auto-increment锁使用的算法。它允许你选择如何对待可预知的自动增长序列和最大并行插入数量。

For more information, see Section 14.8.1.5, “AUTO_INCREMENT Handling in InnoDB”.

 

发表回复