JDBC事务锁基础教程

　　通过本教程学习JDBC基础知识以及事务机制和死锁，假设你已经学会基础数据库连接。

　　所谓事务transaction也就是一系列操作如同一个操作一样，要么这一系列操作全部成功，要么全部失败，不能存在有部分成功或部分不成功的情况，数据库通过ACID提供这种事务机制。参考：什么是数据库ACID

事务操作

　　当我们获得一个数据库连接时，其默认是处于自动提交autocommit模式，也就是意味着你发往数据库的每条语句将会立即被自动执行，这种执行有自己的事务（ACID），每条语句都彼此隔离独立的。

　　为了体验数据库的这种事务，我们不使用缺省的自动提交模式，当我们启动一个事务时，调用数据库连接的setAutocommit(false)方法，就如同我们以connection.startTx()启动事务一样：

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 TransactionExercise {

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

    @Test
    public void transaction_exercise()  {
        try (Connection connection = getConnection()) {
            // this is the ONLY way you start a transaction in Java
            // with plain JDBC.
        System.out.println("Opening up a jdbc transaction...");
        connection.setAutoCommit(false);

            // 下面三个语句发给数据库了，但是没有被
        // 确认提交commmited, 也就是该语句操作结果还不能被其他用户或数据库连接看见。
        connection.createStatement().execute("insert into items" +
            " (name) values ('Windows 10 Premium Edition')");
        connection.createStatement().execute("insert into bids " +
            "" +
            "(user, time, amount, currency) values ('Hans', now()," +
            " 1" +", 'EUR')");
        connection.createStatement().execute("insert into bids (user, "
            +"time, amount, currency) values ('Franz',now() , 2," +
            " 'EUR')");

            // 下面是确认提交你的事务
        connection.commit();

         System.out.println("Commit worked! Now everything is in the " +
            "database");

        } catch (SQLException e) {
            e.printStackTrace();
        }
}


    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)");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

 

ROLLBACK回滚

　　当我们将语句发送给数据库时，我们突然不希望数据库执行这些语句，怎么办？我们可以使用回滚，使用

 connection.rollback();

替代上面的： 

 connection.commit();

 

多用户与死锁

　　Web应用情况下一般可能同时有多个用户操作数据库同一条记录，你会有多个数据库连接。多个数据库连接会导致堵塞或死锁的情况发生，当一个用户A执行将数据放入数据表中，而另外一个用户B也许会堵塞等待用户A的更新操作完成，下面我们来模拟这种情况：

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 DeadlocksInsert1Exercise {

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

 

    @Test
　　public void deadlock_insert_exercise_part() throws SQLException {
　　　　System.out.println("Do we reach the end of the test without a " +
　　　　　　　　　　　　　"deadlock?...");
　　　　try (Connection connectionFromJackBauer = getConnection()) {
　　　　　　connectionFromJackBauer.setAutoCommit(false);
　　　　　　connectionFromJackBauer.createStatement().execute(
　　　　　　　　"insert into items " +
　　　　　　　　"(name) values ('CTU Field Agent Report')");

　　            try (Connection connectionFromHabibMarwan = getConnection()) {
　　　　　　　　connectionFromHabibMarwan.setAutoCommit(false);
　　　　　　　　connectionFromHabibMarwan.createStatement().execute(
　　　　　　　　　　　　"insert into items " +
　　　　　　　　　　　　"(name) values ('CTU Field Agent Report')");
　　　　　　}

　　        }

　　　　//上面两个操作语句是插入name字符串是一样相同的记录
　　　　//但是items数据表的name是唯一 unique的，不能重复
　　　　//死锁在这里，下面语句无法执行。
　　　　System.out.println("Yes!");
　　}

 

    private void createTables(Connection conn) {
　　try {
　　　　conn.createStatement().execute("create table bids (id " +
　　　　　　"identity, user VARCHAR, time TIMESTAMP ," +
　　　　　　" amount NUMBER, currency VARCHAR) ");

　　　　//这里我们将数据表的items的name作为唯一字符串，不能重复
　　　　conn.createStatement().execute("create table items (id " +
　　　　　　"identity, name VARCHAR unique )");
　　} catch (SQLException e) {
　　　　e.printStackTrace();
　　}
　　}

 

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

}

上述代码我们是模拟两个用户同时操作，并不是真正打开两个数据库连接，而是打开一个数据库连接，然后开始一个事务，然后在关闭这个连接之前再打开一个连接事务，通过这种方式模拟两个用户两个事务操作同一个数据表的情况。

上面代码会抛出JdbcSQLException 错误，因为 H2 数据库发现了一个死锁，并抛出这个exception。

如果上面这种嵌套打开数据库连接不能真实模拟用户并发操作数据库的情况，我们使用两个线程来模拟并发操作，第一个连接等待1300ms提交，第二个连接至少堵塞等待1300ms。

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.assertTrue;

 

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

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

    private static final Long WAIT_BEFORE_COMMIT_MS = 1300l;
   private static final Long ORDERING_SLEEP = 150l;

    @Test
   public void deadlock_insert_exercise_part3() throws Exception {
      System.out.println("Do we reach the end of the test without a " +
         "deadlock?...");

        Thread i1 = new Thread(new Inserter("Jack Bauer",WAIT_BEFORE_COMMIT_MS));

        Thread i2 = new Thread(new Inserter("Habib Marwan"));

        i1.start();
         // dirty way to make sure i2 _really_ starts after i1
      Thread.sleep(ORDERING_SLEEP);
      i2.start();

      i1.join();
      i2.join();

      System.out.println("Yes!");
}

 

    public class Inserter implements Runnable {

        private String name;
         private Long waitBeforeCommit;

 

        public Inserter(String name) {
            this.name = name;
         }

        public Inserter(String name, Long waitBeforeCommit) {
            this.waitBeforeCommit = waitBeforeCommit;
            this.name = name;
         }

        @Override
      public void run() {
            long start = System.nanoTime();

            try (Connection connection = getConnection()) {
               connection.setAutoCommit(false);
               connection.createStatement().execute(
                     "insert into items " +
                     "(name) values ('CTU Field Agent Report')");

                if (waitBeforeCommit != null) {
                  // 在提交前等待1300ms
                  Thread.sleep(WAIT_BEFORE_COMMIT_MS);
            }
            connection.commit();
   } catch (Exception e) {
         if (e instanceof SQLException) {
               String errorCode = ((SQLException) e).getSQLState();
               System.err.println("Got error code " + errorCode + " " +
                        "when trying to insert a row into the items " +
                        "tablew");
         } else {
               e.printStackTrace();
      }
   } finally {
         long end = System.nanoTime();
         long durationMs = (end - start) / 1000000;
         System.out.println("User[= " + name + "]. The whole " +
               "getTransactionalConnection/insertion" +
               " " +
               "process took: " +
                  durationMs + " ms");
            assertTrue(durationMs > WAIT_BEFORE_COMMIT_MS - ORDERING_SLEEP);
   }
   }
}

 

    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 unique)");
      } catch (SQLException e) {
            e.printStackTrace();
      }
}

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

 

}

上述代码执行结果：

jdbc

As you can see in the console window, both connections tried to insert something in the database. Jack Bauer 成功插入了数据到数据库，整个查询循环花费了1300ms. 而Habib Marwan 失败了，他被堵塞等待超过了1秒，而此时Jack Bauer并没有确认提交commit. 只有Jack确认提交以后, Habib 才会得到一个错误23505的信息。

下一步我们看看数据库JDBC的悲观锁与乐观锁。

 

什么是数据库ACID

JDBC基础教程

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

JDBC隔离级别基础教程

SQL入门教程

数据库事务专题

Hibernate专题