YashanDB Docs
search-icon Ctrl K
官网首页 立即体验 文档中心 search-icon Language

#处理事务

事务是数据库操作的基本单元，确保一组操作要么全部成功，要么全部失败。

# 事务基础概念

事务遵循ACID特性，保证数据的一致性和可靠性。

  • 原子性：事务中的所有操作要么全部完成，要么全部不完成。

  • 一致性：事务完成后数据库处于一致状态。

  • 隔离性：并发执行的事务互不干扰。

  • 持久性：事务提交后，对数据库的修改永久保存。

JDBC连接默认启用自动提交，每条SQL语句执行后都会单独提交。需要将多条SQL语句作为一个事务执行时，调用setAutoCommit(false)关闭自动提交，并根据执行结果调用commit()或rollback()。

# 事务提交方法

以下示例在同一事务中完成两个账户的余额变更。两条更新都执行成功时提交事务；任意操作失败时回滚整个事务。建表和初始化数据在事务开始前完成，示例结束后删除测试表。

package quickstart;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class BasicTransactionExample {
    private static final String URL =
            "jdbc:yasdb://192.168.1.2:1688/yasdb";
    private static final String TABLE = "jdbc_transaction_account";

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.setProperty("user", "username");
        properties.setProperty("password", "passwd");

        Class.forName("com.yashandb.jdbc.Driver");
        prepareTable(properties);

        try (Connection connection =
                     DriverManager.getConnection(URL, properties)) {
            // 关闭自动提交，由应用程序控制事务边界。
            connection.setAutoCommit(false);
            try {
                updateBalance(
                        connection, 1, new BigDecimal("-100.00"));
                updateBalance(
                        connection, 2, new BigDecimal("100.00"));

                // 两条更新均成功后提交事务。
                connection.commit();
                System.out.println("Transaction committed");
            } catch (Exception exception) {
                // 任意操作失败时回滚整个事务。
                connection.rollback();
                throw exception;
            }

            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(
                         "SELECT account_id, balance FROM " + TABLE
                                 + " ORDER BY account_id")) {
                while (resultSet.next()) {
                    System.out.println(
                            "Account " + resultSet.getInt(1)
                                    + " balance: "
                                    + resultSet.getBigDecimal(2));
                }
            }
        } finally {
            dropTable(properties);
        }
    }

    private static void updateBalance(
            Connection connection, int accountId, BigDecimal amount)
            throws Exception {
        String sql = "UPDATE " + TABLE
                + " SET balance = balance + ? WHERE account_id = ?";
        try (PreparedStatement statement =
                     connection.prepareStatement(sql)) {
            statement.setBigDecimal(1, amount);
            statement.setInt(2, accountId);
            statement.executeUpdate();
        }
    }

    private static void prepareTable(Properties properties)
            throws Exception {
        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
            statement.execute(
                    "CREATE TABLE " + TABLE
                            + " (account_id INTEGER PRIMARY KEY, "
                            + "balance DECIMAL(10, 2))");
            statement.executeUpdate(
                    "INSERT INTO " + TABLE + " VALUES (1, 1000.00)");
            statement.executeUpdate(
                    "INSERT INTO " + TABLE + " VALUES (2, 1000.00)");
        }
    }

    private static void dropTable(Properties properties)
            throws Exception {
        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
        }
    }
}

正常情况下，程序输出如下：

Transaction committed
Account 1 balance: 900
Account 2 balance: 1100

# 事务隔离级别

JDBC定义了五种事务隔离级别，用于控制并发事务之间的数据可见性。YashanDB支持READ_COMMITTED和SERIALIZABLE隔离级别。

在事务开始前，使用setTransactionIsolation()设置隔离级别，使用getTransactionIsolation()获取当前隔离级别。

以下示例依次设置并读取YashanDB支持的两种隔离级别，用于确认连接上的隔离级别配置已经生效。

package quickstart;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class IsolationLevelsExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:yasdb://192.168.1.2:1688/yasdb";
        Properties properties = new Properties();
        properties.setProperty("user", "username");
        properties.setProperty("password", "passwd");

        Class.forName("com.yashandb.jdbc.Driver");

        try (Connection connection =
                     DriverManager.getConnection(url, properties)) {
            connection.setTransactionIsolation(
                    Connection.TRANSACTION_READ_COMMITTED);
            System.out.println(
                    "Current isolation level: "
                            + isolationLevelName(
                                    connection.getTransactionIsolation()));

            connection.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            System.out.println(
                    "Current isolation level: "
                            + isolationLevelName(
                                    connection.getTransactionIsolation()));
        }
    }

    private static String isolationLevelName(int level) {
        if (level == Connection.TRANSACTION_READ_COMMITTED) {
            return "READ_COMMITTED";
        }
        if (level == Connection.TRANSACTION_SERIALIZABLE) {
            return "SERIALIZABLE";
        }
        return "UNKNOWN(" + level + ")";
    }
}

正常情况下，程序输出如下：

Current isolation level: READ_COMMITTED
Current isolation level: SERIALIZABLE

# 保存点

保存点用于在事务中标记中间位置。调用rollback(Savepoint)只撤销该保存点之后的操作，保存点之前的操作仍可以继续提交。保存点只能在关闭自动提交后使用。

以下示例先将余额减少100并创建保存点，再将余额减少200。回滚到保存点后，第二次更新被撤销，第一次更新仍然保留并最终提交。

package quickstart;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Properties;

public class SavepointExample {
    private static final String URL =
            "jdbc:yasdb://192.168.1.2:1688/yasdb";
    private static final String TABLE = "jdbc_transaction_savepoint";

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.setProperty("user", "username");
        properties.setProperty("password", "passwd");

        Class.forName("com.yashandb.jdbc.Driver");
        prepareTable(properties);

        try (Connection connection =
                     DriverManager.getConnection(URL, properties)) {
            connection.setAutoCommit(false);
            try (Statement statement = connection.createStatement()) {
                statement.executeUpdate(
                        "UPDATE " + TABLE
                                + " SET balance = balance - 100 "
                                + "WHERE account_id = 1");
                Savepoint savepoint =
                        connection.setSavepoint("BEFORE_SECOND_UPDATE");

                statement.executeUpdate(
                        "UPDATE " + TABLE
                                + " SET balance = balance - 200 "
                                + "WHERE account_id = 1");
                System.out.println(
                        "Balance before rollback: "
                                + queryBalance(connection));

                // 只撤销保存点之后的第二次更新。
                connection.rollback(savepoint);
                System.out.println(
                        "Balance after rollback: "
                                + queryBalance(connection));
                connection.commit();
            } catch (Exception exception) {
                connection.rollback();
                throw exception;
            }
        } finally {
            dropTable(properties);
        }
    }

    private static BigDecimal queryBalance(Connection connection)
            throws Exception {
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(
                     "SELECT balance FROM " + TABLE
                             + " WHERE account_id = 1")) {
            resultSet.next();
            return resultSet.getBigDecimal(1);
        }
    }

    private static void prepareTable(Properties properties)
            throws Exception {
        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
            statement.execute(
                    "CREATE TABLE " + TABLE
                            + " (account_id INTEGER PRIMARY KEY, "
                            + "balance DECIMAL(10, 2))");
            statement.executeUpdate(
                    "INSERT INTO " + TABLE + " VALUES (1, 1000.00)");
        }
    }

    private static void dropTable(Properties properties)
            throws Exception {
        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
        }
    }
}

正常情况下，程序输出如下：

Balance before rollback: 700
Balance after rollback: 900

# 分布式事务

YashanDB JDBC通过YasXADataSource提供XA分布式事务支持。应用程序从XAConnection获取XAResource，并通过start()、end()、prepare()和commit()控制XA事务分支。

以下示例创建一个XA事务分支，在分支中修改账户余额，然后执行准备和提交。执行示例的数据库需要启用XA事务能力。由于XAConnection未实现AutoCloseable，示例在finally代码块中显式关闭该对象。

package quickstart;

import com.yashandb.xa.YasXADataSource;
import com.yashandb.xa.YasXid;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.XAConnection;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;

public class XATransactionExample {
    private static final String URL =
            "jdbc:yasdb://192.168.1.2:1688/yasdb";
    private static final String TABLE = "jdbc_transaction_xa";

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.setProperty("user", "username");
        properties.setProperty("password", "passwd");

        Class.forName("com.yashandb.jdbc.Driver");
        prepareTable(properties);

        YasXADataSource dataSource = new YasXADataSource();
        dataSource.setURL(URL);
        dataSource.setUser(properties.getProperty("user"));
        dataSource.setPassword(properties.getProperty("password"));

        XAConnection xaConnection = dataSource.getXAConnection();
        try {
            try (Connection connection =
                         xaConnection.getConnection()) {
                XAResource xaResource =
                        xaConnection.getXAResource();
                Xid xid = new YasXid(
                        1,
                        "transaction-1".getBytes(
                                StandardCharsets.US_ASCII),
                        "branch-1".getBytes(
                                StandardCharsets.US_ASCII));

                // XA分支中不能使用本地自动提交。
                connection.setAutoCommit(false);
                xaResource.start(xid, XAResource.TMNOFLAGS);
                try (Statement statement =
                             connection.createStatement()) {
                    statement.executeUpdate(
                            "UPDATE " + TABLE
                                    + " SET balance = balance - 100 "
                                    + "WHERE account_id = 1");
                }
                xaResource.end(xid, XAResource.TMSUCCESS);

                int result = xaResource.prepare(xid);
                if (result == XAResource.XA_OK) {
                    xaResource.commit(xid, false);
                    System.out.println("XA transaction committed");
                }
            }
        } finally {
            xaConnection.close();
        }

        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(
                     "SELECT balance FROM " + TABLE
                             + " WHERE account_id = 1")) {
            resultSet.next();
            System.out.println(
                    "Account balance: "
                            + resultSet.getBigDecimal(1));
        } finally {
            dropTable(properties);
        }
    }

    private static void prepareTable(Properties properties)
            throws Exception {
        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
            statement.execute(
                    "CREATE TABLE " + TABLE
                            + " (account_id INTEGER PRIMARY KEY, "
                            + "balance DECIMAL(10, 2))");
            statement.executeUpdate(
                    "INSERT INTO " + TABLE + " VALUES (1, 1000.00)");
        }
    }

    private static void dropTable(Properties properties)
            throws Exception {
        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
        }
    }
}

正常情况下，程序输出如下：

XA transaction committed
Account balance: 900
pdf-btn 下载文档 copy-btn 复制链接
edit-icon
反馈
coperate-icon
coperate
合作
communication-icon
communicate
交流