侧边栏壁纸
  • 累计撰写 39 篇文章
  • 累计创建 1 个标签
  • 累计收到 3 条评论
标签搜索

【深入浅出-事务】(2):Java的事务

mousycoder
2016-02-15 / 0 评论 / 0 点赞 / 65 阅读 / 12,167 字
温馨提示:
本文最后更新于 2022-01-20,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

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;
0

评论区