Re: conflict txns in serialization isolation - Mailing list pgsql-jdbc

From Yi LIN
Subject Re: conflict txns in serialization isolation
Date
Msg-id Pine.GSO.4.44.0407261031120.29092-100000@willy
Whole thread Raw
In response to Re: conflict txns in serialization isolation  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: conflict txns in serialization isolation
List pgsql-jdbc
Hi Tom:

> That's a true statement for SELECT, but a read-committed UPDATE will
> in fact find and update the latest version of the row; it won't cause
> changes to be lost, even if they occurred after the UPDATE statement as
> a whole started.  The same is true of SELECT FOR UPDATE.
>
> I'm not sure how that relates to your previous observations though.
> Can you duplicate the apparent misbehavior in psql?  I can't.
>

I tested psql and here is the observation I had:

For transaction isolation level = SERIALIZABLE,

If two concurrent txns update same row(or rows), one of them will be
aborted.
If one txn updates row(s) and the other concurrent txn selects the same
row(s) for update, the select txn which happened later will be blocked and
then aborted upon the time when the previous update txn commits.

For transaction isolation level = READ COMMITTED,

If two concurrent txns update same row(or rows), one of them will be
blocked before the previous txn commits, but it will commit after the
previous txn commits.
If one txn updates row(s) and the other concurrent txn selects the same
row(s) for update, the select txn which happened later will be blocked and
then select and commit upon the time when the previous update txn
commits.

These observations on psql show that postgresql7.2 is correct in terms of
SERIALIZABLE and READ COMMITTED.

However, when the JDBC program doesn't correctly work, as I stated in my
first email. i.e.,

If two concurrent txns update more than 1 same rows, one of them will be
aborted upon the time when the previous txn commits. But if they update
only 1 same row, none of them will be aborted. It doesn't matter if
transaction isolation level is set to SERIALIZABLE or READ COMMITTED.

It doesn't conform to either SERIALIZABLE or READ COMMITTED observation in
psql. So I wonder if there is a bug in JDBC driver. I set transaction
level using these APIs:

db = DriverManager.getConnection(url, username, password);
db.setAutoCommit(false);
db.setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);

Regards,

Yi



pgsql-jdbc by date:

Previous
From: "Nick Fankhauser"
Date:
Subject: Re: Problem w/ IDENT authentication
Next
From: Tom Lane
Date:
Subject: Re: Problem w/ IDENT authentication