Re: [JDBC] Problem with Serializable transactions

Tom Lane wrote:
> "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).

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