Optimizing Locking Operations

8.11.1 Internal Locking Methods
8.11.2 Table Locking Issues
8.11.3 Concurrent Inserts
8.11.4 Metadata Locking
8.11.5 External Locking

MySQL 使用 locking来管理表内容的争用：

Internal locking 发生在MySQL服务器内部，来管理多线程导致的表内容争用。这种锁是内部的，因为它完全是服务器产生的，而不是其他程序。参见 Section 8.11.1, “Internal Locking Methods”。
External locking 发生在服务器和其他程序锁住 MyISAM 表文件来协商该时间点哪个程序可以访问改表的时候。参见 Section 8.11.5, “External Locking”。

一、内部锁 Internal Locking Methods

行级锁 Row-Level Locking

MySQL 使用row-level locking 给 InnoDB 表来支持不同session的同时写，使得适用于多用户、高并发、以及OLTP应用。

要在并发写某个InnoDB 表的时候避免 deadlocks ，通过在事务开头对要修改的行提交 SELECT ... FOR UPDATE来获取必要的锁，即使在稍后事务会修改这些数据。如果事务修改或锁定多个表，那么应用的语句在每个事务内都要以同样的顺序执行。死锁会影响性能，而不是一个严重的错误，因为InnoDB自动的 detects 死锁状态，然后回滚其中一个受影响的事务。

行级锁的优势：

不同session访问不同行的时候，产生更少锁的冲突。
回滚更少的变更。
可以长时间锁某单行。

表级锁 Table-Level Locking

MySQL使用table-level locking 给 MyISAM、 MEMORY、和 MERGE 表，一次只允许一个连接更新表。这种锁使得存储引擎更适合于只读、读多写少、或者单用户应用。

这些存储引擎通过在开始查询时即获取所有需要的锁，然后以同样的顺序来锁住这些表来规避deadlocks 。相对妥协的是该策略降低了并发性能；其他会话修改数据必须等到当前修改数据的语句结束。

表级锁的优势：

相对小的内存需求 (行锁需要锁定的每行或者行组都分配内存)
在使用表的大范围内容的时候快，因为仅一个锁
在经常在大范围数据执行 GROUP BY 操作，或者必须频繁执行全表扫描的时候快

MySQL通过如下授权表写锁：

如果表上没锁，那么推一个写锁给表
或者，推锁的请求进写锁队列

MySQL通过如下授权读锁：

如果表上没锁，那么推一个读锁给表
或者，推锁的请求进读锁队列

表更新的优先级高于表的检索。因此，当一个锁释放了，先对写锁队列的请求可用，然后对读锁队列的请求可用。这就保证了即使该表有一个很大的SELECT，也不会导致更新等待。那么，如果该表有很多更新，SELECT 语句会等待直到没有更新为止。

关于更改读写优先级，参见Section 8.11.2, “Table Locking Issues”。

表锁

InnoDB 表使用行级锁，这样多连接和应用可以同时读取和写入到同一个表，而不需要等待或产生不一致的结果。对于InnoDB引擎，避免使用 LOCK TABLES statement，因为它不提供任何保护反而降低了并发。自动的行级锁使这些表适用于你最忙的数据库，同时简化了应用逻辑因为你不需要去锁和解锁表。因此，InnoDB 存储引擎成为了MySQL的默认引擎。

除了InnoDB，其他所有的存储引擎MySQL都使用表锁 (而不是page, row, or column 锁)。锁操作自身没什么开销。但是因为同时只能有一个连接可以写入表，为了这些引擎的最佳性能，它们主要用于查询多，插入、更新少的表。

二、外部锁 External Locking

External locking是使用文件系统锁定来管理多个进程对MyISAM 数据库表的争用。External locking 适用于单进程的情况，但是该进程MySQL server不能将其作为访问表的唯一进程。

三、元数据锁

MySQL 使用元数据锁来管理数据库对象的并发访问，确保数据一致性。元数据锁不仅可以作用于tables，还可以作用于 schemas、 stored programs (procedures, functions, triggers, and scheduled events)。

元数据锁确实会产生一些开销，随着查询列数量的增加而增加。多个查询试图访问同一对象的时候，元数据争用会增加。

元数据锁不是用于代替表定义缓存的，它的互斥锁、锁跟LOCK_open互斥锁是不一样的。如下内容讨论了元数据锁是如何工作的。

元数据锁(MDL)按锁住的对象来分类，可以分为global，commit，schema，table，function，procedure，trigger，event，这些对象发生锁等待时，我们在show processlist可以分别看到如下等待信息。

Waiting for global read lock 
Waiting for commit lock
Waiting for schema metadata lock
Waiting for table metadata lock
Waiting for stored function metadata lock
Waiting for stored procedure metadata lock
Waiting for trigger metadata lock
Waiting for event metadata lock

场景一：

通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。出现Waiting for table metadata lock

场景二：

通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。

场景三：

通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

状态 Waiting for table metadata lock

mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| Id  | User | Host      | db     | Command | Time | State                           | Info                                      |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| 267 | root | localhost | sbtest | Query   |    7 | Sending data                    | insert into sbtest2 select * from sbtest1 |
| 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table sbtest2 add test1 int         |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

状态 Waiting for global read lock

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------------------------+------------------+
| 1 | root | localhost:5202  | test | Query | 12 | altering table | alter table t1 add c3 bigint |
| 2 | root | localhost:14699 | test | Query | 3 | Waiting for global read lock | set global read_only=on |
| 3 | root | localhost:17085 | NULL | Query | 0 | init | show processlist |
+----+------+-----------------+------+---------+------+------------------------------+------------------+

状态 Waiting for commit lock

mysql> show processlist;
+----+------+-----------------+------+------------+------+-------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+------------+------+-------------------------+------------------+
| 1 | root | 127.0.0.1:5202 | test | Query | 7 | Waiting for commit lock | commit |
| 2 | root | 127.0.0.1:14699 | test | Sleep | 13 | | NULL |
| 3 | root | 127.0.0.1:17085 | NULL | Query | 0 | init | show processlist |
+----+------+-----------------+------+---------+------+-------------------------+------------------+

metadata lock.png

MDL锁的诊断
MySQL 5.7版本之前并没有提供一个方便的途径来查看MDL锁，github上有一名为mysql-plugin-mdl-info的项目，通过插件的方式来查看，非常有想法的实现。好在官方也意识到了这个问题，于是在MySQL 5.7中的performance_schea库下新增了一张表metadata_locks，用其来查看MDL锁那是相当的方便：

mysql> SELECT * FROM performance_schema.metadata_locks;
    
mysql> SELECT * FROM performance_schema.setup_instruments;

对于5.7之前版本，没有具体的方法查看全局读锁，表锁，MDL锁信息。可以通过 information_schema.processlist， performance_schema.events_statements_history， performance_schema.events_statements_current 等表大概推测阻塞情况

四、 InnoDB 锁

Shared and Exclusive Locks 共享锁/排他锁
Intention Locks 意向锁
Record Locks 行锁
Gap Locks 间隙锁
Next-Key Locks 临键锁
Insert Intention Locks 插入意向锁
AUTO-INC Locks 自增锁

五、 InnoDB 死锁

死锁是这样一个场景，不同的事务不能推进，因为彼此都占了对方需要的锁。因为这些事务都在等资源释放，而不释放自己占的锁。

死锁发生在事务在多个表产生行锁，但是以相反的顺序的时候 (比如通过 UPDATE or SELECT ... FOR UPDATE)。死锁还会发生在，这些statements锁住某范围的 index records and gaps的时候，每个事务都在请求锁但是没有而导致的超时问题。死锁的范例，参见 Section 14.7.5.1, “An InnoDB Deadlock Example”。

要降低死锁的几率，使用事务而不是 LOCK TABLES statements；使得insert or update 数据的事务足够的小，这样它们不会长时间打开；当不同的事务update多个表或者大范围行的时候，在每个事务都要使用同样的操作顺序 (比如 SELECT ... FOR UPDATE)；给 SELECT ... FOR UPDATE and UPDATE ... WHERE statements 操作的列增加索引。死锁的几率不受隔离级别的影响，因为隔离级别是变更的读操作，而死锁是因为写操作导致的。关于如何避免死锁和恢复死锁，参见 Section 14.7.5.3, “How to Minimize and Handle Deadlocks”.

如果死锁发生，InnoDB 会检测到，然后回滚其中一个事务 (the victim)。因此，即使你应用的逻辑是对的，如果该事务必须被重试，你还是必须要处理这个问题。查看 InnoDB用户事务最近的死锁，使用命令 SHOW ENGINE INNODB STATUS 。如果频繁的死锁导致了事务问题或者应用报错，配置innodb_print_all_deadlocks 设置为 enabled 来将所有的死锁信息都打印到mysqld error log。关于死锁如何自动检测和处理的更详细信息，参见Section 14.7.5.2, “Deadlock Detection and Rollback”。

六、死锁检测和回滚

InnoDB自动检测事务死锁并且回滚其中一个事务来打破死锁。 InnoDB 选择小一些的事务来进行回滚，判断事务大小的标准是 inserted, updated, or deleted的行数。

只有设置innodb_table_locks = 1 (the default) and autocommit = 0的时候，InnoDB 才会识别表级锁，MySQL才会感知行级锁。否则，当MySQL LOCK TABLES statement 设置了表锁，或其他不是InnoDB的存储引擎设置锁的时候，InnoDB 不能检测到死锁。可以通过设置 innodb_lock_wait_timeout 系统变量来解决这个问题。

当 InnoDB 执行一个事务的完全回滚时，所有该事务设置的锁会被释放。但是，如果一个SQL statement 回滚出错，那么该statement设置的一些锁还会保留。这是因为InnoDB以某格式存储行锁，这样它就无法知道哪个锁是哪个语句设置的。

如果一个 SELECT 在事务内调用了存储函数，然后该函数内的一个statement失败了，那么这个statement会回滚。此外，如果之后执行了 ROLLBACK ，整个事务都会回滚。

如果InnoDB 监控的 LATEST DETECTED DEADLOCK 部分输出包含了如下信息， “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION”，这表明当前等待清单内的事务数量已经达到了200上限。等待清单超过200的事务会被视为死锁，正在等待检查等待清单的事务会被回滚。如果等待清单内事务产生的锁的线程超过1,000,000 locks，同样的报错还会发生。

如何通过技术管理数据库来避免死锁，参见 Section 14.7.5, “Deadlocks in InnoDB”.

通过检查innodb_row_lock变量来检查innodb行锁争用情况

select * from information_schema.innodb_locks 了解锁等待

设置innodb monitors观察锁冲突情况
show enging innodb status

首先，从锁的粒度，我们可以分成两大类：
表锁
开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁
开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

不同的存储引擎支持的锁粒度是不一样的：InnoDB行锁和表锁都支持、MyISAM只支持表锁！InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB使用表锁也就是说，InnoDB的行锁是基于索引的！

MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类
MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock。
MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

MySQL 5.6 锁

MySQL 5.6 锁

Optimizing Locking Operations

一、内部锁 Internal Locking Methods

行级锁 Row-Level Locking

表级锁 Table-Level Locking

表锁

二、外部锁 External Locking

三、元数据锁

四、 InnoDB 锁

五、 InnoDB 死锁

六、死锁检测和回滚

MySQL 5.6 锁

Optimizing Locking Operations

一、 内部锁 Internal Locking Methods

行级锁 Row-Level Locking

表级锁 Table-Level Locking

表锁

二、 外部锁 External Locking

三、 元数据锁

四、 InnoDB 锁

五、 InnoDB 死锁

六、 死锁检测和回滚

一、内部锁 Internal Locking Methods

二、外部锁 External Locking

三、元数据锁

六、死锁检测和回滚