【MySQL】MySQL中的表锁

锁是计算机协调多个线程并发访问某一资源的机制。

在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

MySQL的锁机制比较简单，表锁由MySQL Server实现，行锁则是存储引擎各自实现，不同的引擎实现的方式不同。在MySQL的常用引擎中InnoDB支持行锁，而MyISAM则只能使用MySQL Server提供的表锁。

几种常见锁的比较：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

MyISAM的表锁

MySQL的表级锁有两种模式：

• 表共享读锁（Table Read Lock）

• 表独占写锁（Table Write Lock）

给表加共享读锁语法：lock table 表名 read

给表加独占写锁语法：lock table 表名 write

演示表结果：

mysql> show create table testmyisam \G;
*************************** 1. row ***************************
       Table: testmyisam
Create Table: CREATE TABLE `testmyisam` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

例1：演示表共享读锁。

T1	T2
lock table testmyisam read;
	select * from testmyisam; – 成功
	select * from testmyisam s; – 成功
select * from testmyisam;
select * from testmyisam s; ERROR 1100 (HY000): Table ‘s’ was not locked with LOCK TABLES
select * from t_emp; ERROR 1100 (HY000): Table ‘t_emp’ was not locked with LOCK TABLES
insert into testmyisam value(2); ERROR 1099 (HY000): Table ‘testmyisam’ was locked with a READ lock and can’t be updated
	insert into testmyisam value(2); – 阻塞
unlock tables;

例2：演示表独占写锁。

T1	T2
lock table testmyisam write;
	select * from testmyisam; --阻塞
select * from testmyisam;
select * from testmyisam s; ERROR 1100 (HY000): Table ‘s’ was not locked with LOCK TABLES
select * from t_emp; ERROR 1100 (HY000): Table ‘t_emp’ was not locked with LOCK TABLES
insert into testmyisam value(4); – 成功
unlock tables;

总结：

• 对MyISAM表加了共享读锁，对于其他session，不会阻塞对同一个表的读请求，阻塞对同一个表的写请求；对于当前session，对同一个表的写请求会直接报错。
• 一个session中只要使用了lock table加了表锁，不管是共享读锁还是独占写锁，在当前session中，对其他表或用表别名访问同一个表会直接报错。
• 对MyISAM表加了独占写锁，对于其他session，对同一个表的所有请求都会阻塞；对于当前session，可以对同一个表进行CRUD。

因为使用MyISAM、MEMORY、MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎实际上最好用在只读，或者大部分都是读操作，或者单用户的情景下。另外，在MyISAM存储引擎中有一个称之为Concurrent Inserts的特性，支持在对MyISAM表读取时同时插入记录，这样可以提升一些插入速度。

InnoDB的表锁

表级别的S锁、X锁

和MyISAM的表锁差别不大。注意开启一个新事务的时候会释放表锁。

元数据锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。另外，在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server 层使用一种称之为元数据锁（英文名：Metadata Locks，简称MDL）来实现的。

表级别的IS锁、IX锁

当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前，那就需要先在表级别加一个IS锁，当我们在对使用InnoDB 存储引擎的表的某些记录加X锁之前，那就需要先在表级别加一个IX锁。

IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。我们并不能手动添加意向锁，只能由InnoDB存储引擎自行添加。

表级别的AUTO-INC锁

在使用MySQL 过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值，系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个：

1. 采用AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。如果我们的插入语句在执行前不可以确定具体要插入多少条记录（无法预计即将插入记录的数量），比方说使用INSERT…SELECT、REPLACE…SELECT或者LOAD DATA这种插入语句，一般是使用AUTO-INC锁为AUTO_INCREMENT修饰的列生成对应的值。
2. 采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。如果我们的插入语句在执行前就可以确定具体要插入多少条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。

InnoDB提供了一个称之为innodb_autoinc_lock_mode的系统变量来控制到底使用上述两种方式中的哪种来为AUTO_INCREMENT修饰的列进行赋值：

mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.03 sec)

MySQL5.7.X中缺省为1。innodb_autoinc_lock_mode的取值说明：

• 值为0时：一律采用AUTO-INC锁
• 值为2时：一律采用轻量级锁
• 值为1时：两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用AUTO-INC锁）。

不过当innodb_autoinc_lock_mode值为2时，可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。