001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.jdbc;
007:
008: import java.sql.Connection;
009: import java.sql.SQLException;
010:
011: import org.h2.test.TestBase;
012:
013: /**
014: * Transaction isolation level tests.
015: */
016: public class TestTransactionIsolation extends TestBase {
017:
018: Connection conn1, conn2;
019:
020: public void test() throws Exception {
021: if (config.mvcc) {
022: // no tests yet
023: } else {
024: testTableLevelLocking();
025: }
026: }
027:
028: void testTableLevelLocking() throws Exception {
029: deleteDb("transactionIsolation");
030: conn1 = getConnection("transactionIsolation");
031: check(conn1.getTransactionIsolation(),
032: Connection.TRANSACTION_READ_COMMITTED);
033: conn1
034: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
035: check(conn1.getTransactionIsolation(),
036: Connection.TRANSACTION_SERIALIZABLE);
037: conn1
038: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
039: check(conn1.getTransactionIsolation(),
040: Connection.TRANSACTION_READ_UNCOMMITTED);
041: checkSingleValue(conn1.createStatement(), "CALL LOCK_MODE()", 0);
042: conn1
043: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
044: checkSingleValue(conn1.createStatement(), "CALL LOCK_MODE()", 3);
045: check(conn1.getTransactionIsolation(),
046: Connection.TRANSACTION_READ_COMMITTED);
047: conn1.createStatement().execute("SET LOCK_MODE 1");
048: check(conn1.getTransactionIsolation(),
049: Connection.TRANSACTION_SERIALIZABLE);
050: conn1.createStatement().execute("CREATE TABLE TEST(ID INT)");
051: conn1.createStatement().execute("INSERT INTO TEST VALUES(1)");
052: conn1.setAutoCommit(false);
053:
054: conn2 = getConnection("transactionIsolation");
055: conn2.setAutoCommit(false);
056:
057: conn1
058: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
059:
060: // serializable: just reading
061: checkSingleValue(conn1.createStatement(), "SELECT * FROM TEST",
062: 1);
063: checkSingleValue(conn2.createStatement(), "SELECT * FROM TEST",
064: 1);
065: conn1.commit();
066: conn2.commit();
067:
068: // serializable: write lock
069: conn1.createStatement().executeUpdate("UPDATE TEST SET ID=2");
070: try {
071: checkSingleValue(conn2.createStatement(),
072: "SELECT * FROM TEST", 1);
073: error("Expected lock timeout");
074: } catch (SQLException e) {
075: checkNotGeneralException(e);
076: }
077: conn1.commit();
078: conn2.commit();
079:
080: conn1
081: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
082:
083: // read-committed: #1 read, #2 update, #1 read again
084: checkSingleValue(conn1.createStatement(), "SELECT * FROM TEST",
085: 2);
086: conn2.createStatement().executeUpdate("UPDATE TEST SET ID=3");
087: conn2.commit();
088: checkSingleValue(conn1.createStatement(), "SELECT * FROM TEST",
089: 3);
090: conn1.commit();
091:
092: // read-committed: #1 read, #2 read, #2 update, #1 delete
093: checkSingleValue(conn1.createStatement(), "SELECT * FROM TEST",
094: 3);
095: checkSingleValue(conn2.createStatement(), "SELECT * FROM TEST",
096: 3);
097: conn2.createStatement().executeUpdate("UPDATE TEST SET ID=4");
098: try {
099: conn1.createStatement().executeUpdate("DELETE FROM TEST");
100: error("Expected lock timeout");
101: } catch (SQLException e) {
102: checkNotGeneralException(e);
103: }
104: conn2.commit();
105: conn1.commit();
106: checkSingleValue(conn1.createStatement(), "SELECT * FROM TEST",
107: 4);
108: checkSingleValue(conn2.createStatement(), "SELECT * FROM TEST",
109: 4);
110:
111: conn1.close();
112: conn2.close();
113: }
114:
115: }
|