MVCC and read uncommitted transaction isolation
Paul Hilliar
It seems to me as if READ_UNCOMMITTED (LOCK_MODE=0) doesn't work with
MVCC
Possibly related to this: http://groups.google.com/group/h2-database/browse_thread/thread/c113b84c3c64b534
I have included a test case that demonstrates what I am talking about
(code below). The output from the test is shown at the bottom.
The short description of the problem is that on a read uncommitted DB,
a thread cannot see what the other thread has written but not
committed.
Now I don't know if this is an invalid thing to do? Perhaps someone
properly in the know could confirm.
If it isn't valid then it would be great if the DB complained on
startup that the combination of MVCC=TRUE;LOCK_MODE=0 is invalid.
Or if it is a valid thing to do then it would be great to fix at some
point.
Regards
Paul.
P.S. I needed MVCC because I wanted row level locking.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.Date;
import javax.sql.DataSource;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
public class ReadUncommittedTransactionIsolationTestHarness {
public static void main(String[] args) throws Exception{
final DataSource ds = new SimpleDriverDataSource(new
org.h2.Driver(), "jdbc:h2:mem:test_db;MVCC=TRUE;LOCK_MODE=0", "sa",
"");
ds.getConnection().createStatement().execute("SET
DEFAULT_LOCK_TIMEOUT 60000");
try {
ds.getConnection().createStatement().execute("drop table TEST");
} catch (Exception e1) {}
ds.getConnection().createStatement().execute("create table TEST
(ID integer primary key)");
Runnable r = new Runnable() {
public void run() {
try {
Connection c1 = ds.getConnection();
c1.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); //
this isn't strictly required because LOCK_MODE=0
c1.setAutoCommit(false);
String sql = "insert into TEST (ID) values (1)";
log(sql);
try {
c1.createStatement().execute(sql);
log("Inserted row");
}
catch (SQLException e) {
log("we get here because row 1 has been inserted by the other
thread. Let's try to read the value back");
String select = "select ID from TEST";
log(select);
ResultSet rs = c1.createStatement().executeQuery(select);
rs.next();
int id = rs.getInt(1); //we get an error at this line:
org.h2.jdbc.JdbcSQLException: No data is available [2000-139]
log("Got the ID written by the other thread. It was:" + id);
}
log("Sleeping before commit");
Thread.sleep(10 * 1000); //sleep for 10 seconds
log("Committing");
c1.commit();
}
catch (Exception e) {
log("Exception " + e);
e.printStackTrace();
}
}
};
new Thread(r, "T1").start();
Thread.sleep(1000);
new Thread(r, "T2").start();
Thread.sleep(1000 * 1000);
}
static void log(String str) {
System.out.println(DateFormat.getTimeInstance(DateFormat.MEDIUM).format(new
Date()) + " " +
Thread.currentThread().getName() + " " +
str);
}
}
10:28:48 T1 insert into TEST (ID) values (1)
10:28:48 T1 Inserted row
10:28:48 T1 Sleeping before commit
10:28:49 T2 insert into TEST (ID) values (1)
10:28:49 T2 we get here because row 1 has been inserted by the other
thread. Let's try to read the value back
10:28:49 T2 select ID from TEST
10:28:49 T2 Exception org.h2.jdbc.JdbcSQLException: No data is
available [2000-139]
org.h2.jdbc.JdbcSQLException: No data is available [2000-139]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.message.DbException.get(DbException.java:133)
at org.h2.jdbc.JdbcResultSet.checkOnValidRow(JdbcResultSet.java:2937)
at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:2943)
at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:299)
at ReadUncommittedTransactionIsolationTestHarness
$1.run(ReadUncommittedTransactionIsolationTestHarness.java:43)
at java.lang.Thread.run(Unknown Source)
10:28:58 T1 Committing
Thomas Mueller
For H2, read-uncommitted means no table-level locks are used. With
MVCC the meaning is a bit different than what you would expect. Why do
you need to use it?
I modified your test case a bit so it's not using Spring (test cases
should have as little dependencies as possible). With version 1.2.141
the result is different from what you got with version 1.2.139. The
reason is a bug fixed in 1.2.141:
http://www.h2database.com/html/changelog.html "MVCC: when trying to
insert two rows with the same key from two connections, the second
connection immediately threw the exception "Unique index or primary
key violation". Instead, the second connection now waits throwing the
exception until the first connection committed the change (same as
when trying to concurrently update the same row). "
http://h2database.com/p.html#75484b6aa265822f5b6f57f0d58200e7
Output:
10:16:32 PM T1 insert into TEST (ID) values (1)
10:16:32 PM T1 Inserted row
10:16:32 PM T1 Sleeping before commit
10:16:33 PM T2 insert into TEST (ID) values (1)
10:16:42 PM T1 Committing
10:16:42 PM T2 we get here because row 1 has been inserted by the
other thread. Let's try to read the value back
10:16:42 PM T2 error: org.h2.jdbc.JdbcSQLException: Unique index or
primary key violation: "PRIMARY_KEY_2 ON PUBLIC.TEST(ID)"; SQL
statement:
insert into TEST (ID) values (1) [23001-141]
10:16:42 PM T2 select ID from TEST
10:16:42 PM T2 Got the ID written by the other thread. It was:1
10:16:42 PM T2 Sleeping before commit
10:16:52 PM T2 Committing
Regards,
Thomas