Skip site navigation (1) Skip section navigation (2)

Re: [JDBC] Problem with Serializable transactions

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Green <Robert(dot)Green(at)marconi(dot)com>,pgsql-bugs(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Problem with Serializable transactions
Date: 2004-03-26 01:37:17
Message-ID: 4063894D.1090809@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2004-03-26 02:18:31
Subject: Re: [JDBC] Problem with Serializable transactions
Previous:From: Tom LaneDate: 2004-03-26 00:36:33
Subject: Re: Problem with Serializable transactions

pgsql-jdbc by date

Next:From: Mikhail TsDate: 2004-03-26 02:15:50
Subject: Invalid message format
Previous:From: Tom LaneDate: 2004-03-26 00:36:33
Subject: Re: Problem with Serializable transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group