Postgresql jdbc drivers not listening to setTransactionIsolation()?

From: Daniel Osborne <dosborne(at)openworld(dot)co(dot)uk>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Postgresql jdbc drivers not listening to setTransactionIsolation()?
Date: 2000-06-16 15:19:24
Message-ID: 394A457C.506698E0@openworld.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

Hi,

I'm in the process of building a java app that imports a new set of data
into postgresql, with the plan that should anything fail I can rollback
the transaction. However, I'm having trouble setting the Isolation
level: I need it to be READ_COMMITTED, which is apparently the default,
but it appears to be acting as if it is set to SERIALIZABLE, even though
getTransactionIsolation() reports it to be READ_COMMITTED.

If I open two psql windows, set up a transaction in one, and make a
change to the data in it, everything behaves as I would expect
READ_COMMITTED to: the transaction window shows the latest changes, the
non-transaction window displays the original data until the transaction
is committed.

However, if my java app is in the process of making the changes
(deleting all records, and adding new records), in its own
READ_COMMITTED transaction, and another client, eg a psql window, asks
for a list of all the records, the psql window blocks until the
transaction commits, at which point it shows the new data. This is the
behaviour I'd expect from a SERIALIZABLE transaction; connection B waits
for connection A to finish.

Also, when I call setTransactionIsolation(SERIALIZABLE) and then call
getTransactionIsolation(), it returns READ_COMMITTED.

I'm no database expert, so I guess I'm missing something here, but I
don't know what to do next.

Has anybody else experienced similar unexpected behaviour, or can
anybody explain what I'm doing wrong?

Thanks,

Daniel

Browse pgsql-interfaces by date

  From Date Subject
Next Message ukritcf 2000-06-16 16:48:41
Previous Message Tom Lane 2000-06-16 15:04:57 Re: [PATCH] Datatype of OID should be VARBINARY, not INT