JDBC悲观锁与乐观锁基础教程

　　通过本教程学习JDBC基础知识以及乐观锁与悲观锁机制，在上篇数据库事务与死锁中，我们通过并发模拟两个用户插入相同键值的记录发生死锁的场景，数据库的锁使用分为悲观锁与乐观锁。

悲观锁

　　当我们使用悲观锁更新或删除一行数据表记录时，数据库会锁住整个一行，排他性地用于读操作和谐操作，这能够确保这一行记录不会同时被其它用户锁住。

　　下面是悲观锁实现代码：

import org.h2.jdbc.JdbcSQLException;
import org.junit.Before;
import org.junit.Test;

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

import static org.junit.Assert.fail;

/**
* @author Marco Behler
*/
public class PessimisticLockingExercise {

    @Before
    public void setUp() {
        try (Connection connection = getConnection()) {
            createTables(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test(expected = JdbcSQLException.class)
    public void pessimistic_locking_exercise() throws SQLException {
        // 首先, jack bauer 插入一行记录
        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            connectionFromJackBauer.createStatement().execute(
                "insert into items " +
                "(name, release_date) values " +
                "('CTU Field Agent Report'" +
                ", current_date() - 100)");
            connectionFromJackBauer.commit();
        }

        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            //然后jack要修改这条记录，确保同时没有其他人访问
            connectionFromJackBauer.createStatement()
                .execute("select * from items where " +
                "name = 'CTU Field Agent Report' for update");
                    // TODO update the row, etc.
            System.out.println("Jack Bauer locked the row for any " +
                "other update");

            // 然后 habib表示试图更新这行记录，但是不成功，泡Exception
        try (Connection connectionFromHabibMarwan = getConnection()) {
            connectionFromHabibMarwan.setAutoCommit(false);
            connectionFromHabibMarwan.createStatement()
                .executeUpdate(
                    "update items set " +
                    "release_date = current_date() + 10" +
                    " where name = " +
                            "'CTU Field Agent Report'");
            fail("We should never be able to get to this line, " +
                    "because an exception is thrown");
        }
    }
}

    // simply what we did in OpenConnectionExerciseJava6/7.java
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:h2:mem:exercise_db;" +
            v"DB_CLOSE_DELAY=-1");
}

    private void createTables(Connection conn) {
        try {
            conn.createStatement().execute("create table bids (id " +
                "identity, user VARCHAR, time TIMESTAMP ," +
                " amount NUMBER, currency VARCHAR) ");
            conn.createStatement().execute("create table items (id " +
                "identity, release_date date, name VARCHAR)");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

　　Jack插入记录后，再修改它，他使用了"select..for update" SQL语句来排他性锁住了整个记录，直到他提交了语句也就是释放了这段事务的锁，其他用户才能读取这行曾经被锁住过的记录。在锁住期间，任何其他用户读取都无法成功，抛出Exception错误，这就是悲观锁。

乐观锁

　　乐观锁这样定义，假设有很少概率出现同时两个用户更新同样的记录，在这种情况下如果万一发生，提供一种健壮的检测方式。你可加入一个额外的列如版本号 "version"到数据表结构中，每次update-sql 语句执行时，附加条件"and where version = X" 限制. 此外，每次你更新一行记录，你需要逐个增加版本号，以表明这行记录已经更新了。

　　这里有一小技巧：JDBC驱动包会在你使用update语句时返回你真正进行update了多少行，但是如果有其他人在我之前更新了同样的记录呢？看看代码：

import org.junit.Before;
import org.junit.Test;

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

/**
* @author Marco Behler
*/
public class OptimisticLockingExercise {

    @Test(expected = OptimisticLockingException.class)
    public void optimistic_locking_exercise() throws SQLException {
        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            connectionFromJackBauer.createStatement().execute(
                "insert into items " +
                "(name, release_date, version) values " +
                "('CTU Field Agent " +
                "Report', current_date() - 100, 0)");

            // 上面刚刚插入了错误的release_date. 让我们快速在更新一下
        //release_date ，并增加版本号version number
        int updatedRows = connectionFromJackBauer.createStatement()
                                            .executeUpdate(
                                            "update items set release_date = current_date(), " +
                                            " version = version + 1 " +
                                            "where name = 'CTU Field Agent Report'" +
                                            " and version = 0");
        System.out.println("Rows updated by Jack Bauer: " +
                                        updatedRows);
        connectionFromJackBauer.commit();
    }

        // 同时, habib marwin试图设置release_date为今天+10，但是他试图使用版本为0
       //的记录进行这种更新
       try (Connection connectionFromHabibMarwan = getConnection()) {
              connectionFromHabibMarwan.setAutoCommit(false);
              int updatedRows = connectionFromHabibMarwan.createStatement()
                                                 .executeUpdate(
                                                 "update items set release_date = current_date() + 10," +
                                                 " version = version + 1" +
                                                 "where name = 'CTU Field Agent Report'" +
                                                 " and version = 0");
              System.out.println("Rows updated by Habib Marwan: " +
                            updatedRows);

       //这一行是很多Java框架进行乐观锁检测的方法，检测真正更新的记录数。
       if (updatedRows == 0) throw new OptimisticLockingException();
}
}

    // self made. check out the one from hibernate or spring
    public static class OptimisticLockingException extends
        RuntimeException {}

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:h2:mem:exercise_db;" +
            "DB_CLOSE_DELAY=-1");
    }

    private void createTables(Connection conn) {
    try {
        conn.createStatement().execute("create table bids (id " +
            "identity, user VARCHAR, time TIMESTAMP ," +
            " amount NUMBER, currency VARCHAR) ");
        conn.createStatement().execute("create table items (id " +
            "identity, name VARCHAR, release_date date," +
            " version NUMBER default " +
            "0)");
    } catch (SQLException e) {
        e.printStackTrace();
    }
    }
}

运行结果如下：

Jack更新成功了一条记录，而 Habib Marwan使用错误的版本后进行了更新，因此无法成功。

下一页讨论 JDBC隔离级别基础教程

JDBC悲观锁与乐观锁基础教程

悲观锁

乐观锁

Hibernate专题