MySQL--锁问题

参考:https://my.oschina.net/huangyong/blog/160012

1) MySQL 概述

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

MySQL 这 3 种锁的特性可大致归纳如下。

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

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

页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同的数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

 

2) MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是 MySQL 开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL 才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的 BDB 存储引擎和支持行锁的 InnoDB 存储引擎。但是 MyISAM 的表锁依然是使用最广泛的锁类型。
2.1> 查询表级锁争用情况

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺：

SHOW STATUS LIKE 'table%';

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

2.2> MySQL 表级锁的锁模式

MySQL 的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

可见，对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。

2.3> 如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显示加锁。

给 MyISAM 表显式加锁，一般是为了在一定程度模拟事务操作，实现对某一个时间点多个表的一致性读取。

在 LOCAL TABLES 时加 local 选项，作用是满足 MyISAM 表并发插入条件的情况下，允许其他用户在表尾并发插入记录。

在用 LOCK TABLES 给表显示加表锁时，必须同时取得所有涉及表的锁，并且 MySQL 不支持锁升级，也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不读执行更新操作。在自动加锁的情况下也是如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁的原因。

当使用 LOCK TABLES 时，不仅需要一次锁定用到的所有表，而且，同一个表在 SQL 语句中出现多少次，就要通过与 SQL 语句中相同的别名锁定多少次，否则也会出错。

 

 2.4> 并发插入

在一定条件下，MyISAM 表也支持查询和插入操作的并发进行。

MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行，其值分别为0、1或2。（试了半天感觉有毛病，local 时一直可以在表末尾插入）

当 concurrent_insert 设置为 0 时，不允许并发插入。

当 concurrent_insert 设置为 1 时，如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是 MySQL 的默认设置。

当 concurrent_insert 设置为 2 时，无论 MyISAM 表中有没有空洞，都允许在表尾并发插入记录。

可以利用 MyISAM 存储引擎的并发插入特性来解决应用中对同一表查询和插入的锁争用。例如，将 concurrent_insert 系统变量设为 2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。

 

2.5> MyISAM 的锁调度

MyISAM 存储引擎的读锁和写锁是互斥的，读写操作是串行的。当一个进程请求某个 MyISAM 表读读锁，同时另一个进程也请求同一个表的写锁，MySQL  会让写进程获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。这是因为 MySQL 认为写请求一般比读请求要重要，这也正是 MyISAM 表不适合有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

可以通过一些设置来调节 MyISAM 的调度行为。

通过指定启动参数 low-priority-updates，使 MyISAM 引擎默认给予读请求以有限的权利。

通过执行命令 SET LOW_PRIORITY_UPDATES = 1，使该连接发出的更新请求优先级降低。

通过制定 INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性，降低该语句的优先级。

虽然上面 3 种方法都是要么更新优先，要么查询优先的方法，但还是可以用来解决查询相对重要的应用（如用户登录系统）中读锁等待严重的问题。

另外，MySQL 也提供了一种折中的办法来调节读写冲突，即给系统参数 max_write_lock_count 设置一个合适的值，当一个表的读锁达到这个值后，MySQL 就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

一些需要长时间运行的查询操作，也会使写进程饿死。因此，应用中应尽量避免出现长时间运行的查询操作。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行。

 

3) InnoDB 锁问题

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

3.1> 背景知识

3.1.1# 事务及其 ACID 属性

事务是由一组 SQL 语句组成的逻辑处理单元，事务具有以下 4 个属性，通常简称为事务的 ACID 属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。

隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务不受外部并发操作影响的独立环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

 

3.1.2# 并发事务处理带来的问题

相对于串行处理来说，并发事务的处理能力大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值或更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题--最后的更新覆盖了由其他事务所做的更新。

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这是，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些脏数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做脏读。

不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其独处的数据已经发生了改变或某些记录已经被删除了，这种现象叫做不可重复读。

幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足查询条件的新数据，这种现象就称为幻读。

InnoDB 用 MVCC 解决了在 REPEATABLE_READ 下的幻读问题。

参考：https://www.oschina.net/question/2265029_2191685

3.1.3# 事务隔离级别

脏读、不可重复读和幻读，都是数据库读一致性问题，必须由数据库提供和一定的事务隔离机制来结局。数据库实现事务隔离的方式，基本上可分为以下两种：

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrent Control，简称 MVCC 或 MCC），也经常称为多版本数据库。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行，这显然与并发是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的。比如许多应用对不可重复读和幻读并不敏感，可能更关心数据并发访问能力。

 各具体数据库并不一定完全实现了上述 4 个隔离级别。Oracle 只提供 READ COMMITTED 和 SERIALIZABLE 两个标准隔离级别，另外还提供了自己定义的 READ ONLY 隔离级别；SQL Server 除支持上述定义的 4 个隔离级别外，还支持一个叫做快照的隔离级别，但严格来说它是一个用 MVCC 实现的 SERIALIZABLE 隔离级别；MySQL 支持全部 4 个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级别下是采用 MVCC 一致性读，但某些情况下又不是。

3.2#获取 InnoDB 行锁争用情况