java - 有条件更新交易中的值

Question

我的表 items 中有一个 totalAmount 和 purchasedItems 项目列。我想自动更新 purchasedItems 如果:totalAmount >=purchasedItems + 1，否则我想抛出一个错误。我尝试做类似的事情但失败了。我怎样才能以原子方式实现这一点(我正在使用 java jdbc 来实现这一点)？

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
    DECLARE @purchased AS INT
    DECLARE @total AS INT
    SELECT @purchased = SELECT (purchasedItems + 1) FROM events WHERE id=1
    SELECT @total = SELECT totalAmount FROM items WHERE id=1
    IF @purchased > @total
    BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Max value passed'
    END 
    # ELSE DO UPDATE 
    SELECT @remaining
COMMIT TRANSACTION

永远不会抛出错误消息

Answer 1

If I start a transaction and do a get to check my condition passes and then update the table, another process cant update in between the get and update, right?

这是正确的，但即使使用可串行化事务隔离，如果多个进程尝试使用“检查然后更新并提交”策略，您仍然可能会遇到死锁。考虑一个简化的示例，其中代码只想将 purchasedItems 增加到最大值 10:

try (Connection conn = DriverManager.getConnection(connectionUrl, myUid, myPwd)) {
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    final int maxPurchasedItems = 10;
    Statement st = conn.createStatement();
    System.out.println("Initial SELECT ...");
    Long t0 = System.nanoTime();
    ResultSet rs = st.executeQuery("SELECT purchasedItems FROM items WHERE id = 1");
    rs.next();
    int n = rs.getInt(1);
    System.out.printf("Original value: %d (%d ms)%n",
            n, (System.nanoTime() - t0) / 1000000);
    if (n >= maxPurchasedItems) {
        System.out.printf("Increment would exceed limit of %d. Cancelled.%n", maxPurchasedItems);
        conn.rollback();
    } else {
        Thread.sleep(5000);
        t0 = System.nanoTime();
        System.out.println("Attempting UPDATE ...");
        st.executeUpdate("UPDATE items SET purchasedItems = purchasedItems+1 WHERE id = 1");
        rs = st.executeQuery("SELECT purchasedItems FROM items WHERE id = 1");
        rs.next();
        n = rs.getInt(1);
        System.out.printf("Updated value: %d (%d ms)%n",
                n, (System.nanoTime() - t0) / 1000000);
        Thread.sleep(5000);
        conn.commit();
    }
} catch (Throwable ex) {
    ex.printStackTrace(System.err);
}

如果我们尝试在两个独立的进程下同时运行该代码，我们会看到

进程_A:

Initial SELECT ...
Original value: 6 (142 ms)
Attempting UPDATE ...
Updated value: 7 (1910 ms)

进程_B:

Initial SELECT ...
Original value: 6 (144 ms)
Attempting UPDATE ...
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

这是因为 Process_A 的初始 SELECT 在该行上放置了读锁(也称为共享锁)。这只是一个读锁，因此允许 Process_B 的初始 SELECT 继续进行。但是，它还在同一行上放置了读锁，因此两个事务在写入时会陷入死锁。 MySQL 必须选择一个事务来终止，而 Process_B 是不幸的一个。

相反，您应该使用“更新然后检查并在必要时回滚”策略:

try (Connection conn = DriverManager.getConnection(connectionUrl, myUid, myPwd)) {
    conn.setAutoCommit(false);
    final int maxPurchasedItems = 10;
    Statement st = conn.createStatement();
    System.out.println("Initial UPDATE ...");
    Long t0 = System.nanoTime();
    st.executeUpdate("UPDATE items SET purchasedItems = purchasedItems+1 WHERE id = 1");
    ResultSet rs = st.executeQuery("SELECT purchasedItems FROM items WHERE id = 1");
    rs.next();
    int n = rs.getInt(1);
    System.out.printf("Updated value: %d (%d ms)%n",
            n, (System.nanoTime() - t0) / 1000000);
    Thread.sleep(5000);
    if (n > maxPurchasedItems) {
        System.out.printf("Increment exceeds limit of %d. Rolling back.%n", maxPurchasedItems);
        conn.rollback();
    } else {
        conn.commit();
    }
} catch (Throwable ex) {
    ex.printStackTrace(System.err);
}