锁

​ 锁是数据库系统区分与文件系统的一个关键特性。为了保证数据一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

lock与latch

​ 使用命令可以查询latch信息

SHOW ENGINE INNODB MUTEX;

​ 对于lock信息查看就很直观

1、SHOW ENGINE INNODB STATUS2、information_schema架构下的表INNODB_TRX  INNODB_LOCKS  INNODB_LOCK_WAITS

INNODB存储引擎中的锁

​ INNODB存储引擎实现了如下两种标准的行级锁：

• 共享锁S Lock：允许事务读一行数据
• 排他锁X Lock：允许事务删除或更新一行数据

​ 某事务获取了行R的X锁，那么另外的事务不能立即获取行R的X和S锁称之为锁不兼容。

​ 某事物获取了行R的S锁，那么另外的事务不能立即获取行R的X锁，但能获取行R的S锁，称为兼容。

​ 此外，INNODB存储引擎支持在不同颗粒度上进行加锁操作，INNODB存储引擎支持一种额外的锁方式，称之为意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细颗粒上进行枷锁。

​ 若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细颗粒度的对象进行上锁。例如对行记录R上X锁，就要对数据库A、表1、页上加意向锁IX，最后对记录R上X锁。若对R加X锁之前，已经有事务对表1进行了S表锁，那么就需要等待S锁释放后再能在表1上加意向锁IX。

​ 意向锁即为表级别的锁，设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。

• 意向共享锁IS Lock：事务想要获取一张表中某几行的共享锁
• 意向排他锁IX Lock：事务想获得一张表中某几行的排他锁

​ 通过下面查询当前锁请求：

SHOW ENGINE INNODB STATUS\G;

​ 还有三张在information_schema下的表可以查询锁情况

# 只显示当前运行的INNODB事务，并不能判断锁的一些情况SELECT * FROM information_schema.INNODB_TRX\G;

# 可以清晰的看到当前锁信息，但是lock_data并非是可信值# 事务情况小的时候可以看这个表，但是事务量大的情况下比较难判断select * from information_schema.INNODB_LOCKS\G;

# 很直观的反映当前事务的等待，知道谁堵塞了谁。SELECT * FROM infomation_schema.INNODB_LOCK_WAITS\G；

​ 最后提供一个常用的联合查询，进行锁信息查询。

SELECT r.trx_id waiting_trx_id,       r.trx_mysql_thread_id waiting_thread,       r.trx_query waiting_query,       b.trx_id blocking_trx_id,       b.trx_mysql_thread_id blocking_thread,       b.trx_query blocking_queryFROM information_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx bON b.trx_id = w.blocking_trx_idINNER JOIN information_schema.innodb_trx rON r.trx_id = w.requsting_trx_id\G;

一致性非读定锁

​ 指INNODB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此等待行上锁的释放。而是会去读取行的一个快照数据。

​ 快照数据是改行之前版本的数据，通过undo段来完成的，undo端是用来在事务中回滚数据，因此快照数据本身是没有额外的开销。

​ 这是INNODB存储引擎的默认设置下的默认读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离界别下，读取的方式不同。

​ 快照数据其实就是当前行之前的历史版本，每行记录可能有多个版本，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制。

​ READ COMMINTED和REPEATABLE READ（默认事务隔离级别）的事务隔离级别下，INNODB存储引擎使用非锁定的一致性读。

• READ COMMITED：总是读取被锁定行的最新一份快照数据，理论上来讲是违反了ACID中的I特性
• REPEATABLE READ：总是读取事务开始时的行数据版本。

一致性锁定读

​ 在事务隔离级别REPEATABLE READ模式下，SELECT使用一致性非锁定读。但是某些情况下，需要显示地对数据库读取操作进行加锁以保证数据逻辑的一致性。这要求数据库支持加锁语句。

# 对读取的行记录加一个X锁，其他事务不能对已锁定的行加任何锁。SELECT ... FOR UPDATE# 对读取的记录加上一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，会被堵塞。SELECT ... LOCK IN SHARE MODE

​ 此外，这种SELECT语句必须在一个事务中进行执行。务必加上

BEGIN, START TRANSACTION或者SET AUTOCOMMIT = 0

自增长与锁

​ 在INNODB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

​ 5.1.22版本之前，自增长实现机制是：AUTO-INC Locking。

​ 是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。虽然一定程度上提升了并发效率，但是还是有问题。

• 事务依旧还是要等待前一个插入的完成（虽然不用等待事务的完成）。
• 对于INSERT...SELECT的大数据量的插入会影响插入的性能，因为另一个事务的插入会被堵塞。

​ 5.1.22版本之后，提供了一种轻量级互斥量额自增长实现机制，大大提高了自增长插入的性能。提供参数innodb_autoinc_lock_mode来控制自增长的模式。

​ 先对自增长的插入进行分类：

插入类型	说明
insert-like	所有的插入语句
simple inserts	插入前就能确定插入行数的语句。不包括INSERT ... ON DUPLICATE KEY UPDATE这类SQL
bulk inserts	指在插入前不能确定得到插入行数的语句，如INSERT...SELECT，LOAD DATA
mixed-mode inserts	指插入中有一部分的值是自增长的，有一部分是确定的。如

​ 分析参数innodb_autoinc_lock_mode以及各个设置下自增的影响，总共有三个有效值可以设定，即0、1、2。

innodb_autoinc_lock_mode	说明
0	5.1.22版本之前自增长的实现方式
1	参数默认值。对于simple inserts，会用互斥量去对内存中的计数器进行累加操作。对于bulk inserts使用传统的AUTO-INC Locking方式。这种配置下，如果不考虑回滚操作的话，自增列键值增长还是连续的。并且在这种方式下，statement-based方式的replication还是能很好的工作。
2	对于所有INSERT-like自增长值的产生都是通过互斥量而不是AUTO-INC Locking方式。显然，这是性能最高的方式，但是因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。最终昂要的是，基于Statement-Base Replication会出现问题。因此，使用这个模式，任何时候都应该使用row-base replication。这样才能保证最大的并发性能以及replication主从数据的一致性

外键和锁

​ 对于外键值的插入或更新，首先要查询父表中的记录，即SELECT父表，但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT ... LOCK IN SHARE MODE 方式，即主动对父表加上一个S锁。如果这时父表已经加上了X锁，那么子表上的操作会被堵塞

锁的算法

行锁的三种算法

• Record Lock：单个行记录上的锁

  总是会去锁住索引记录，如果没有显示设置索引，会使用隐式的主键来进行锁定。

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身

• Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身。

​ Next-Key Lock是INNODB对于行查询的采用的锁定算法。例如一个索引有10，11，13和20这四个值，那么索引可能被Next-Key Locking的区间为：

​ 当插入新纪录12时，锁定的范围会变成

​ 然而，当查询的索引含有唯一属性时，INNODB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身。

​ 对于辅助索引，不仅加上的是Next-Key Lock，还会对辅助索引下一个键值加上gap lock。

解决Phantom Problem

​ Phantom Problem（幻象问题）：是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

​ 在默认事务隔离级别REPEATABLE READ下，INNODB存储引擎采用Next-Key Locking来避免。

​ 例如：

CREATE TABLE t (a INT PRIMARY KEY)BEGINSELECT * FROM TWHRER a > 2 FOR UPDATE;

​ 这个事务中。对于a>2的范围加上了X锁，因此任何对和这个返回的插入都是不被允许的，所以避免的幻读。

锁问题

脏读

​ 脏数据是指未提交的数据。

​ 脏读指的是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

​ 在事务隔离级别READ UNCOMMITTED下才会发生，但是实际生产并不会使用这个级别，但是可以将replication环境中的slave节点设置为这个级别，以提升性能。

不可重复读

​ 不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问了该同一数据集合，并做了一些DML操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。

​ 在MYSQL官方文档中，不可重复度被定义为Phantom Problem，但是在事务级别READ REPEATABLE级别下，采用Next-Key Lock算法，避免了不可重复读的现象。

丢失更新

​ 就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。

​ 在数据库层面，任何事务隔离界别都会通过加锁，来避免出现丢失更新的情况。一般出现在用户编程时发生。

• 事务T1查询一行的数据，放入本地内存，并显示给一个终端用户USER1
• 事务T2也查询一行的数据，放入本地内存，并显示给一个终端用户USER2
• USER1修改这行数据，更新数据库并提交
• USER2修改这行数据，更新数据库并提交

​ 解决方式，是将事务并行操作变成串行的操作。所以要对用户读取的记录上加一个排他X锁即可。

select cash into @cashform accountwhere user = pUser FOR UPDATE;

堵塞

​ 因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源，这就是堵塞。

​ 参数innodb_lock_wait_timeout用来控制等待的时间，默认50秒，是可以动态设置的，

​ 参数innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作。默认是OFF

死锁

死锁的概念

​ 死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待现象。

​ 解决死锁问题最简单方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。但是性能很差。

​ 解决死锁问题最简单方法是超时，即当两个事务互相等待时，当一个等待时间超过阈值时，其中一个事务进行回滚，另外一个事务就能继续进行。虽然简单，但是仅仅根据FIFO进行回滚，若超时的事务占据权重比较大，会浪费较多时间。

​ 所以除了超时机制外，还普遍采用wait-for graph等待图的方式来进行死锁检测。这要求数据库保存以下两种信息：

• 锁的信息链表
• 事务等待链表

​ 通过上述链表可以构造出一张图，如果存在贿赂，那就代表存在死锁，因此资源间互相发生等待。在图中，事务T1执向T2边的定义为：

• 事务T1等待事务T2所占用的资源
• 事务T1最终等待T2所占用的资源，也就是事务之间在等待相同的资源，而事务T1发生在事务T2的后面。

​ 然后就可以画出wait-for graph

​ 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时会判断是否存在贿赂，若存在死锁，选择回滚undo量最小的事务。

死锁概率

​ 事务发生死锁的概率与以下几点因素有关：

• 系统中事务的数量n，数量越多发生死锁的概率越大。
• 每个事务操作的数量r，每个事务操作的数量越多，发生死锁的概率越大
• 操作数据的集合R，越小则发生死锁的概率越大。

锁升级

​ 是指将当前锁的颗粒度降低。举例说，数据库可以把一个表的1000行锁升级为一个页锁，或者将页锁升级为表锁。

​ INNODB不存在所省级的问题，因为其不是根据每个记录来产生行锁的，想法，根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常是一致的。