-- 創(chuàng)建 products 表
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  quantity INT,
  version INT
);

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

public class OptimisticLockingExample {
    public static void main(String[] args) {
        // 數(shù)據(jù)庫連接信息
        String url = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            // 開啟事務(wù)
            connection.setAutoCommit(false);

            // 查詢商品信息并獲取當(dāng)前版本號
            String selectSql = "SELECT quantity, version FROM products WHERE id =?";
            try (PreparedStatement selectStmt = connection.prepareStatement(selectSql)) {
                selectStmt.setInt(1, 1); // 假設(shè)要更新 id 為 1 的商品
                try (ResultSet resultSet = selectStmt.executeQuery()) {
                    if (resultSet.next()) {
                        int currentQuantity = resultSet.getInt("quantity");
                        int currentVersion = resultSet.getInt("version");

                        // 模擬業(yè)務(wù)邏輯，減少商品數(shù)量
                        int newQuantity = currentQuantity - 1;

                        // 更新商品數(shù)量和版本號，使用樂觀鎖機制
                        String updateSql = "UPDATE products SET quantity =?, version = version + 1 WHERE id =? AND version =?";
                        try (PreparedStatement updateStmt = connection.prepareStatement(updateSql)) {
                            updateStmt.setInt(1, newQuantity);
                            updateStmt.setInt(2, 1); // 商品 id
                            updateStmt.setInt(3, currentVersion);
                            int rowsUpdated = updateStmt.executeUpdate();
                            if (rowsUpdated == 0) {
                                // 說明在更新之前數(shù)據(jù)被其他事務(wù)修改，樂觀鎖沖突
                                System.out.println("樂觀鎖沖突，更新失敗");
                                connection.rollback();
                            } else {
                                // 提交事務(wù)
                                connection.commit();
                                System.out.println("商品數(shù)量更新成功");
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

-- 開啟事務(wù)
START TRANSACTION;

-- 使用悲觀鎖查詢商品信息并鎖定行
SELECT quantity FROM products WHERE id = 1 FOR UPDATE;

-- 模擬業(yè)務(wù)邏輯，減少商品數(shù)量
UPDATE products SET quantity = quantity - 1 WHERE id = 1;

-- 提交事務(wù)
COMMIT;