Problem with Serializable transactions - Mailing list pgsql-bugs

Name:  Rob Green
e-mail:  Robert.Green@marconi.com

PostgreSQL version:  7.4.2
Java version:  1.4.1.03

Operating System:  HP-UX 11.00
Host Machine:  HP  N4000-44 (Quad processor)

Short Description:
      Serializable Transactions don't work as well as they did in
postgresql 7.3.3.


I am evaluating transaction and locking mechanisms for an imminent
multiuser database project.
I have written a simple java program to compare the characteristics of the
different isolation levels
and locks.

I have noticed that using postgresql 7.4.2 at serializable level it is
possible for two users to update
the database at the same time.  I then cracked out postgresql 7.3.3 and
built it on the same
machine, in the same environment (and compiled my program to use the 7.3.3
jar file), and the
program worked.  For completeness I built 7.4.1 and the problem was present
there as well.
I have run the regression checks (gmake check) and the database seems to
have built and
installed OK on this environment.

Therefore I don't know if something has changed in postgresql or the JDBC
between 7.3.3 and
7.4.1.

I am creating my initial data as follows:

      CREATE DATABASE rob;
      CREATE TABLE values ( valueid integer, value integer );
      INSERT INTO values VALUES (0, 0);
      INSERT INTO values VALUES (1, 0);
      INSERT INTO values VALUES (2, 0);

This gives me three values, all initially zero.

Then my program essentially does as follows (leaving out the exception
handling - a full listing
is attached):

      conn = DriverManager.getConnection("jdbc:postgresql://neelix/rob",
"greenrj", "");
      conn.setAutoCommit(false);
      conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
      for (int i=0; i<30; ++i) {
            for (int j=0; j<3; ++j) {
                  Statement stmt = null;
                  ResultSet rs = null;
                  try {
                        stmt = conn.createStatement();
                        rs = stmt.executeQuery("SELECT value FROM Values
WHERE valueId = " + Integer.toString(j));
                        if (rs != null && rs.first()) {
                              int v = rs.getInt(1);
                              System.out.println("i="+ i+ ", j="+ j+ ", v="
+ v);  // do some I/O
                              ++v;
                              stmt.executeUpdate("UPDATE Values SET value
= " + Integer.toString(v) + "WHERE valueId = " + Integer.toString(j));
                              conn.commit();
                        }
                  }
                  catch (SQLException e) {
                        // serialization exceptions & rollback here
                  }
                  finally {
                        if (stmt != null) {
                              stmt.close();
                              stmt = null;
                        }
                        if (rs != null) {
                              rs.close();
                              rs = null;
                        }
                  }
            }
      }
      conn.close();

Essentially the above program reads and increments the three values 30
times.  After the program has run, the database appears as follows:

rob=# select * from values order by valueid;
 valueid | value
---------+-------
       0 |  30
       1 |  30
       2 |  30

Now, if I run the program concurrently, from two xterms on the same
machine, I should see some serialization exception fireworks (which I do -
see my retry mechanism below) and the values set to 60.  With postgresql
7.3.3 I do - but with 7.4.2 I sometimes see:

rob=# select * from values order by valueid;
 valueid | value
---------+-------
       0 |  59
       1 |  60
       2 |  60

This happens about one time in three.  You need to be quick to get the two
programs running at
the same time, or spawn one in the background with the other in the
foreground ('test & test').

It always seems to be valueId 0 that misses an increment.
It only seems to happen once in a run (for instance I've never seen 58, 60,
60).

Here is a full listing of my program, showing the code for retrying when I
get a serialization
exception:

(See attached file: Increment.java)

Here is the typical output from the two sessions (user 1 and user 2):

(See attached file: user1.txt)(See attached file: user2.txt)

Here is a very rare occurrence (only caught it once) where there were no
serialization conflicts
and there was still a corruption:

(See attached file: user1A.txt)(See attached file: user2A.txt)

For user1, (at i=0) when j=0 a 9 was read and a 10 was written.  But also
for user2, (at i=9) when
j=0 a 9 was read and a 10 was written with no complaint.


I hope this is enough for you to tell what has changed.

regards,

Rob Green