MySQL锁机制

一、MySQL锁概述

MySQL数据库不同的存储引擎支持不同的锁机制：MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，默认情况采用行级锁；BDB存储引擎采用页面锁（page-level locking）。
MySQL三种锁的特性如下：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
• 页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

三种锁更有特点，表级锁更适合以查询为主，只有少量按索引条件更新数据的应用；行级锁更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。

二、MyISAM表锁

MyISAM存储引擎只支持表锁。可通过如下命令查询表锁争用情况：

mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 73    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

如果Table_locks_waited 值比较高，说明存在比较严重的表锁争用情况。

1、MySQL表级锁的锁模式

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
对于MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对于MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。简单来说，就是MyISAM读操作和读操作可以并行，读和写是串行的，写和写也是串行的。

2、如何加表锁

显式加表写锁语句：

mysql> lock table demo write;
Query OK, 0 rows affected (0.01 sec)

显式加表读锁语句：

mysql> lock table demo read;
Query OK, 0 rows affected (0.01 sec)

释放锁：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

事实上，MyISAM在执行查询语句（select）前，会自动给涉及到的表加读锁；在执行更新操作（update、delete、insert）前，会自动给涉及的表加写锁。
注意：

• 在显式地执行lock table语句后，只能访问显式加锁的表，不能访问为加锁的表；如果加的是读锁，那么只能执行读操作，不能执行更新操作。所以MyISAM不会发生死锁。
• 如果同一个表被取以不同别名多次使用，需要对所有别名分别加锁。lock table demo as a read , demo as b read;

3、并发插入

在一定条件下，MyISAM表也支持读和插入操作并发进行。
MyISAM存储引擎有一个系统变量 concurrent_insert，专门控制其并发行为，其值可为0、1、2。

• concurrent_insert=0，表示不予许并发插入。
• concurrent_insert=1（默认设置），如果MyISAM表中没有空洞（即表中间没有被删除的行）。MyISAM允许在一个进程读表的同时，另一个进程在行尾插入记录。
• concurrent_insert=2，表示无论表有没有空洞，都允许在表位并行插入记录。

4、MyISAM的锁调度

MyISAM的锁调度机制：当一个进程请求MyISAM表的读锁，同时另一个进程请求该表的写锁时，写进程先获得锁。
这也是MyISAM表不适合大量更新操作的原因，大量更新操作会使进程很难获得读锁，从而长时间阻塞。
这种情况可以通过一些设置来改变：

• 通过执行命令set low_priority_updates=1，使该连接发出的更新请求优先级降低。
• 通过指定insert、update、delete语句的low_priority属性，降低该语句的优先级。
• 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一些获得锁的机会。

三、InnoDB锁问题

InnoDB与MyISAM锁问题最大的不同，一是InnoDB支持事务，二是InnoDB采用行级锁。

1、获取InnoDB行锁争用情况

（1）通过InnoDB_row_lock状态变量来分析系统上行锁的争夺情况：

mysql> show status like "innodb_row_lock%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

如果Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值比较高说明行锁争用严重，可查询information_schema数据库相关表来查看锁情况
（2）通过查询information_schema数据库相关表来了解锁等待情况

 use information_schema;

 select * from innodb_locks \G;

（3）通过设置InnoDB Monitors观察锁冲突情况

mysql> create table innodb_monitor(a int) engine=innodb;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-10-29 09:38:02 16cc INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 647 srv_idle
srv_master_thread log flush and writes: 648
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
Mutex spin waits 1, rounds 1, OS waits 0
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 1, rounds 30, OS waits 1
Spin rounds per wait: 1.00 mutex, 30.00 RW-shared, 30.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 242440
Purge done for trx's n:o < 240916 undo n:o < 0 state: running but idle
History list length 1013
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x16cc, query id 14 localhost ::1 root init
show engine innodb status
...

mysql> drop table innodb_monitor;
Query OK, 0 rows affected (0.01 sec)

设置监视器后，在show engine innodb status的显示内容中，会有详细的当前等待的信息，包括表名、锁类型、锁记录情况等。打开监视器默认每15秒向日志记录监控的内容，长时间不关闭会使日志文件非常巨大。因此记得删除监控表以关闭监视器。

2、InnoDB的行锁模式及加锁方法

InnoDB实现了两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许标所和行锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加行共享锁之前，必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加行排他锁之前，必须先取得该表的IX锁。

InnoDB行锁模式兼容性：

当前锁模式 \ 请求锁模式	X	IX	S	IS
X	×	×	×	×
IX	×	√	×	√
S	×	×	√	√
IS	×	√	√	√

意向锁是InnoDB自动加的，用户不需要干预。对于insert、update、delete语句，InnoDB会自动给涉及的数据集加排它锁（X），对于select语句，不自动加锁。
显式加锁语句：
共享锁（S）：select * from tb where ... lock in share mode;
排他锁（X）：select * from tb where ... for update;
共享锁场景示例：

排它锁场景：

3、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的。这意味着如果不通过索引检索数据，InnoDB将对表中所有记录加锁，实际效果跟表锁一样。
根据这一特性，为了避免大量的锁冲突，使用行锁时应注意：

• 在不通过索引条件查询时，InnoDB会锁定表中的所有记录
• MySQL的行锁是针对索引加的锁，不是针对记录，所以即使是访问不同行的记录，如果是使用相同的索引键，会出现锁冲突。
• 当表中有多个索引时，不同事务可以使用不同的索引来锁定不同的行，以避免锁冲突。
• 即使条件中使用了索引字段，但有些情况下MySQL执行计划不使用索引（具体有哪些情况参考我的另一篇关于索引的文章），这种情况下InnoDB也会对所有记录加锁。

4、关于死锁

MyISAM表锁不会发生死锁，InnoDB可能发生死锁。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退。但在涉及外部锁或涉及表锁的情况下，InnoDB不能完全检测到死锁。这时可通过设置所等待超时参数innodb_lock_wait_timeout来解决。
避免死锁的常用方法：

• 在应用中，如果不同程序会并发存取多个表，应尽量约定以相同的顺序来访问多个表，这样可以大大降低发生死锁的可能。
• 在程序以批量的方式处理数据的时候，如果实现对数据排序，保证每个线程按固定的顺序处理记录，可以大大降低发生死锁的可能。
• 在事务中，如果要更新记录，应申请排他锁，而不应该先申请共享锁，到需要更新记录的时候又申请排它锁。
• 在repeatable-read隔离级别下，如果两个线程相同条件的记录用select … for update语句加排他锁，在没有符合该条件记录的情况下，两个线程都会加锁成功，此时如果两个线程都试图插入新纪录，机会出现死锁。这种情况解决办法是将隔离级别改成read commited。

最后，通过以上的设计和优化，可以大大减少死锁的发生，但死锁还是有可能发生，如果程序出现死锁，可以用show InnoDB status命令来确定最后一个死锁产生的原因。