MySQL表级锁使用说明_数据库_开发者_运维开发者技术经验分享

表级锁

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

1、表级别的S锁，X锁

InnoDB存储引擎

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

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

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

lock tables t read  -- S锁
lock tables t write  -- X锁

不过尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。

MyISAM存储引擎

MyISAM 的表级锁有2种模式，分别为：表共享读锁（S锁）和表独占写锁（X锁）。

表共享读锁（S锁）：当开启事务A 获取表共享读锁，则其他新开启事务只能读取数据，不能对操作的同张表进行更新或者插入操作，删除操作，

表独占写锁（X锁）：当开启事务A 获取独占写锁，则其他新开启的事物读取，新增，修改，删除等操作会处于阻塞状态，只到事务A 主动释放锁。

MySQL表级锁使用说明

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

lock tables t read  -- S锁
lock tables t write  -- X锁

可通过 show status like 'tables%'; 命令来查看 mysql 内部表级锁定的情况：

2、意向锁

意向锁概述

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

==意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。==

意向锁是一种不与行级锁冲突的表级锁，这一点非常重要。

意向锁分为两种：

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

select column from table ... lock in share mode; --

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

select column from table ... for mode; --

申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

意向锁解决的问题

事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。

如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。于是就有了意向锁。

事务B只需检查表上的意向锁，发现表上有意向共享锁IS，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

在数据表的场景中，如果我们给某一行数据加上了排AKxcGDcT它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁。
如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁。

意向锁的并发性

开启一个事务，并给查询记录加上X锁：此时针对查询的记录还加上了一个表级别的共享排它锁(IX)

MySQL表级锁使用说明

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

MySQL表级锁使用说明

结论:

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_lohttp://www.devze.comck_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锁，并且可以同时执行多个语句，这是最快和最可扩展的锁定模式，所以这个模式下的性能是最好的；但是它也有一个问题，由于多个语句可以同时生成数字，为任何给定语句插入的行生成的值可能是不连续的。