Re: Problem with Serializable transactions

"Robert Green" <Robert(dot)Green(at)marconi(dot)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