Hello, please, how exactly does the transaction isolation work when using MVCC? I.e. how to ensure that two update transactions are serialized, if possible, or one of them is rolled back (if they are not serializable)?
As an example, let us take a table with three columns: ID, A, B and one row (1, "A0", "B0"). I have two threads. The first is modifying attribute A (successively changing it to A1, A2, ...), second does the same to attribute B (changing it to B1, B2, ...). However, each of the threads reads *both* attributes and writes new value of "its" attribute and old value of the other. (This is an abstraction of a functioning of a bigger product that does the same to objects with tens of attributes.) When using MVCC=FALSE, LOCK_MODE=1, a deadlock is correctly detected. However, when using MVCC=TRUE, no deadlocks are seen and the later commit overwrites values stored by the preceding one. (I would expect that one of the threads would be disallowed to commit, because such transactions are not serializable.) The situation looks like this: [Modifier for first attribute] --- Iteration number 1 for Modifier for first attribute --- [Modifier for second attribute] --- Iteration number 1 for Modifier for second attribute --- [Modifier for second attribute] Read: attribute A = A0, attribute B = B0 [my value = B0] [Modifier for first attribute] Read: attribute A = A0, attribute B = B0 [my value = A0] [Modifier for second attribute] UPDATE TEST SET A = 'A0', B = 'B1' WHERE ID = 1 [my value = B1] [Modifier for first attribute] UPDATE TEST SET A = 'A1', B = 'B0' WHERE ID = 1 [my value = A1] [Modifier for second attribute] Committed (iteration number 1) [Modifier for second attribute] --- Iteration number 2 for Modifier for second attribute --- [Modifier for second attribute] Read: attribute A = A1, attribute B = B0 [my value = B0] [Modifier for second attribute] Got exception: java.lang.RuntimeException: Got B0, expected B1 (This is my exception signalling that the value of "B" attribute was not as expected. The new value (B1) was overwritten by the original value (B0) by the first thread.) Full source as well as the log is in the attachment. Perhaps I'm missing some switch to enable row-level locking? Or some stupid mistake? Thank you very much. Pavol Mederly -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/o6-_8exnUNEJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
package test;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestMVCC {
private static final long WAIT_TIME = 15000;
private static final long WAIT_STEP = 500;
public static void main(String[] args) throws Exception {
TestMVCC test = new TestMVCC();
test.execute();
}
public Connection connection;
public void execute() throws Exception {
String dbDriver = "org.h2.Driver";
String dbUrl = "jdbc:h2:mem:;MVCC=TRUE";
String dbUser = "sa";
String dbPassword = "";
Class.forName(dbDriver);
connection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
connection.setAutoCommit(false);
log("autocommit = " + connection.getAutoCommit());
DatabaseMetaData dbmd = connection.getMetaData();
log("DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() );
log("DriverVersion: " + dbmd.getDriverVersion() );
connection.createStatement().execute("CREATE TABLE TEST(ID INT PRIMARY KEY, A VARCHAR(255), B VARCHAR(255));");
connection.createStatement().execute("INSERT INTO TEST VALUES (1, 'A0', 'B0');");
ModifierThread mt1 = new ModifierThread(1, true);
ModifierThread mt2 = new ModifierThread(1, false);
log("Starting modifier threads");
mt1.start();
mt2.start();
log("Waiting " + WAIT_TIME + " ms\n");
for (long time = 0; time < WAIT_TIME; time += WAIT_STEP) {
Thread.sleep(WAIT_STEP);
if (!mt1.isAlive() || !mt2.isAlive()) {
log("At least one of threads died prematurely, finishing waiting.");
break;
}
}
mt1.stop = true; // stop the threads
mt2.stop = true;
// we do not have to wait for the threads to be stopped, just examine their results
log("*** Thread 1 has done " + (mt1.counter-1) + " iterations, thread 2 has done " + (mt2.counter-1) + " iterations. ***");
Thread.sleep(1000); // give the threads a chance to finish (before repo will be shut down)
if (mt1.threadResult != null) {
log("Modifier thread 1 finished with an exception: " + mt1.threadResult);
mt1.threadResult.printStackTrace();
}
if (mt2.threadResult != null) {
log("Modifier thread 2 finished with an exception: " + mt2.threadResult);
mt2.threadResult.printStackTrace();
}
}
void log(String s) {
System.out.println("[" + Thread.currentThread().getName() + "] " + s);
}
class ModifierThread extends Thread {
int id; // object to modify
boolean first; // attribute to modify (first or second)
volatile Exception threadResult;
volatile int counter = 1;
ModifierThread(int id, boolean first) {
this.id = id;
this.first = first;
this.setName("Modifier for " + (first?"first":"second") + " attribute");
}
public volatile boolean stop = false;
@Override
public void run() {
try {
run1();
} catch (Exception e) {
threadResult = e;
log("Got exception: " + e);
}
}
public void run1() throws Exception {
String dataRead, dataWritten = null;
while (!stop) {
log(" --- Iteration number " + counter + " for " + Thread.currentThread().getName() + " ---");
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM TEST WHERE ID = " + id);
if (!rs.next()) {
throw new Exception("No data could be read.");
}
String a1 = rs.getString("A");
String a2 = rs.getString("B");
rs.close();
statement.close();
if (first)
dataRead = a1;
else
dataRead = a2;
log("Read: attribute A = " + a1 + ", attribute B = " + a2 + " [my value = " + dataRead + "]");
if (dataWritten != null) {
if (!dataRead.equals(dataWritten)) {
threadResult = new RuntimeException("Got " + dataRead + ", expected " + dataWritten);
throw threadResult;
}
}
try {
Thread.sleep(100);
} catch (InterruptedException e) {
}
if (first) {
dataWritten = "A" + counter;
a1 = dataWritten;
}
else {
dataWritten = "B" + counter;
a2 = dataWritten;
}
Statement statement2 = connection.createStatement();
String update = "UPDATE TEST SET A = '"+a1+"', B = '"+a2+"' WHERE ID = " + id;
statement2.executeUpdate(update);
log(update + " [my value = " + dataWritten + "]");
statement2.close();
connection.commit();
log("Committed (iteration number " + counter + ")");
counter++;
}
}
}
}
mvcc.log
Description: Binary data
