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