事务控制和锁定语句

1、MyISAM和MEMORY存储引擎的表级锁定。

2、SDB的页级锁定

3、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

二、事务控制

       默认：自动提交。。如果需要通过明确的Commit和Rollback来提交和回滚事务，则需要通过明确的事务控制命令来开始事务。

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

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

       因此，在同一个事务中，最好不使用不同存储引擎的表，否则rollback时需要对非事务类型的表进行特别的处理，因为commit、rollback只能对事务类型的表进行提交和回滚。

通常情况下，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型表的更新可以被赋值到从数据库slave中。

  所有的DDL语句是不能回滚的，且部分的ＤＤＬ语句会造成隐式的提交。

　　在事务中可以通过定义savepoint，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。删除savepoint:release savepoint。删除后的savepoint不能再执行rollback to point命令。

三、分布式事务的使用

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

2、分布式事务的原理（一个或多个资源管理器，一个事务管理器）

资源管理器（RM）：用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM管理的事务。

事务管理器（TM）：用于协调作为一个分布式事务一部分的事务。TM与管理每个事物的RMs进行通信。在一个分布式事务中，各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种明明方法进行标识。

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

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

第一阶段，所有的分支被预备好。即他们被TM告知要准备提交。通常这意味着永固管理分支的每个RM会记录对于被文档保存的分支的行动，分支指示是否他们可以这么做，这些结果被用于第2阶段。

第二阶段，TM告知RMs是否需要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

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

（1）分布式事务的语法：

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

启动一个带给定xid值的XA事务。每个XA事务必须有一个唯一的xid值。xid值由客户端提供，或由MySQL服务器生成。xid值包含1-3个部分：

xid：gtrid[,bqual[,formatID]]

其中gtrid是一个分布式事务标识符，

bqual是一个分支限定符，默认是空串。对于每一个分布式事务中的每个分支事务，bqual值必须是唯一的。

formatID是一个数字，用于标识由gtrid和bqual值使用的格式，默认值是1。

使事务进入prepare状态：即2阶段的第一个提交阶段

XA END xid  [SUSPEND[FOR MIGRATE]]

XA PREPARE xid

提交或者回滚具体的分支事务：即2阶段的第二个阶段，分支事务被实际地提交或者回滚。

XA  COMMIT xid [ONE  PUASE]

XA  ROLLBACK  xid

XA  RECOVER：

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

（2）存在的问题

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

如果分支事务在执行到prepare状态时，数据库异常、且不能再正常启动，需要使用备份和binlog来恢复数据，那么在那些prepare状态的分支事务因为并没有记录到binlog，所以不能通过binlog进行恢复，在数据库恢复后，将丢失这部分的数据。