深入浅出mysql_14.事务控和锁定语句

十四.  事务控制和锁定语句

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

14.1 LOCK TABLE 和 UNLOCK TABLE

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

LOCK TABLES table_name [as alias] {READ [LOCAL]| [LOW_PRIORITE] WRITE}

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

14.2 事务控制 

mysql 通过 set autocommit、start TRANSACTION 、commit、和rollback等语句支持本地事务；

默认情况下，mysql是自动提交（autocommit）的，如果需要明确的commit和rollback来提交和回滚事务，那么就需要明确的事务控制命令来开始事务。

语法：

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

• START TRANSACTION 或者BEGIN 可以开始一项新的事务。
• commit 和rollback用来提交或者回滚事务
• chain 和RELEASE 分别用来定义事务提交或者回滚之后的操作，chain或立即启动一个新的事务，并且和刚才的事务有相同的隔离级别，RELEASE则会断开和客户端的连接；
• SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

例子

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

 

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

例子:

start transtion;
insert into table_name values() ...;
savepoint test;
insert into table_name ....;
rollback to savtepoint test;
commit;
这时只插入了第一条；

14.3 分布式事务的使用

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

14.3.1 分布式事务的原理
 

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

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

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

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

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

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

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

14.3.2 分布式事务的语法

语法

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 的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等，如表 14-5所示的例子演示了一个简单的分布式事务的执行，事务的内容是在 DB1 中插入一条记录，同时在 DB2 中更新一条记录，两个操作作为同一事务提交或者回滚。