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

Re: Problem with Serializable transactions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Robert Green" <Robert(dot)Green(at)marconi(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Problem with Serializable transactions
Date: 2004-03-26 00:36:33
Message-ID: 16447.1080261393@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
"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

In response to

Responses

pgsql-bugs by date

Next:From: Oliver JowettDate: 2004-03-26 01:37:17
Subject: Re: [JDBC] Problem with Serializable transactions
Previous:From: Joshua D. DrakeDate: 2004-03-26 00:31:54
Subject: [Fwd: [HACKERS] PostgreSQL ES3.0 problems?]

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-03-26 01:37:17
Subject: Re: [JDBC] Problem with Serializable transactions
Previous:From: Gunjeet SinghDate: 2004-03-26 00:05:52
Subject: Using User Defined Type in JDBC code

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