3

Java事务分为:JDBC事务、JTA事务,容器事务。

JDBC事务

利用JDBC定义的java.sql.Connection接口中setAutoCommit(boolean)来达到自动提交和手动提交,只能局限在同一个数据库连接,不能跨库。
切记:MySQL只有InnoDB才有事务效果,其他引擎设置了autoCommit无效。

不用JDBC事务的DEMO

SQL

CREATE TABLE `test1` (
`id`  bigint(1) NOT NULL DEFAULT 0 ,
`name`  varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)

CREATE TABLE `test2` (
`id`  bigint(1) NOT NULL DEFAULT 0 ,
`name`  varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)

Java

package com.mousycoder.server.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcNoTransaction {

    public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    public final static String DB_URL = "jdbc:mysql://xxx.mysql.rds.aliyuncs.com/xxx";
    public final static String DB_USERNAME = "xx";
    public final static String DB_PASSWORD = "xxx";
    public static final String INSERT_TEST1 = "INSERT INTO test1(id,name) VALUES(?,?)";
    public static final String INSERT_TEST2 = "INSERT INTO test2(id,name) VALUES(?,?)";

    public static void main(String[] args) {
        Connection con = null;
        try {
            con = getConnection();
            insertTest1(con);
            insertTest2(con);
        } catch (ClassNotFoundException|SQLException  e ) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    public static Connection getConnection() throws ClassNotFoundException,
            SQLException {
        Connection conn = null;
        Class.forName(DB_DRIVER_CLASS);
        conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        return conn;
    }

    public static void insertTest1(Connection conn) throws SQLException {
        PreparedStatement stmt;
        stmt = conn.prepareStatement("INSERT INTO test1(id,name) VALUES(?,?)");
        stmt.setInt(1, 1);
        stmt.setString(2, "1");
        stmt.executeUpdate();
        System.out.println("======insert into test1 successfully======");
        stmt.close();

    }

    public static void insertTest2(Connection conn) throws SQLException {
        PreparedStatement stmt;
        stmt = conn.prepareStatement("INSERT INTO test2(id,name) VALUES(?,?)");
        stmt.setInt(1, 1);
        stmt.setString(2, "11"); // 故意长度超出
        stmt.executeUpdate();
        System.out.println("======insert into test2 successfully========");
        stmt.close();

    }
}

控制台输出

======insert into test1 successfully======
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3885)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
    at com.mousycoder.server.jdbc.JdbcNoTransaction.insertTest2(JdbcNoTransaction.java:61)
    at com.mousycoder.server.jdbc.JdbcNoTransaction.main(JdbcNoTransaction.java:22)

结果:
表test1有数据,表test2里没有数据,但是我们想让test1,test2 要么同时插入数据,要么都不插数据,这个时候就需要用到事务了。
顺便提一下,如果让test1抛异常,那么直接异常处理,结果是test1没数据,test2也没数据,但是这里并不是用到了事务,而是异常处理改变了程序走向。

利用JDBC事务的DEMO

Java

package com.mousycoder.server.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcTransaction {

    public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    public final static String DB_URL = "jdbc:mysql://xx.mysql.rds.aliyuncs.com/xxx";
    public final static String DB_USERNAME = "xxx";
    public final static String DB_PASSWORD = "xxx";
    public static final String INSERT_TEST1 = "INSERT INTO test1(id,name) VALUES(?,?)";
    public static final String INSERT_TEST2 = "INSERT INTO test2(id,name) VALUES(?,?)";

    public static void main(String[] args) {
        Connection con = null;
        try {
            con = getConnection();
            con.setAutoCommit(false);
            insertTest1(con);
            insertTest2(con);
            con.commit();
            System.out.println("=======JDBC Transaction commit===========");
        } catch (ClassNotFoundException|SQLException  e ) {
            try {
                con.rollback();
                System.out
                        .println("=======JDBC Transaction rolled back successfully=======");
            } catch (SQLException e1) {
                System.out.println("=======SQL Exception in rollback"
                        + e1.getMessage()); // 回滚必要条件:1.同一个transaction
                                            // 2.connection未关,所以这里要加异常处理
            }
            e.printStackTrace();
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    public static Connection getConnection() throws ClassNotFoundException,
            SQLException {
        Connection conn = null;
        Class.forName(DB_DRIVER_CLASS);
        conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        return conn;
    }

    public static void insertTest1(Connection conn) throws SQLException {
        PreparedStatement stmt;
        stmt = conn.prepareStatement("INSERT INTO test1(id,name) VALUES(?,?)");
        stmt.setInt(1, 1);
        stmt.setString(2, "1");
        stmt.executeUpdate();
        System.out.println("======insert into test1 successfully======");
        stmt.close();

    }

    public static void insertTest2(Connection conn) throws SQLException {
        PreparedStatement stmt;
        stmt = conn.prepareStatement("INSERT INTO test2(id,name) VALUES(?,?)");
        stmt.setInt(1, 1);
        stmt.setString(2, "11"); // 故意长度超出
        stmt.executeUpdate();
        System.out.println("======insert into test2 successfully========");
        stmt.close();

    }
}

控制台输出

======insert into test1 successfully======
=======JDBC Transaction rolled back successfully=======
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3885)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
    at com.mousycoder.server.jdbc.JdbcTransaction.insertTest2(JdbcTransaction.java:73)
    at com.mousycoder.server.jdbc.JdbcTransaction.main(JdbcTransaction.java:23)

结果:
表test1和表test2是没有数据的,因为insertTest2方法抛出了异常,直接到异常处理,进行了手动的rollback(结束事务,回滚先前操作,释放掉锁),如果insertTest1()和insertTest2()都正常插入,则调用commit()方法(结束事务,提交先前改变,释放掉锁)

源码分析

com.mysql.jdbc.ConnectionImpl.rollback()

    /**
     * The method rollback() drops all changes made since the previous
     * commit/rollback and releases any database locks currently held by the
     * Connection.
     *
     * @exception SQLException
     *                if a database access error occurs
     * @see commit
     */
    public void rollback() throws SQLException {
        synchronized (getConnectionMutex()) {
            checkClosed();
 
            try {
                if (this.connectionLifecycleInterceptors != null) {
                    IterateBlock<Extension> iter = new IterateBlock<Extension>(this.connectionLifecycleInterceptors.iterator()) {
 
                        @Override
                        void forEach(Extension each) throws SQLException {
                            if (!((ConnectionLifecycleInterceptor) each).rollback()) {
                                this.stopIterating = true;
                            }
                        }
                    };
 
                    iter.doForAll();
 
                    if (!iter.fullIteration()) {
                        return;
                    }
                }
                // no-op if _relaxAutoCommit == true
                if (this.autoCommit && !getRelaxAutoCommit()) {
                    throw SQLError.createSQLException("Can't call rollback when autocommit=true", SQLError.SQL_STATE_CONNECTION_NOT_OPEN,
                            getExceptionInterceptor());
                } else if (this.transactionsSupported) {
                    try {
                        rollbackNoChecks(); //真正执行rollback地方,发送rollback指令到数据库
                    } catch (SQLException sqlEx) {
                        // We ignore non-transactional tables if told to do so
                        if (getIgnoreNonTxTables() && (sqlEx.getErrorCode() == SQLError.ER_WARNING_NOT_COMPLETE_ROLLBACK)) {
                            return;
                        }
                        throw sqlEx;
 
                    }
                }
            } catch (SQLException sqlException) {
                if (SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE.equals(sqlException.getSQLState())) {
                    throw SQLError.createSQLException("Communications link failure during rollback(). Transaction resolution unknown.",
                            SQLError.SQL_STATE_TRANSACTION_RESOLUTION_UNKNOWN, getExceptionInterceptor());
                }
 
                throw sqlException;
            } finally {
                this.needsPing = this.getReconnectAtTxEnd();
            }
        }
    }

解析:

  1. 只允许拿到连接互斥锁的线程进入方法。

  2. 检查连接是否被强制关闭,若被强制关闭,则抛出"No operations allowed after connection closed." 异常。

  3. 如果autoCommit且relaxAutoCommit为false,则抛出"Can't call commit when autocommit=true"异常。

  4. 如果使用本地事务状态以及MySql的版本号至少大于5.0.0并且事务在服务器上。

  5. 如果支持事务,则执行。

  6. 执行rollback。

com.mysql.jdbc.ConnectionImpl.commit()

   public void commit() throws SQLException {
        synchronized (getConnectionMutex()) {
            checkClosed();
 
            try {
                if (this.connectionLifecycleInterceptors != null) {
                    IterateBlock<Extension> iter = new IterateBlock<Extension>(this.connectionLifecycleInterceptors.iterator()) {
 
                        @Override
                        void forEach(Extension each) throws SQLException {
                            if (!((ConnectionLifecycleInterceptor) each).commit()) {
                                this.stopIterating = true;
                            }
                        }
                    };
 
                    iter.doForAll();
 
                    if (!iter.fullIteration()) {
                        return;
                    }
                }
 
                // no-op if _relaxAutoCommit == true
                if (this.autoCommit && !getRelaxAutoCommit()) {
                    throw SQLError.createSQLException("Can't call commit when autocommit=true", getExceptionInterceptor());
                } else if (this.transactionsSupported) {
                    if (getUseLocalTransactionState() && versionMeetsMinimum(5, 0, 0)) {
                        if (!this.io.inTransactionOnServer()) {
                            return; // effectively a no-op
                        }
                    }
 
                    execSQL(null, "commit", -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
                }
            } catch (SQLException sqlException) {
                if (SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE.equals(sqlException.getSQLState())) {
                    throw SQLError.createSQLException("Communications link failure during commit(). Transaction resolution unknown.",
                            SQLError.SQL_STATE_TRANSACTION_RESOLUTION_UNKNOWN, getExceptionInterceptor());
                }
 
                throw sqlException;
            } finally {
                this.needsPing = this.getReconnectAtTxEnd();
            }
        }
        return;
    }

解析:

这段代码与rollback()基本一样,不同之处是commit()方法里执行的是 execSQL(null, "commit", -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
rollback()方法里执行的是 execSQL(null, "rollback", -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);

执行事务期间涉及到的sql

  • /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'

  • /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment

  • SET character_set_results = NULL

  • SET autocommit=1

  • SET sql_mode='STRICT_TRANS_TABLES'

  • SET autocommit=0

  • select @@session.tx_read_only

以上为不完全统计,jdbc包在执行一个真正的sql语句的时候,实际上会先执行一部分常用sql,把数据库的一些信息给缓存起来。

mysql&oracle区别

oracle的commit详细步骤

  • SGA中生成undo块

  • SGA中生成了产生改变的数据块和索引块

  • SGA中生成前两项的缓存redo信息

  • 依赖于前三项产生的数据量大小以及操作所需要的时间,buffer中的数据可能已经有一部分输出到磁盘

  • 获得所需全部锁

  • commit

  • 为事务生成一个SCN,SCN是oracle数据库的一种计时信息,用以保证事务的顺序性,同时还用于失败恢复和保证数据库的读一致性和检查点,无论何时何人提交,SCN自动加1

  • 将事务相关的未写入redo log file 中的redo信息从redo log buffer写入到redo log file ,这一步是真正的commit,这一步完成,才叫真正完成commit,事务条目从V$TRANSACTION中“删除

  • V$LOCK中记录的SESSION关于该事务的锁会释放,其他需要这些锁的事务被唤醒

  • 执行块清理,清理快头保存的事务信息

oracle的rollback详细步骤

  • 撤销已做的所有修改,从undo段读回数据,逆向执行commit中的操作,并将undo条目标记为已用,先前插入,则会删除,先前更新,则会回滚取消更新,先前删除,则回滚再次插入

  • 释放会话中所有的锁,唤醒等待锁。

mysql的commit详细步骤

  • InnoDB每次提交事务都会刷新日志innodb_log到磁盘,磁盘速度比较慢,不要频繁提交事务

JTA事务

JTA是一种高层,与实现无关,与协议无关的API,应用程序和应用服务器可以使用JTA来访问事务
计划用JTA界定事务,那么需要实现javax.sql.XADataSource、javax.sql.XAConnection、java.sql.XAResource。

容器事务

基于JTA,以及JNDI完成,容器负责事务的管理任务。

三种事务差异

  • JDBC 事务局限于一个数据库连接,使用简单。

  • JTA事务功能强大,可以跨多个数据库多个DAO,比较复杂。

  • 容器事务,局限于EJB使用。

补充知识

MySql常用命令

  • 查询隔离级别
    select @@tx_isolation;

  • 设置手动提交
    set autocommit=0 ;

  • 查看当前事务自动提交模式
    select @@autocommit;

  • 设置隔离级别
    set tx_isolation = 'READ-COMMITTED';

  • 查询表的状态
    show table status like 'test1';

  • 修改表的存储引擎
    alter table test1 engine = INNODB

  • 查看是否开启日志
    show variables like 'log_bin';

  • 查看日志状态
    show master status;


感谢您的耐心阅读,如果您发现文章中有一些没表述清楚的,或者是不对的地方,请给我留言,您的鼓励是作者写作最大的动力,
如果您认为本文质量不错,读后觉得收获很大,不妨请我喝杯咖啡,让我更有动力继续写出高质量的文章。

  • 支付宝

  • 微信

作 者 : @mousycoder

原文出处 : http://mousycoder.com/2016/02/15/explain-transaction-in-simple-language-2/

创作时间:2016-2-15

更新时间:2016-2-15


mousycoder
5.4k 声望819 粉丝

引用和评论

0 条评论