Re: conflict txns in serialization isolation

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