Dear PostgreSQL gurus,
I got a incorrect or unexpected behavior in concurrent environment.
Luckily, I was able to localize it and create an example:
My setup:
Postgres 8.2 ( with 8.1 the same effect )
Linux ( with Solaris the same effect )
java 1.5, JDBC driver 8.2-504.jdbc3 ( with earlier versions the same effect )
The table:
create table t_jtest (
ikey CHAR(36) primary key,
ivalue integer not null
);
The appication:
Client 1:
autocommit off
loop:
INSERT INTO t_jtest VALUES (?,?)
UPDATE t_jtest SET ivalue=? where ikay=?
commit
Client 2:
autocommit off
"SELECT COUNT(*) FROM t_jtest"
do nothing
The effect is that time, which is needed by Client 1 is growing, unless I add a
commit into client 2.
Is it normal behavior?
Both application attached. To run:
javac *.java
in terminal 1:
java -cp postgresql-8.2-504.jdbc3.jar DbIject
in terminal 2:
ava -cp postgresql-8.2-504.jdbc3.jar Spy
Regards,
Tigran.
________________________________________________________________________
Tigran Mkrtchyan DESY, IT,
[EMAIL PROTECTED] Notkestrasse 85,
Tel: + 49 40 89983946 Hamburg 22607,
Fax: + 49 40 89984429 Germany.
import java.sql.*;
import java.util.UUID;
/*
create table t_jtest (
ikey CHAR(36) primary key,
ivalue integer not null
);
*/
public class DbIject {
/**
* @param args
*/
public static void main(String[] args) {
try {
Class.forName("org.postgresql.Driver");
Connection newConnection = null;
newConnection = DriverManager.getConnection("jdbc:postgresql://localhost/jTest?prepareThreshold=3",
"postgres", "");
newConnection.setAutoCommit(false);
String firstId = null;
for(int i = 0; ; i++) {
PreparedStatement ps = newConnection.prepareStatement("INSERT INTO t_jtest VALUES(?,?)");
String id = UUID.randomUUID().toString();
long now = System.currentTimeMillis();
ps.setString(1, id );
ps.setInt(2, i);
ps.executeUpdate();
ps.close();
if(i == 0 ) {
firstId = id;
}else{
ps = newConnection.prepareStatement("UPDATE t_jtest SET ivalue=? WHERE ikey=?");
ps.setString(2, firstId );
ps.setInt(1, i);
ps.executeUpdate();
ps.close();
}
newConnection.commit();
System.out.println(i + " " + (System.currentTimeMillis() - now) );
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
import java.sql.*;
public class Spy {
public static void main(String[] args) {
try {
Class.forName("org.postgresql.Driver");
Connection newConnection = null;
newConnection = DriverManager.getConnection("jdbc:postgresql://localhost/jTest?prepareThreshold=3",
"postgres", "");
newConnection.setAutoCommit(false);
PreparedStatement ps = newConnection.prepareStatement("SELECT COUNT(*) FROM t_jtest");
ResultSet rs = ps.executeQuery();
rs.close();
ps.close();
// newConnection.commit();
Thread.sleep(3600000);
} catch (Exception e) {
e.printStackTrace();
}
}
}
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
