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 {

 

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

 

    @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基础教程

JDBC事务锁基础教程

JDBC隔离级别基础教程

SQL入门教程

数据库事务专题

Hibernate专题

 

猜你喜欢