MySQL表级锁使用说明 - 主机乐

该锁会锁定整张表，它是MySQL中最基本的锁策略，并不依赖于存储引擎(不管你是MySQL的什么存储引擎，对于表锁的策略都是一样的)，并且表锁是开销最小的策略（因为粒度比较大)。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

1、表级别的S锁，X锁

InnoDB存储引擎

在对某个表执行SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。

一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。

InnoDB存储引擎下，手动添加表t的S锁或X锁：

MyISAM存储引擎下，手动添加表t的S锁或X锁：

再开启一个事务，查询不同记录，并给查询记录加上X锁：表级别的 IX共享排它锁加锁成功，因为两次事务加的IX是针对不同的记录的

结论:

InnoDB支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
意向锁之间互不排斥，但除了IS与S兼容外，意向锁会与共享锁/排他锁互斥。
lX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

3、自增锁（AUTO-INC锁）

自增锁是MySQL一种特殊的锁，如果表中存在自增字段，当向表中插入数据时，MySQL便会自动维护一个表级的自增锁。

在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。

一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性显然并不高，当我们向一个有AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的。

所以 innodb 引擎通过设置 innodb_autoinc_lock_mode 的值来提供不同的锁定机制，来显著提高sQL语句的可伸缩性和性能。

innodb_autoinc_lock_mode有三个取值：0,1,2
tradition（innodb_autoinc_lock_mode = 0) 模式：==传统==锁定模式

它提供了一个向后兼容的能力
在这一模式下，所有类型的insert语句都会在语句开始的时候得到一个表级的auto_inc锁，用于插入具有auto_inc列的表，在语句结束的时候才释放这把锁，注意，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。因为是表级锁，当在同一时间多个事务中执行 insert 的时候，对于auto_inc锁的争夺会限制并发能力。

consecutive（innodb_autoinc_lock_mode = 1) 模式：==连续==锁定模式

在MySQL8.0之前，==连续==锁定模式是默认的添加模式
这一模式在simple insert （要插入的行数已知）做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的 (它保证了基于语句复制的安全)
这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

interleaved（innodb_autoinc_lock_mode = 2) 模式：==交错==锁定模式

在MySQL8.0，==交错==锁定模式是默认的添加模式
由于这个模式下所有insert语句都不回使用表级auto_inc锁，并且可以同时执行多个语句，这是最快和最可扩展的锁定模式，所以这个模式下的性能是最好的；但是它也有一个问题，由于多个语句可以同时生成数字，为任何给定语句插入的行生成的值可能是不连续的。