本文更新于2019-09-22，使用MySQL 5.7，操作系统为Deepin 15.4。

锁

锁概述

MyISAM和MEMORY存储引擎使用表级锁。BDB存储引擎进使用页级锁，但也支持表级锁。InnoDB存储引擎默认使用行级锁，也支持表级锁。

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

默认情况下，表级锁和行级锁都是自动获取的。但在有些情况下，用户需要明确进行锁定。

MyISAM表级锁

表级锁有两种模式：

• 表共享读锁：允许并发读，但会阻塞并发写。
• 表独占写锁：阻塞并发读和并发写。

加锁，如果表已被其他线程锁定，则当前线程会等待直至获得锁：

LOCK TABLE|TABLES
tablename [AS alias] {READ [LOCAL]}|{[LOW_PRIORITY] WRITE}
[, ...]

代码100分

加锁时指定LOCAL，则允许在满足MyISAM表并发插入条件（使用变量concurrent_insert控制）的情况下，其他用户在表尾并发插入记录。加锁时，需一次锁定所有用到的表，且同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次（使用AS）。加锁后，只能访问加锁的表，且不支持锁升级（即如果是读锁，那么只能执行读操作，不能执行写操作）。

MyISAM在执行读操作（SELECT）前，会自动给涉及的所有表加读锁，在执行写操作（UPDATE、DELETE、INSERT）前，会自动给涉及的所有表加写锁。

即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁之前。可以使用max_write_lock_count给予读请求获得锁的机会，或使用以下方法改变请求优先级：

• 通过指定启动参数low-priority-updates，默认给予写请求比读请求更低的优先级。
• 通过执行SET low_priority_updates=1，给予该连接写请求比读请求更低的优先级。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY，降低该语句的优先级。

解锁，释放当前线程获得的所有锁：

代码100分UNLOCK TABLES

如在锁表期间，当前线程执行另一个LOCK TABLES或START TRANSACTION（对InnoDB存储引擎），或与服务器的连接被关闭时，会隐含地执行UNLOCK TABLES。

通过SHOW STATUS LIKE "table_locks%"查看表级锁使用情况。table_locks_waited比较高说明存在较严重的表级锁争用。

InnoDB行级锁

可以通过SHOW STATUS LIKE "innodb_row_lock%"，或查看information_schema中相关的表，或通过设置InnoDB Monitors查看行级锁争夺情况。

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

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

另外，为了允许行级锁和表级锁共存，InoDB还有两种内部使用的意向锁，二者都是表锁：

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

InoDB行级锁模式兼容性如下（纵向是当前锁模式，横向是请求锁模式）：

对于UPDATE、DELETE、INSERT语句会自动给涉及数据集加排他锁（X）。对普通SELECT语句不会加任何锁，可通过select_statement LOCK IN SHARE MODE加共享锁或select_statement FOR UPDATE加排他锁,并需进行提交或回滚。

意向锁是InnoDB自动加的。

InnoDB行级锁是通过给索引上的索引项或间隙加锁来实现的，共分三种：

• Record锁：对索引项加锁。
• Gap锁：对索引项之间的间隙（包括第一条记录前和最后一条记录后）加锁。
• Next-Key锁：前两种的组合，对索引项和间隙加锁。当使用范围条件而不是相等条件加锁时，会对符合条件的已有记录的索引项加锁，对并不存在相应记录但索引值在范围内的间隙（GAP）也会加锁。如果使用相等条件给一个不存在的记录加锁，也会使用Next-Key锁。InnoDB使用Next-Key锁的目的，一方面为了防止幻读，另一方面为了满足其恢复和复制的需要。

InnoDB行级锁的特点，需注意如下问题：

• 如不通过索引条件查询时，会锁定表中的所有记录，就如表级锁。
• 虽然是访问不同的行，但如果使用的是相同的索引项，是会出现锁冲突的。这是因为行级锁是对索引加锁，而不是对记录加锁。
• 当表有多个索引时，不同的事务可以使用不同的索引锁定不同的行。但如果是相同的行，则会等待。
• 即使在条件中使用了索引字段，但是否使用索引是由MySQL通过判断不同执行计划的代价决定的。

InnoDB存储引擎中不同SQL在不同隔离级别下的锁比较（off/on指变量innodb_locks_unsafe_for_binlog的值）：

INSERT INTO ... SELECT ...和CREATE TABLE ... SELECT ...叫做不确定的SQL，属于不安全的SQL，不推荐使用。如确实需要使用，又不希望因加锁对源表并发更新产生影响，可使用以下方法：

• 将innodb_locks_unsafe_for_binlog设置为on，强制使用多版本数据库（MVCC），但可能无法使用binlog正确恢复和复制数据。
• 使用SELECT ... INTO OUTFILE ...和LOAD DATA INFILE ...间接实现，这种方式不会对源表加锁。
• 使用基于行数据的binlog格式和基于行数据的复制。

InnoDB表级锁

以下两种情况可以考虑使用表级锁：

• 事务需要更新大部分或全部数据，表又比较大。
• 事务涉及多个表，很可能引起死锁，造成大量事务回滚。

使用表级锁需要注意：

• 虽然LOCK TABLES可以给InnoDB表加表级锁，但表级锁不是由InnoDB存储引擎管理的，而是由其上一层——MySQL Server管理的。仅当autocommit=0、innodb_table_locks=1时，InnoDB才能知道MySQL Server加的表级锁，MySQL Server也才能知道InnoDB加的行级锁。这样InnoDB才能自动识别涉及表级锁的死锁。
• 在用LOCK TABLES给InnoDB表加锁时，需将autocommit设为0。事务结束前，不要用UNLOCK TABLES，因其会隐含地提交事务。COMMIT和ROLLBACK不能释放表级锁，必须使用UNLOCK TABLES。

死锁

MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。InnoDB，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB可能发生死锁。

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回滚，另一个事务获得锁继续完成事务。但在涉及外部锁或涉及表级锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout解决。

减少锁冲突和死锁的方法：

• 尽量使用较低的隔离级别。
• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
• 选择合理的事务大小，小事务发生锁冲突的几率也小。
• 尽量用相等条件访问数据，这样可以避免Next-Key锁对并发插入的影响。
• 不要申请超过实际需要的锁级别，除非必需，查询时不要显式加锁。
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少发生死锁的几率。
• 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同顺序来访问表，这样可以大大降低锁死发生的概率。
• 在程序以批量方式处理数据的时候，如果实现对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
• 在可重复读隔离级别下，如果两个线程同时对相同条件的记录用SELECT ... FOR UPDATE加排他锁，在没有符合该条件记录的情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况将隔离级别改成已提交读就可避免问题。
• 当隔离级别为已提交读时，如果两个线程都先执行SELECT ... FOR UPDATE判断是否存在符合条件的记录，如果没有就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待。等第一个线程提交后，第二个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第三个线程来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

可以使用SHOW ENGINE INNODB STATUS查看最后一个死锁产生的原因。

事务

事务概述

事务的ACID属性：

• 原子性（Actomicity）：事务是一个原子操作单元，对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据必须保持一致状态。即所有相关的数据规则都必须应用于事务中对数据的修改，所有内部数据结构（如索引）也必须是正确的。
• 隔离性（Isolation）：提供一定的隔离机制，保证事务不受外部并发操作的影响，事务处理过程的中间状态对外部是不可见的。
• 持久性（Durable）：事务完成后，其对数据的修改是永久性的。

并发事务处理的问题：

• 更新丢失：当多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，最后的更新覆盖了由其他事务所做的更新。
• 脏读：一个事务正在对一条记录做修改，在这个事务提交前，如果另一个事务也来读取同一条记录，就会读取到脏数据（如果不加控制，读取到第一个事务修改的数据，而后第一个事务回滚，第二个事务读取到的数据就处于不一致状态）。
• 不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读取过的数据，却发现其读出的数据已经发生改变或被删除。
• 幻读：一个事务按照相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足查询条件的新数据。

防止更新丢失是应用的责任，需要应用对要更新的数据加锁来解决。脏读、不可重复读、幻读其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。事务隔离实质上是使事务在一定程度上串行化。数据库实现事务隔离的方式基本上分两种：

• 在读数据前加锁，阻止其他事务对数据进行修改。
• 数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也称为多版本数据库。不用加锁，通过生成数据请求时间点的一致性数据快照来提供一定级别的一致性读取。

有以下4个事务隔离级别：

可使用语句改变事务隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED}|{READ COMITTED}|{REPEATABLE READ}|SERIALIZABLE

InnoDB事务

默认情况下，InnoDB是自动提交事务的，即每执行一条语句提交一次事务。可设置变量autocommit指定是否自动提交。

在同一个事务中，最好不要使用不同存储引擎的表，否则ROLLBACK需要对非事务表进行特别的处理，因为COMMIT、ROLLBACK只能对事务表有效。通常情况下，只对提交的事务记录到二进制日志中，但如果一个事务中包含非事务表，那么回滚的操作也会被记录到二进制日志中，以确保非事务表的更新也可以被复制到从数据库中。

所有的DDL语句都是不能回滚的，并且部分DDL语句会造成隐式的事务提交。

开始事务：

代码100分{START TRANSACTION}|{BEGIN [WORK]}

提交事务：

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

回滚事务，可以回滚到指定的savepointname。注意，可以回滚事务的一个部分，但不能提交事务的一个部分：

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] [TO SAVEPOINT savepointname]

CHAIN和RELEASE子句用于定义事务提交或回滚后的操作：CHAIN会立即启动一个新事务，并且和原先的事务有相同的隔离级别；RELEASE会断开客户端和服务器之间的连接。默认是NO CHAIN NO RELEASE。

定义SAVEPOINT。可以定义多个SAVEPOINT，如果定义了相同名字的SAVEPOINT，则后面定义的覆盖前面定义的：

SAVEPOINT savepointname

删除SAVEPOINT：

RELEASE SAVEPOINT savepointname

分布式事务

当前分布式事务只支持InnoDB存储引擎。

一个分布式事务会涉及多个分支事务（XA事务），这些XA事务必须一起被提交，或一起被回滚。

使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器：

• 资源管理器（RM）：必须可以提交和回滚由TM管理的事务。当执行XA语句时，MySQL服务器相当于资源管理器。
• 事务管理器（TM）：与RM进行通信，用于协调作为分布式事务一部分的各个XA事务。当执行XA语句时，与服务器连接的客户端相当于事务管理器。

执行分布式事务的过程使用两阶段提交：

1. 第一阶段，所有分支事务被预备好，即它们被TM告知准备提交。
2. 第二阶段，TM告知所有RM需要提交还是回滚。如果在第一阶段，所有XA事务指示都能提交，则在第二阶段所有XA事务都被告知需要提交；如在第一阶段，任一XA事务指示不能提交，则在第二阶段所有XA事务都被告知需要回滚。

启动XA事务：

XA START|BEGIN xid [JOIN|RESUME]

每个XA事务必须有一个唯一的xid，该值不能被其他的XA事务使用。xid由客户端提供，或由MySQL服务器生成，包含3个部分："gtrid"[,"bqual"[,formatID]]。

• gtrid是分布式事务标识符，相同的分布式事务应使用相同的gtrid。
• bqual是一个分支限定符，默认是空串。对一个分布式事务中的每个XA事务，bqual值必须是唯一的。
• formatID是一个数字，用于标识gtrid和bqual值使用的格式，默认是1。

使XA事务进入PREPARE状态，也即两阶段提交的第一阶段：

XA END xid [SUSPEND [FOR MIGRATE]];
XA PREPARE xid;

提交XA事务，进入两阶段提交的第二阶段：

XA COMMIT xid [ONE PHASE]

回滚XA事务，进入两阶段提交的第二阶段：

XA ROLLBACK xid

返回当前数据库中处于PREPARE状态的XA事务详细信息：

XA RECOVER

MySQL的分布式事务还存在比较严重的缺陷：

1. 如果XA事务在到达PREPARE状态时，数据库异常重启后，可以继续对XA事务进行提交或回滚，但提交的事务没有写如binlog，可能导致使用binlog恢复时丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。
2. 如果某个XA事务的客户端连接异常终止，数据库会自动回滚未完成的XA事务。如果此时XA事务已经执行到PREPARE状态，那么这个分布式事务的其他XA事务可能已经提交。这个XA事务回滚，会导致分布式事务不完整。