Thread: Problem with Serializable transactions

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

"Robert Green" <Robert.Green@marconi.com> writes:
> 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 ran your test program here and tracked down what the problem is.
What's happening is that the JDBC driver is issuing commands in the
wrong order.  Look at this log_statement trace of startup of one
of your test processes:

2004-03-25 19:19:58 31096 LOG:  statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) =
'SQL_ASCII'then 'UNKNOWN' else getdatabaseencoding() end; 
2004-03-25 19:19:58 31096 LOG:  statement: set client_encoding = 'UNICODE'
2004-03-25 19:19:58 31096 LOG:  statement: begin;
2004-03-25 19:19:58 31096 LOG:  statement: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
2004-03-25 19:19:58 31096 LOG:  statement: SELECT value FROM Values WHERE valueId = 0
2004-03-25 19:19:58 31096 LOG:  statement: UPDATE Values SET value = 31WHERE valueId = 0
2004-03-25 19:19:58 31096 LOG:  statement: commit;begin;

The error is that "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION
LEVEL SERIALIZABLE" is issued *after* the first BEGIN.  This means that
the transaction level of the first transaction has already been set,
and it's READ COMMITTED.  Your bug happens when a write conflict occurs
during that first transaction (this is why you never saw it on any
valueId except zero).

Doing things in this order is broken for another reason, which is that
if the first transaction later rolls back with an error, the SET will be
rolled back too, and so all the subsequent transactions will have the
wrong isolation level as well.

In short: if the driver is gonna use SET SESSION CHARACTERISTICS for
this, it *must* issue it outside any transaction block.

            regards, tom lane

Oliver Jowett <oliver@opencloud.com> writes:
> What do we do if setTransactionIsolation() is called halfway through a
> transaction?

You can't change the isolation status of an already-started transaction
(both Postgres and the SQL spec agree on this).  So to the extent that
the user expects this to affect the current xact and not just subsequent
xacts, that's user error that might be best addressed through
documentation.  However, it sounds like the JDBC driver is contributing
to the problem by delaying the effectiveness of the SET more than a user
would reasonably expect.

> Perhaps we should just always throw an exception if
> setTransactionIsolation() is called with autocommit off, since we know
> that doesn't work at all currently?

Might be a good quick-hack answer.  In the long run I think what you
ought to do is save the requested change and apply it during the next
transaction boundary --- that is,
    commit;begin
becomes
    commit; SET SESSION ... ; begin
I also wonder if you could force this to happen immediately if the
current transaction hasn't actually done anything yet.

Given your comment about the JDBC spec, Robert's test program is simply
wrong as written, so his answer is to change the order of calls.

            regards, tom lane

Tom Lane wrote:
> "Robert Green" <Robert.Green@marconi.com> writes:
>
>>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 ran your test program here and tracked down what the problem is.
> What's happening is that the JDBC driver is issuing commands in the
> wrong order.  Look at this log_statement trace of startup of one
> of your test processes:
>
> 2004-03-25 19:19:58 31096 LOG:  statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1)
='SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end; 
> 2004-03-25 19:19:58 31096 LOG:  statement: set client_encoding = 'UNICODE'
> 2004-03-25 19:19:58 31096 LOG:  statement: begin;
> 2004-03-25 19:19:58 31096 LOG:  statement: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
> 2004-03-25 19:19:58 31096 LOG:  statement: SELECT value FROM Values WHERE valueId = 0
> 2004-03-25 19:19:58 31096 LOG:  statement: UPDATE Values SET value = 31WHERE valueId = 0
> 2004-03-25 19:19:58 31096 LOG:  statement: commit;begin;
>
> The error is that "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION
> LEVEL SERIALIZABLE" is issued *after* the first BEGIN.  This means that
> the transaction level of the first transaction has already been set,
> and it's READ COMMITTED.  Your bug happens when a write conflict occurs
> during that first transaction (this is why you never saw it on any
> valueId except zero).

One (untested) workaround that might work is to call
setTransactionIsolation() *before* setAutoCommit(). The problem is that
the JDBC driver keeps a transaction open whenever autocommit is off,
even if no statements have been executed since the last
setAutoCommit()/commit()/rollback(). This also causes the "idle JDBC
connections produce idle-in-transaction backends" issue.

> Doing things in this order is broken for another reason, which is that
> if the first transaction later rolls back with an error, the SET will be
> rolled back too, and so all the subsequent transactions will have the
> wrong isolation level as well.
>
> In short: if the driver is gonna use SET SESSION CHARACTERISTICS for
> this, it *must* issue it outside any transaction block.

What do we do if setTransactionIsolation() is called halfway through a
transaction? Refusing to do anything (and throwing an exception) seems
better than accepting a request that might get rolled back. The current
driver doesn't track transaction state beyond "autocommit is off, I must
be in a transaction!" so there will be some lower-level work needed if
we want to be more selective about this.

The JDBC spec says that the behaviour of setTransactionIsolation() is
implementation-defined if it's called during a transaction, so the
client is digging a hole for themselves anyway if they do this. Of
course, the spec doesn't seem to define exactly *when* a transaction is
considered to start (surprise surprise) so the safest course for
portable clients is probably to always set isolation while autocommit is on.

Perhaps we should just always throw an exception if
setTransactionIsolation() is called with autocommit off, since we know
that doesn't work at all currently?

-O