Thread: conflict txns in serialization isolation

I doubt if this is a bug for serialization isolation in postgresql 7.2.

I wrote a small java program to test serialization isolation level. By
definition of serialization isolation
level(http://www.postgresql.com/docs/7.2/static/xact-serializable.html),
if two concurrent txns are conflict, one of them should be aborted.
However, I found that abortion only happened when two current txns are
conflict in terms of more than 1 rows. For example,

txn1: update t_a set t_a_param2=213 where t_a_id=50 or t_a_id=51;
txn2: update t_a set t_a_param2=213 where t_a_id=50 or t_a_id=51;

In this case txn2 or txn1 will be aborted at the time of commit of the
first txn. But, if

txn1: update t_a set t_a_param2=213 where t_a_id=50;
txn2: update t_a set t_a_param2=213 where t_a_id=50;

none of them will be aborted. The last txn will be blocked until the first
txn commits and then get executed and commit.

Also select for update doesn't work as specified in
http://www.postgresql.com/docs/7.2/static/xact-serializable.html
A select for update txn won't block a conflicting update txn at all. But a
conflicting update txn does block a select for update txn.

By the way, I checked psql client. In psql client, the first conflicting
update txn will block the second concurrent conflicting update txn. But
instead of abortion, the second one will be executed after the first ones
commit.

Does anyone have the same problem as I? I am using postgresql7.2

thanks,

Yi

Yi LIN <ylin30@cs.mcgill.ca> writes:
> Does anyone have the same problem as I? I am using postgresql7.2

Nope, not even in 7.2.

I kinda suspect that you are getting confused by JDBC autocommit
behavior and are having transactions commit when you thought they
were still open.  But without a complete example it's only speculation.

Your first gripe might also be explained by supposing that the
transactions are actually in read committed mode, and not serializable
mode at all.

            regards, tom lane

> I kinda suspect that you are getting confused by JDBC autocommit
> behavior and are having transactions commit when you thought they
> were still open.  But without a complete example it's only speculation.
>
> Your first gripe might also be explained by supposing that the
> transactions are actually in read committed mode, and not serializable
> mode at all.

I realized those potential problems and in my codes:

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

Even in Read-Committed isolation level, if version check is performed(as I
suppose postgresql7.2 will always do), one of concurrent conflicting txns
will be aborted. Read-committed is different from Snapshot in that
Read-Committed txn will read data committed before read operation starts
but snapshot txn will read data committed before the whole txn starts.

Do I correctly understand how postgresql7.2 works?

Regards,

Yi

Yi LIN <ylin30@cs.mcgill.ca> writes:
> Read-committed is different from Snapshot in that
> Read-Committed txn will read data committed before read operation starts
> but snapshot txn will read data committed before the whole txn starts.

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.

            regards, tom lane

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

On Mon, 26 Jul 2004, Yi LIN wrote:

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

I think you want to set the transaction isolation before starting the
transaction by turning off autocommit.  Later versions of the driver make
this work correctly or throw an error on this situation, but I recall you
were using an old version.  So try reordering the last two lines.

Kris Jurka

Hi Kris:

> > db = DriverManager.getConnection(url, username, password);
> > db.setAutoCommit(false);
> > db.setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
> >
>
> I think you want to set the transaction isolation before starting the
> transaction by turning off autocommit.  Later versions of the driver make
> this work correctly or throw an error on this situation, but I recall you
> were using an old version.  So try reordering the last two lines.

Now it works correctly as psql. Thanks a lot!

Yi Lin