transactions getting slon in councurrent environment - Mailing list pgsql-bugs

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,
tigran.mkrtchyan@desy.de                       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();
        }

    }
}