Apparent bug in transaction processing in serializable mode

From: Jan Hlavatý <Jan(dot)Hlavaty(at)code(dot)cz>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Apparent bug in transaction processing in serializable mode
Date: 2002-12-22 11:44:56
Message-ID: 3E05A5B8.3000809@code.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi, I have found something suspicious testing pg73jdbc3.jar:

I have 2 connections, both using serializable transaction isolation mode
and no autocommit.

I have a table containing (name,number) records and I try to increment
number in record with given name.

On one connection, I perform select on record with for update clause.
This is supposed to lock the record with update type lock against other
concurrent updates.
Then I update that record with new number. All this should do is upgrade
update lock to exclusive one.

On the second connection, I try to do the same.
If things were as it should be, select for update in second connection
would block waiting on lock (first update then exclusive) until
transaction in first connection commits, then continue normally getting
update lock on the (new) record and eventually updating it again.

BUT - for some reason, if the other transaction tries the select for
update after first has updated the record but before it has committed
it, instead of blocking, I get following exception immediately:

java.sql.SQLException: ERROR: Can't serialize access due to concurrent
update
at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
at
cz.code.test.TestSerializable$Thread1.run(TestSerializable.java:144)

I have no idea why it happens. It should wait for the first transaction
to commit!

When i leave out the update statement from first transaction, it works
OK (i.e. second transaction blocks until commit of the first).

I have attached a test case to show this.
Can someone plese fix it soon?

Jan

Attachment Content-Type Size
TestSerializable.java text/plain 5.6 KB

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Andreas Joseph Krogh 2002-12-22 12:34:59 Patch against 7.3.1 AbstractJdbc1Connection.java to allow schema in connectionURL
Previous Message Nagy István 2002-12-21 16:31:33 JDBC SSL - looking for test partners