分布式事务,不好的事务习惯

分布式事务

InnoDB存储引擎支持XA事务，通过XA事务可以来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中所有参与的事务要么都提交、要么都回滚，这对于事务原有的ACID要求又有了提高。另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIABLE。

XA事务允许不同数据库之间的分布式事务，如：一台服务器是MySQL数据库的，另一台是Oracle数据库的，又可能还有一台服务器是SQL Server数据库的，只要参与全局事务中的每个节点都支持XA事务。分布式事务可能在银行系统的转账中比较常见，如一个用户需要从上海转10 000元到北京的一个用户上：

#Bank@Shanghai：

update account set money=money-10000 where user='David'；

#Bank@Beijing

Update account set money=money+10000 where user='Mariah'；

这种情况一定需要分布式的事务，如果不能都提交或都回滚，在任何一个节点出现问题都会导致严重的结果：要么是David的账户被扣款，但是Mariah没收到；又或者是David的账户没有扣款，但是Mariah还是收到钱了。

分布式事务由一个或者多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。

事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务中的所有资源管理器进行通信。

应用程序：定义事务的边界，指定全局事务中的操作。

在MySQL的分布式事务中，资源管理器就是MySQL数据库，事务管理器为连接到MySQL服务器的客户端。下图显示了一个分布式事务的模型：

分布式事务使用两段式提交（two-phase commit）的方式。在第一个阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。第二个阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。

当前Java的JTA（Java Transaction API）可以很好地支持MySQL的分布式事务，需要使用分布式事务应该认真参考其API。

下面的一个示例显示了如何使用JTA来调用MySQL的分布式事务，例子就是前面的银行转账，如下所示。

import javax.transaction.xa.Xid;

class MyXid implements Xid {
    public int formatId;
    public byte gtrid[];
    public byte bqual[];

    public MyXid() {
    }

    public MyXid(int formatId, byte gtrid[], byte bqual[]) {
        this.formatId = formatId;
        this.gtrid = gtrid;
        this.bqual = bqual;
    }

    public int getFormatId() {
        return formatId;
    }

    public byte[] getBranchQualifier() {
        return bqual;
    }

    public byte[] getGlobalTransactionId() {
        return gtrid;
    }
}


import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

import javax.sql.XAConnection;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;
import java.sql.Connection;
import java.sql.Statement;

public class xa_demo {
    public static MysqlXADataSource GetDataSource(String connString,String user,String passwd) {
        try {
            MysqlXADataSource ds = new MysqlXADataSource();
            ds.setUrl(connString);
            ds.setUser(user);
            ds.setPassword(passwd);
            return ds;
        } catch (Exception e) {
            System.out.println(e.toString());
            return null;
        }
    }

    public static void main(String[] args) {
        String connString1 =
                "jdbc：mysql：//192.168.24.43：3306/bank_shanghai";
        String connString2 =
                "jdbc：mysql：//192.168.24.166：3306/bank_beijing";
        try {
            MysqlXADataSource ds1 = GetDataSource(connString1, "peter", "12345");
            MysqlXADataSource ds2 = GetDataSource(connString2, "david", "12345");
            XAConnection xaConn1 = ds1.getXAConnection();
            XAResource xaRes1 = xaConn1.getXAResource();
            Connection conn1 = xaConn1.getConnection();
            Statement stmt1 = conn1.createStatement();
            XAConnection xaConn2 = ds2.getXAConnection();
            XAResource xaRes2 = xaConn2.getXAResource();
            Connection conn2 = xaConn2.getConnection();
            Statement stmt2 = conn2.createStatement();
            Xid xid1 = new MyXid(
                    100,
                    new byte[]{0x01},
                    new byte[]{0x02});
            Xid xid2 = new MyXid(
                    100,
                    new byte[]{0x11},
                    new byte[]{0x12});
            try {
                xaRes1.start(xid1, XAResource.TMNOFLAGS);
                stmt1.execute("update account set money = money - 10000 where user = 'david'");
                xaRes1.end(xid1, XAResource.TMSUCCESS);
                xaRes2.start(xid2, XAResource.TMNOFLAGS);
                stmt2.execute("update account set money = money + 10000 where user = 'mariah'");
                xaRes2.end(xid2, XAResource.TMSUCCESS);
                int ret2 = xaRes2.prepare(xid2);
                int ret1 = xaRes1.prepare(xid1);
                if (ret1 == XAResource.XA_OK&&ret2 == XAResource.XA_OK){
                    xaRes1.commit(xid1, false);
                    xaRes2.commit(xid2, false);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
}

参数innodb_support_xa可以查看是否启用了XA事务支持（默认为ON）：

show variables like 'innodb_support_xa'G

***************************1.row***************************

Variable_name：innodb_support_xa

Value：ON

1 row in set（0.01 sec）

另外需要注意的是，对于XA事务的支持，是在MySQL体系结构的存储引擎层。因此即使不参与外部的XA事务，MySQL内部不同存储引擎层也会使用XA事务。假设我们用START TRANSACTION开启了一个本地的事务，往NDB Cluster存储引擎的表t1插入一条记录，往InnoDB存储引擎的表t2插入一条记录，然后COMMIT。在MySQL内部，也是通过XA事务来协调的，这样才可以保证两张表的原子性。

不好的事务习惯

在循环中提交

开发人员非常喜欢在循环中进行事务的提交，下面是他们可能常写的一个存储过程：

CREATE PROCEDURE load1(count int unsigned)

begin

　　declare s int unsigned default 1;

　　declare c char(80) default repeat('a',80);

　　while s＜=count do

　　　　insert into t1 select NULL,c;

　　　　commit;

　　　　set s=s+1;

　　end while;

end;

其实，在这个例子中，是否加上commit并不关键，因为InnoDB存储引擎默认为自动提交，因此上面的存储过程中去掉commit，结果是完全一样的。这也是另一种容易忽视的问题：

CREATE PROCEDURE load2(count int unsigned)

begin

　　declare s int unsigned default 1;

　　declare c char(80) default repeat('a',80);

　　while s＜=count do

　　　　insert into t1 select NULL,c;

　　　　set s=s+1;

　　end while;

end;

不论上面哪个存储过程都存在一个问题：当发生错误时，数据库会停留在一个未知的位置。如我们要插入的是10 000条记录，但是在插入5000条时，发生了错误，而这时前5000条记录已经存放在数据库中，那我们应该怎么处理呢？还有一个问题是性能问题，上面两个存储过程都不会比在下面的一个存储过程快，因为它是放在一个事务里：

CREATE PROCEDURE load3(count int unsigned)

begin

　　declare s int unsigned default 1;

　　declare c char(80) default repeat('a',80);

　　start transaction;

　　　　while s＜=count do

　　　　　　insert into t1 select NULL,c;

　　　　　　set s=s+1;

　　　　end while;

　　commit;

end;

比较这3个存储过程的执行时间：

call load1(10000);

Query OK，0 rows affected（1 min 3. 15 sec）

truncate table t1;

call load2(10000);

Query OK，1 row affected（1 min 1. 69 sec）

truncate table t1;

call load3(10000);

Query OK，0 rows affected（0. 63 sec）

显然，第三种方法要快得多！这是因为，每一次提交都要写一次重做日志，因此存储过程load1和load2实际写了10 000次，而对于存储过程load3来说，实际只写了1次。可以对第二个存储过程load2的调用进行调整，同样可以达到存储过程load3的性能，如下代码所示。

begin;

call load2(10000);

commit;

大多数程序员会使用第一种或者第二种方法，有人可能不知道InnoDB存储引擎自动提交的情况，另外有些人可能持有以下两种观点：首先，在他们曾经使用过的数据库中，对于事务的要求总是尽快地进行释放，不能有长时间的事务；其次，他们可能担心存在Oracle数据库中由于没有足够UNDO产生的Snapshot Too Old的经典问题。MySQL InnoDB存储引擎上述两个问题都没有，因此程序员不论从何种角度出发，都不应该在一个循环中反复进行提交操作，不论是显式的提交还是隐式的提交。

使用自动提交

自动提交并不是好习惯，因为这对于初级DBA容易犯错，另外对于一些开发人员可能产生错误的理解。MySQL数据库默认设置使用自动提交（autocommit）。可以使用如下语句来改变当前自动提交的方式：

set autocommit=0;

也可以使用START TRANSACTION、BEGIN来显式地开启一个事务。显式开启事务后，在默认设置下（即参数completion_type等于0），MySQL会自动执行SET AUTOCOMMIT=0的命令，并在COMMIT或者ROLLBACK结束一个事务后执行SET AUTOCOMMIT=1。

另外，在不同的语言API时，自动提交是不同的。MySQL C API默认的提交方式是自动提交的，而MySQL Python API则是自动执行SET AUTOCOMMIT=0，以禁用自动提交。因此在选用不同的语言来编写数据库应用程序前，应该对连接MySQL的API做好研究。

在编写应用程序开发时，最好把事务的控制权限交给开发人员，即在程序端进行事务的开始和结束。同时，开发人员必须了解自动提交可能带来的问题。

使用自动回滚

InnoDB存储引擎支持通过定义一个HANDLER来进行自动事务的回滚操作，如一个存储过程中发生了错误，会自动对其进行回滚操作，因此很多开发人员喜欢在应用程序的存储过程中使用自动回滚操作，如下面的一个存储过程：

create procedure sp_auto_rollback_demo()

begin

　　declare exit handler for sqlexception rollback；

　　start transaction；

　　　　insert into b select 1；

　　　　insert into b select 2；

　　　　insert into b select 1；

　　　　insert into b select 3；

　　commit；

end；

存储过程sp_auto_rollback_demo首先定义了一个exit类型的handler，当捕获到错误时进行回滚。结构如下所示：

show create table bG

因此插入第二个记录1时会发生错误，但是因为启用了自动回滚的操作，因此这个存储过程的执行结果如下所示：

call sp_auto_rollback_demo;

select * from b；

Empty set（0.00 sec）

看起来运行没有问题，非常正常。但是，执行sp_auto_rollback_demo这个存储过程的结果到底是正确的还是错误的呢？

对于同样的存储过程sp_auto_rollback_demo，开发人员可能会进行这样的处理：

create procedure sp_auto_rollback_demo()

begin

　　declare exit handler for sqlexception begin rollback；select -1；end；

　　start transaction；

　　　　insert into b select 1；

　　　　insert into b select 2；

　　　　insert into b select 1；

　　　　insert into b select 3；

　　commit；

　　select 1；

end；

当发生错误时，先回滚，然后返回-1，表示运行有错误。运行正常，返回值1。因此这次运行的结果就会变成：

call sp_auto_rollback_demo()G

***************************1.row***************************

-1：-1

1 row in set（0.04 sec）

select * from b;

Empty set（0.00 sec）

看起来我们可以得到运行是否准确的信息。但问题还没有最终解决，对于开发来说，重要的不仅是知道发生了错误，而是发生了什么样的错误。因此自动回滚存在这样的一个问题。

使用自动回滚大多是以前使用Microsoft SQL Server数据库。在Microsoft SQL Server数据库中，可以使用SET XABORT ON来回滚一个事务。但是Microsoft SQL Server数据库不仅会自动回滚当前的事务，并且还会抛出异常，开发人员可以捕获到这个异常。因此，Microsoft SQL Server数据库和MySQL数据库在这方面是有所不同的。

对于事务的BEGIN、COMMIT和ROLLBACK操作，应该交给程序端来完成，存储过程只要完成一个逻辑的操作。

下面演示用Python语言编写的程序调用一个存储过程sp_rollback_demo，存储过程sp_rollback_demo和之前的存储过程sp_auto_rollback_demo在逻辑上完成的内容大致相同：

create procedure sp_rollback_demo()

begin

　　insert into b select 1；

　　insert into b select 2；

　　insert into b select 1；

　　insert into b select 3；

end;

和sp_auto_rollback_demo存储过程不同的是，在sp_rollback_demo存储过程中去掉了对于事务的控制语句，将这些操作都交由程序来完成。接着来看test_demo.py的程序源代码：

#!/usr/bin/env python

#encoding=utf-8

import MySQLdb

try:

conn=MySQLdb.connect(host="192.168.8.7",user="root",passwd="xx“,db="test")

cur=conn.cursor()

cur.execute("set autocommit=0")

cur.execute("call sp_rollback_demo")

cur.execute("commit")

except Exception,e:

cur.execute("rollback")

print e

观察运行test_demo.py这个程序的结果：

python test_demo.py

starting rollback

（1062，"Duplicate entry'1'for key'PRIMARY'"）

在程序中控制事务的好处是，我们可以得知发生错误的原因。如上述这个例子中，我们知道是因为发生了1062这个错误，错误的提示内容是Duplicate entry'1'for key'PRIMARY'，即发生了主键重复的错误，然后可以根据发生的原因来调试我们的程序。