Skip to content

19. MySQL事务与锁定语句

MySQL 支持对 MyISAM 和 MEMORY 存储引擎的表进行表级锁定，对 BDB 存储引擎的表进行页级锁定，对 InnoDB 存储引擎的表进行行级锁定。

1. 锁定、解锁表⚓

-- 锁表
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

  -- 一些操作

-- 解锁
UNLOCK TABLES

MySQL使客户端会话（session）能够明确获取表锁，以便与其他会话协作以访问表，或者防止其他会话在会话需要对其进行独占访问期间修改表。

LOCK TABLES显式获取当前客户端会话的表锁。可以为表或视图获取表锁。在获取新锁之前，LOCK TABLES 隐式释放当前会话持有的任何表锁。

UNLOCK TABLES显式释放当前会话持有的任何表锁。

2. 事务控制⚓

MySQL支持的本地事务的语句：

-- 开启事务
START TRANSACTION [WITH CONSISTENT SNAPSHOT];
BEGIN [WORK];
-- 提交事务
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE];
-- 回滚事务
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE];
-- 设置自动提交事务
SET autocommit = {0 | 1};
  • START TRANSACTION 或 BEGIN 语句可以开始一项新的事务。复合语句中使用 START TRANSACTION 开启事务。
  • COMMIT 和 ROLLBACK 用来提交或者回滚事务。
    • CHAIN 和 RELEASE 子句分别用来定义在事务提交或者回滚之后的操作
    • CHAIN 会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别
    • RELEASE 则会断开和客户端的连接。
  • SET AUTOCOMMIT 可以修改当前连接的提交方式。如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

注意事项：

  • 默认情况下，MySQL 是自动提交（Autocommit）的，这意味着只要你修改了表，MySQL就会将其持久化到磁盘上，不可回滚。如果需要通过明确的 Commit 和 Rollback 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务。

  • 如果在锁表期间，用 START TRANSACTION 命令开始一个新事务，会造成一个隐含的 UNLOCK TABLES 被执行。

  • 和 Oracle 的事务管理相同，所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。

3. 还原点⚓

在事务中可以通过定义 SAVEPOINT， 指定回滚事务的一个部分， 但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚到不同的 SAVEPOINT。

-- 设置还原点
SAVEPOINT identifier
-- 回滚到还原点
ROLLBACK [WORK] TO [SAVEPOINT] identifier
-- 删除还原点
RELEASE SAVEPOINT identifier

只有InnoDB支持对事务设置还原点（SAVEPOINT）。

4. 分布式事务⚓

只有InnoDB支持分布式事务（XA Transaction）。

4.1 原理⚓

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

  • 资源管理器（RM）：提供对事务资源的访问途径。数据库服务器就是一种资源管理器。该管理器必须可以提交或回滚由 RM 管理的事务。
  • 事务管理器（TM）：协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务” 。分布式事务和各分支通过一种命名方法进行标识。

MySQL的XA实现是，让MySQL服务器表现的像是处理XA事务的RM，连接到服务器的客户端程序表现的像是TM。

分布式事务的执行使用两个阶段的提交。这是在执行了全局事务的分支执行的操作之后发生的。

  1. 第一阶段，所有的分支准备完毕。意味着已经准备好提交了。
  2. 第二阶段，TM 告知 RMs 提交或回滚。如果任何分支在准备时指示它将无法提交，则告知所有分支都回滚。

4.2 分布式事务的语法⚓

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

-- 使事务进入 PREPARE 状态，也就是两阶段提交的第一个提交阶段。
XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid

-- 第二个提交阶段，分支事务被实际的提交或者回滚。
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

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

xid 是一个 XA 事务标识符，用来唯一标识一个分布式事务。xid 值由客户端提供，或由MySQL 服务器生成。xid 值包含 1～3 个部分：

xid: gtrid [, bqual [, formatID ]]
  • gtrid 是一个分布式事务标识符， 相同的分布式事务应该使用相同的 gtrid， 这样可以明确知道 xa 事务属于哪个分布式事务。最好写为十六进制字符串 (X'6162', 0x6162)
  • bqual 是一个分支限定符， 默认值是空串。 对于一个分布式事务中的每个分支事务，bqual 值必须是唯一的。最好写为十六进制字符串 (X'6162', 0x6162)
  • formatID 是一个数字，用于标识由 gtrid 和 bqual 值使用的格式，默认值是 1。

4.3 XA 事务使用流程⚓

XA事务通过以下流程执行：

  1. 使用XA START开启事务并将其置为ACTIVE激活状态。
  2. 对于ACTIVE状态的事务，执行构成事务的SQL语句，然后执行XA END语句，此语句将事务置为IDLE怠惰状态。
  3. 对于IDLE的事务，可以执行以下两种语句：
    1. XA PREPARE：将事务置为PREPARED准备状态。此时可以用XA RECOVER列出事务的xid值。
    2. XA COMMIT ... ONE PHASE：准备并提交事务。
  4. 对于PREPARED的事务，可以执行XA COMMIT提交并终止事务，或者执行XA ROLLBACK回滚并终止事务。

注意事项：

  • XA START不支持[JOIN|RESUME]子句
  • XA END不支持[SUSPEND [FOR MIGRATE]]子句。？？？
  • 如果XA START已经开启了一个XA事务，那么不能使用本地事务，隐式的提交（存在隐式提交的语句）也是如此；反之亦然。

4.4 存在的问题⚓

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

​ 如果分支事务的客户端连接异常中止， 那么数据库会自动回滚未完成的分支事务， 如果此时分支事务已经执行到 prepare 状态， 那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。

    如果应用对事务的完整性有比较高的要求， 那么对于当前的版本， 则不推荐使用分布式事务。