MySQL之行锁与表锁

非Innodb存储引擎中的锁

MyISAM、Memory、Merge这些存储引擎，只支持表级别锁，并且这些引擎并不支持事务，所以一般对这类引擎加锁时，都是会话粒度的锁，

例如会话1对A表执行select操作，即为该表加上S锁，会话2想更新A表的某条数据，则需要获取该表的X锁，此时必须要等待会话1的S锁释放才能获取到，

这类引擎的写操作只能串行处理，在并发情况下，性能比较差，它们更适合只读处理或者大部分都是读操作或者单用户写操作。

MyISAM的并发插入(concurrent insert)

MyISAM也并不完全是读写互斥，如果一个MyISAM表空间中没有空闲块，那么新的数据就会被插入到文件的末尾，在这种情况下，select和insert并不会发生冲突，此时读写可以并发执行。

通过系统变量 concurrent_insert 配置MyISAM的并发插入行为，通过 show variables like '%concurrent_insert%' 查看当前系统中的值，该变量有3个可选值：

1. never(或0)：不启用并发插入功能；
2. auto(或1)：自动模式，当数据文件中间没有空闲块则启用，否则自动取消；
3. always(或2)：即便表数据文件中间有空闲块也始终启用并发插入。当有空闲块时，表上有其它会话的读操作，这时新的数据行将被插入到文件的末尾；如果表上没有其它操作，那么insert操作就是一个正常的操作：获得一个写锁，并优先将数据行插入空闲块中。

缺省情况下，写操作的优先级要高于读操作的优先级，即便是先发送的读请求，后发送的写请求，此时也会优先处理写请求，然后再处理读请求，

这就造成一个问题：一旦用户发出若干个写请求，就会堵塞所有的读请求，直到写请求全都处理完，才有机会处理读请求,

此时可以考虑设置max_write_lock_count值为1，或者固定的次数，设置后，当系统处理固定次数的写操作后，就会暂停写操作，给读操作执行的机会，

更直接点的方式，可以降低写操作的优先级，给读操作更高的优先级。

Innodb存储引擎中的锁

Innodb既支持表级锁，又支持行级锁，表级锁粒度粗，占用资源少，但是性能差，行级锁粒度细，可以实现精准的并发控制提升性能，但是占用资源多。

1. Innodb的表级锁

1.1 表级别S锁和X锁

1. 在执行insert、select、delete、update语句时，并不会对该表添加表级S锁或X锁。

2. 执行alter table、drop table等DDL语句时，如果其他事务对该表进行上述的CURD操作，将会发生阻塞，反过来也是一样，这种互斥是通过server层的元数据锁(Metadata Lock，MDL)实现，并没有用到Innodb的表级别S锁和X锁，

   DDL语句在执行时会隐式提交当前会话中的事务，因为DDL语句需要执行若干个特殊事务，执行这些特殊事务前，需要将当前会话中的事务提交掉。

Innodb的表级锁一般不会用到，只会在特殊情况下，例如系统崩溃恢复时使用，在autocommit = 0、innodbc_table_locks = 1 的情况下，可通过下面的语句手动获取表级别的S锁和X锁：

1. lock tables t read：对表t加S锁
2. lock tables t write：对表t加X锁

一般情况下不要手动获取表S锁和X锁，除了降低并发能力，不会有其他的好处。

1.2 表级别的IS锁和IX锁

在事务对某些记录加S锁或者X锁前，需要对表加上对应的IS锁或IX锁，IS锁和IX的作用是在加表级锁时，判断当前表中是否已经有被加锁的记录，从而避免遍历记录判断有没有锁。

1.3 表级别的AUTO-INC锁

在创建表的过程中，可以指定某一列的AUTO_INCREMENT属性，之后在插入记录时，可不指定该列的值，系统将自动为其赋予递增的值。

系统对AUTO_INCREMENT列的赋值方式有两种：

1. 采用AUTO-INC锁：执行插入语句时，加一个表级别的AUTO-INC锁，然后为每条待插入的记录分配递增的值，语句结束后释放锁，这种情况insert事务处于串行，可保证每个语句的递增值都是连续的，

   在语句插入时，并不清楚插入具体多少条数据，一般会用到AUTO-INC锁为AUTO_INCREMENT列生成值，例如 insert ... select语句、replace ... select 语句、load data 语句。

2. 采用轻量级锁：轻量级锁即为一条记录生成AUTO_INCREMENT列值时，获取该锁，然后生成完毕后释放该锁，不用等到插入语句执行完毕后释放锁， 在插入语句时确定要插入的记录条数，可采用轻量级锁为AUTO_INCREMENT列生成值，这种方式可避免锁定表，提升插入性能。

Innodb提供了一个 innodb_autoinc_lock_mode 的系统表里，可控制生成AUTO_INCREMENT列值时，使用哪种锁，取值如下：

1. 0: 始终使用AUTO-INC锁
2. 1: 两种情况混合使用，记录不确定时用AUTO-INC锁，确定时使用轻量级锁
3. 2: 始终使用轻量级锁

轻量级锁会造成多个事务的插入语句AUTO_INCREMENT列值是交叉的，在基于Statement的主从复制的场景下会造成安全问题。

2. Innodb的行级锁

行级锁即在相应的记录上加锁，行级锁分为多种类型，不同类型的行级锁起到的作用也不同。

2.1 Record Lock

Record Lock也称行锁，官方称之为 LOCK_REC_NOT_GAP ，仅仅锁一条记录。

行锁也有S锁和X锁之分，一条记录的S锁如果被获取，那么其他事务依然可以继续获取该记录的S锁，但是不可以获取X锁，

反之，如果一个事务获取到了一条记录的X锁，那么其他事务不可以继续获取该记录的S锁和X锁。

2.2 Gap Lock

Innodb在repeatable read隔离级别下可以很大程度上解决幻读问题，解决方式有两种：

1. 使用MVCC
2. 加锁

这里讨论加锁的情况，在加锁情况下，一些记录并不存在，我们无法为这些记录加上锁，例如我们A事务想为主键3-8之间的记录加锁，但此时db中只有3和8两条记录，

我们如果对4、5、6、7几个不存在的记录加锁，让其他事务此时无法进行这几个主键的插入呢？

Innodb提供了一个名为 Gap Lock的锁，官方称为LOCK_GAP，例如我们为ID 8加上一个Gap Lock，则其他事务不可以在8之前的空隙中插入记录，即3-8之间不允许插入记录，

例如事务B想插入一个ID为4的记录，先定位到4的下一条记录的位置，此时定位到8，发现8上面有一个Gap Lock，那么事务B就会阻塞至8的Gap Lock释放之后，才可以将新记录插入进去。

Gap Lock的存在是为了防止插入幻影记录，一般还有共享Gap锁、独占Gap锁的说法，不过都是为了防止幻影记录的发生，如果一个事务对某条记录加上了Gap锁，并不会影响其他事务对该记录加Gap锁。

Gap Lock不允许其他事务向该记录前面的空隙插入数据，但是对于最后一条记录的间隙如何去做？例如表中最大的记录是20，

A事务获取20 - 25范围的锁，但是此时表中没有25这条记录，该向谁加这个Gap锁？MySQL数据页中有两条伪记录：

1. Infimum：表示该页面中最小的记录
2. Supremum：表示该页面中最大的记录

此时表中没有25这条记录，将会在 Supremum 上加Gap锁，这样就可以阻止其他事务插入幻影记录了。

2.3 Next-Key Lock

Next-Key Lock官方称为LOCK_ORDINARY，该锁是Gap锁的升级版，例如我们想锁住8这条记录，又不想让其他事务在8这条记录前面的空隙中插入记录，即可用到Next-Key Lock。

2.4 Insert Intention Lock

Insert Intention Lock又称插入意向锁，官方称为 LOCK_INSERT_INTENTION。

当一个事务要插入一条记录时，不要判断记录所插入的位置是否被别的事务增加了gap锁，如果有的话，需要进行等待，直到该gap锁被其事务释放，

在等待过程中需要在内存中生成一个锁结构，表明想在某个间隙中插入新记录，但是目前处于等待状态，锁结构会被挂在对应的记录上，这个锁结构就是插入意向锁，

多个事务可同时获取某记录的插入意向锁，当事务释放该记录的gap锁时，将通过该记录上的意向锁结构，唤醒对应的事务，此时对应的事务即可获取到gap锁记录上的插入意向锁，执行插入操作。

插入意向锁并不会影响其他事务获取对应记录上的任意锁。

如下图，t1拿到了3-8的gap锁，t2、t3想在3-8之间插入记录，但是由于有gap锁，此时在8上面增加一个插入意向锁，

当t1释放时，会唤醒t2、t3，此时2、3不会相互影响，而是并行插入。

2.5 隐式锁

在内存中生成锁结构会消耗一定的资源，Innodb出于节约资源的前提，在一般情况下，insert语句并不生成插入意向锁这样的锁结构，而是使用隐式锁即不加锁。

隐式锁会导致两个问题：

1. B事务立即使用 select ... lock in share mode获取该记录的S锁，或者使用select ... for update获取该记录的X锁

   如果允许这种情况发生，将发生脏读现象，因为之前的事务还没提交。

2. B事务立即update该记录，即获取X锁

   如果允许这种情况发生，将发生脏写现象，因为之前的事务还没提交。

解决办法：

获取S锁和X锁分为通过聚簇索引和二级索引获取，因此分为两种情况解决：

1. 对于聚簇索引，该记录有一个trx_id隐藏列，对应最后修改该记录的事务id，当B记录想获取该记录的S锁或X锁时，判断trx_id中的事务id是否是当前活跃的事务，

   如果不是，就正常获取，如果是则帮助该活跃事务创建一个X锁的锁结构，该锁结构的is_waiting为false，然后为自己也创建一个锁结构，is_waiting为true，然后自己陷入等待状态。

2. 对于二级索引，本身没有trx_id隐藏列，但是二级索引页面的Page Header中有一个 page_max_trx_id 表示对当前页面最后一次改动的事务id，

   如果该值小于当前事务id，则说明做过改动的事务已提交，可以正常获取，否则需要回表，然后执行1的步骤。

Innodb通过事务id，正常insert记录时，不生成锁结构，如果有其他事务想获取该记录的锁，再为相关的事务创建响应的锁结构(延迟创建锁)，形成了一个隐式锁，起到了既可以不加锁节省资源，又可以保护记录不发生脏读脏写。