MySQL基础之事务编程学习笔记

## 方法1：通过set autocommit = 0关闭事务自动提交
# 查询是否开启事务自动提交，1：事务自动提交；2：事务不自动提交
SELECT @@autocommit;
# 关闭事务自动提交
SET autocommit = 0;
## 方法2：通过BEGIN 或者 START TRANSACTION命令
# 开启一个事务，使用这个命令之后，事务要使用commit或者commit work命令才会提交
BEGIN;
# START TRANSACTION命令和Begin命令效果一样 
START TRANSACTION;

# 查询completion_type类型
SHOW VARIABLES LIKE 'completion_type';
# 也可以使用@@系统变量方式查询
SELECT @@completion_type;
## completion_type默认值为0，它还有1和2两种值，不过在新版mysql5.7测试，发现值为2的情况不起效
SET @@completion_type = 1;# commit+chain(链事务，提示事务之后还会start transaction)
SET @@completion_type = 2;# commit+release(经过测试，在新版mysql5.7这个设置不起效，以前版本开启之后是会关闭Session的)

# 新建一个测试表
CREATE TABLE t (a INT,PRIMARY KEY(a))ENGINE = INNODB;
# 开启链事务
SET @@completion_type = 1;
# 开启一个事务，如下两个命令皆可
BEGIN; START TRANSACTION;
# 新增一条数据
INSERT INTO t SELECT 1;
# commit work在@@completion_type = 1的情况相关于commit+start transaction
COMMIT WORK;
# 新增一条数据
INSERT INTO t SELECT 2;
# 新增一条重复数据，让数据库报错
INSERT INTO t SELECT 2;
# 查询，有两条数据
SELECT * FROM t;
# 回滚事务
ROLLBACK;
# 再次查询，发现只有1这条数据，由此说明COMMIT WORK在commit之后，还开启了一个事务，当然是在autocommit值为1的情况进行验证的
SELECT * FROM t;

# completion_type=2在旧版估计才有用，我在5.7版本验证，发现并不起作用
SET @@completion_type=2;
# 开启事务
BEGIN;
# 新增一条数据
INSERT INTO t SELECT 1;
# commit work相关于commit+release，这个操作会关闭session
COMMIT WORK;
# 使用@@version验证会话是否关闭
SELECT @@version;

SELECT @@tx_isolation;

SELECT @@global.tx_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

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

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid

XA RECOVER [CONVERT XID]

mysql>XA START 'a';
mysql>INSERT INTO t SELECT 1;
mysql>XA END 'a';
mysql>XA PREPARE 'a';
mysql>XA RECOVER;
mysql>XA COMMIT 'a';

create table t (username varchar(20),money float)engine=innodb;
insert into t(username,money) values('david',40000);
insert into t(username,money) values('jack',40000);

import javax.transaction.xa.Xid;

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

    public JTAXid(){}

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

    @Override
    public int getFormatId() {
        return formatId;
    }

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

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

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.SQLException;
import java.sql.Statement;

public class JTADemo {
	//数据源设置
    public static MysqlXADataSource getDataSource(String connString,String user,String pwd){
        try{
            MysqlXADataSource dataSource = new MysqlXADataSource();
            dataSource.setUrl(connString);
            dataSource.setUser(user);
            dataSource.setPassword(pwd);
            return dataSource;
        }catch (Exception e){
            e.fillInStackTrace();
            return null;
        }
    }

    public static void main(String[] args){
        String connStr1 = "jdbc:mysql://192.168.0.11:3306/bank_shanghai";
        String connStr2 = "jdbc:mysql://192.168.0.12:3306/bank_beijing";
        XAResource xaResource1=null,xaResource2=null;
        Statement statement1 =null,statement2 =null;
        try {
            MysqlXADataSource dataSource1 = getDataSource(connStr1, "root", "zaq12wsx");
            XAConnection xaConnection1 = dataSource1.getXAConnection();
            xaResource1 = xaConnection1.getXAResource();
            Connection connection1 = xaConnection1.getConnection();
            statement1 = connection1.createStatement();

            MysqlXADataSource dataSource2 = getDataSource(connStr2, "root", "zaq12wsx");
            XAConnection xaConnection2 = dataSource2.getXAConnection();
            xaResource2 = xaConnection2.getXAResource();
            Connection connection2 = xaConnection2.getConnection();
            statement2 = connection2.createStatement();
        }catch (SQLException e){
            e.printStackTrace();
        }

        try{

            Xid xid1 = new JTAXid(101,new byte[]{0x01},new byte[]{0x02});
            Xid xid2 = new JTAXid(101,new byte[]{0x11},new byte[]{0x12});

            xaResource1.start(xid1, XAResource.TMNOFLAGS);
            statement1.execute("update t set money = money-1000 where user='david'");
            xaResource1.end(xid1, XAResource.TMSUCCESS);

            xaResource2.start(xid2, XAResource.TMNOFLAGS);
            statement2.execute("update t set money = money+1000 where user='jack'");
            xaResource2.end(xid2, XAResource.TMSUCCESS);

            int ret1 = xaResource1.prepare(xid1);
            int ret2 = xaResource2.prepare(xid2);
			//两个操作都正常运行才提交事务
            if (ret1 == XAResource.XA_OK && ret2 == XAResource.XA_OK) {
                xaResource1.commit(xid1, false);
                xaResource2.commit(xid2, false);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}