Hi, I have found something suspicious testing pg73jdbc3.jar:
I have 2 connections, both using serializable transaction isolation mode
and no autocommit.
I have a table containing (name,number) records and I try to increment
number in record with given name.
On one connection, I perform select on record with for update clause.
This is supposed to lock the record with update type lock against other
concurrent updates.
Then I update that record with new number. All this should do is upgrade
update lock to exclusive one.
On the second connection, I try to do the same.
If things were as it should be, select for update in second connection
would block waiting on lock (first update then exclusive) until
transaction in first connection commits, then continue normally getting
update lock on the (new) record and eventually updating it again.
BUT - for some reason, if the other transaction tries the select for
update after first has updated the record but before it has committed
it, instead of blocking, I get following exception immediately:
java.sql.SQLException: ERROR: Can't serialize access due to concurrent
update
at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
at
cz.code.test.TestSerializable$Thread1.run(TestSerializable.java:144)
I have no idea why it happens. It should wait for the first transaction
to commit!
When i leave out the update statement from first transaction, it works
OK (i.e. second transaction blocks until commit of the first).
I have attached a test case to show this.
Can someone plese fix it soon?
Jan
/*
* TestSerializable.java
*
* Created on 22. prosinec 2002, 10:51
*/
package cz.code.test;
import java.sql.*;
/**
*
* @author Administrator
*/
public class TestSerializable {
String jdbc_url = "jdbc:postgresql://192.168.0.2:5432/web1";
String user = "web";
String pass = "web";
String table = "seq";
String name = "name";
String value = "seq";
String key = "GENID";
boolean thread1_ready = false;
Object signal = new Object();
Thread thread1 = null;
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
try {
new TestSerializable().run();
} catch (Exception e) {
e.printStackTrace();
}
}
public void run() throws SQLException {
try {
Class driver = Class.forName("org.postgresql.Driver");
thread1 = new Thread1();
System.out.println("Main: starting threads");
thread1.start();
System.out.println("Main: getting connection...");
Connection c = DriverManager.getConnection(jdbc_url,user,pass);
try {
c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
c.setAutoCommit(false);
System.out.println("Main: waiting for other thread to get ready...");
// wait for other thread to get ready
synchronized (signal) {
while (!thread1_ready) {
try {
signal.wait();
} catch (InterruptedException e) { }
}
}
System.out.println("Main: OK, performing locks...");
long v = 0;
// perform select for update, which should lock records with update locks
PreparedStatement s = c.prepareStatement("SELECT "+value+" FROM "+table+" WHERE "+name+" = ? FOR
UPDATE");
try {
s.setString(1, key);
ResultSet rs = s.executeQuery();
try {
rs.next();
v = rs.getLong(1);
} finally {
rs.close();
}
} finally {
s.close();
s = null;
}
System.out.println("Main: updating record...");
// update
s = c.prepareStatement("UPDATE "+table+" SET "+value+" = ? WHERE "+name+" = ?");
try {
s.setLong(1, v+1L);
s.setString(2, key);
s.executeUpdate();
} finally {
s.close();
s = null;
}
// do not commit yet, hold the locks
// signal other thread to try to get same data for update
synchronized (signal) {
thread1_ready = false;
signal.notify();
}
System.out.println("Main: Signalled other thread, sitting on locks for 10 seconds...");
try {
Thread.sleep(10000);
} catch (InterruptedException e) { }
System.out.println("Main: committing transaction - second thread should unblock now");
c.commit();
} finally {
c.close();
}
} catch (Exception e) {
System.out.println("Main: exception!");
e.printStackTrace();
}
}
public class Thread1 extends Thread {
public void run() {
try {
System.out.println("Thread1: getting connection...");
Connection c1 = DriverManager.getConnection(jdbc_url,user,pass);
c1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
c1.setAutoCommit(false);
System.out.println("Thread1: got connection...");
try {
// notify main thread we'return ready...
synchronized (signal) {
thread1_ready = true;
signal.notify();
// wait for signal
while (thread1_ready) {
try {
signal.wait();
} catch (InterruptedException e) {}
}
}
System.out.println("Thread1: now should block...");
long v = 0;
PreparedStatement s = c1.prepareStatement("SELECT "+value+" FROM "+table+" WHERE "+name+" = ? FOR
UPDATE");
try {
s.setString(1, key);
ResultSet rs = s.executeQuery();
try {
rs.next();
v = rs.getLong(1);
} finally {
rs.close();
}
} finally {
s.close();
}
System.out.println("Thread1: End of blocking!");
c1.commit();
} finally {
c1.close();
}
} catch (Exception e) {
System.out.println("Thread1: exception!");
e.printStackTrace();
}
}
}
}