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