MySQL 事务控制和锁定语句

MySQL 支持对 MyISAM 和 MEMORY 存储引擎的表进行表级锁定，对 BDB 存储引擎的表进行页级锁定，对 InnoDB 存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成

lock table 和 unlock table

LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止

UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁，具体语法如下：

LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

事务控制

MySQL 通过 set autocommit，start transaction，commit 和 rollback 等语句支持本地事务，具体语法如下：

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

默认情况下，MySQL 是自动提交（Autocommit）的，如果需要通过明确的 Commit 和 Rollback 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务 start transaction 或 begin 语句可以开始一项新的事务

commit 和 rollback 用来提交或者回滚事务

chain 和 release 子句分别用来定义在事务提交或者回滚之后的操作，chain 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，release 则会断开和客户端的连接

set autocommit 可以修改当前连接的提交方式，如果设置了 set autocommit=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚

如果只是对某些语句需要进行事务控制，则使用 start transaction 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改 set autocommit=0 来控制事务比较方便，这样不用在每个事务开始的时候再执行 start transaction 语句

如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock tables 被执行

在事务中可以通过定义 savepoint，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 savepoint，满足不同的条件时，回滚不同的 savepoint。需要注意的是，如果定义了相同名字的 savepoint，则后面定义的 savepoint 会覆盖之前的定义。对于不再需要使用的 savepoint，可以通过 release savepoint 命令删除 savepoint，删除后的 savepoint，不能再执行 rollback to savepoint 命令

分布式事务的使用

当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚

分布式事务的原理

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

• 资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器该管理器必须可以提交或回滚由 RM 管理的事务。例如，多台 MySQL 数据库作为多台资源管理器或者几台 Mysql 服务器和几台 Oracle 服务器作为资源管理器
• 事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识

MySQL 执行 XA MySQL 时，MySQL 服务器相当于一个用于管理分布式事务中的 XA 事务的资源管理器。与 MySQL 服务器连接的客户端相当于事务管理器

要执行一个分布式事务，必须知道这个分布式事务涉及到了哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚时。根据每个资源管理器报告的有关执行情况的内容，这些分支事务必须作为一个原子性操作全部提交或回滚。要管理一个分布式事务，必须要考虑任何组件或连接网络可能会出现故障

用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后

• 在第一阶段，所有的分支被预备好。即它们被 TM 告知要准备提交。通常，这意味着用于管理分支的每个 RM 会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段
• 在第二阶段，TM 告知 RMs 是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚

在有些情况下，一个分布式事务可能会使用一阶段提交。例如，当一个事务管理器发现，一个分布式事务只由一个事务资源组成（即单一分支），则该资源可以被告知同时进行预备和提交

分布式事务的语法

分布式事务（XA 事务）的 SQL 语法主要包括：

XA {START|BEGIN} xid [JOIN|RESUME]

XA START xid 用于启动一个带给定 xid 值的 XA 事务。每个 XA 事务必须有一个唯一的 xid 值，因此该值当前不能被其他的 XA 事务使用xid 是一个 XA 事务标识符，用来唯一标识一个分布式事务。xid 值由客户端提供，或由MySQL 服务器生成。xid 值包含 1～3 个部分：

xid: gtrid [, bqual [, formatID ]]

• gtrid 是一个分布式事务标识符，相同的分布式事务应该使用相同的 gtrid，这样可以明确知道 xa 事务属于哪个分布式事务
• bqual 是一个分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务，bqual 值必须是唯一的
• formatID 是一个数字，用于标识由 gtrid 和 bqual 值使用的格式，默认值是 1

下面其他 XA 语法中用到的 xid 值，都必须和 START 操作使用的 xid 值相同，也就是表示对这个启动的 XA 事务进行操作

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

使事务进入 PREPARE 状态，也就是两阶段提交的第一个提交阶段

XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

这两个命令用来提交或者回滚具体的分支事务。也就是两阶段提交的第二个提交阶段，分支事务被实际的提交或者回滚

XA RECOVER

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

分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时的故障解决。XA 的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等

存在的问题

虽然 MySQL 支持分布式事务，但是在测试过程中，还是发现存在一些问题

如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写 binlog，存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致