mysql InnoDB事务锁

mysql InnoDB事务锁

一、什么是锁

  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”.

 

发表评论