db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject SELECT ... FOR UPDATE not working?
Date Thu, 18 Aug 2005 21:23:01 GMT

I stumbled across a possible bug with the SELECT ... FOR UPDATE clause. 
I have found several sources of information regarding this statement, 
with partly conflicting contents.

First of all, I wrote a simple JDBC test application making use of the 
statement. I ran this on Derby and two other database systems. Derby did 
not behave as the two others (more on this later).

Second, the reference manual states that the statement is supported, and 
that it must be used to obtain updateable resultsets.

Third, the JIRA issue 231 
(http://issues.apache.org/jira/browse/DERBY-231) is regarding support 
for SELECT ... FOR UPDATE. It is unresolved and unassigned. Is this a 
stale issue?

The JDBC application I wrote consisted of two threads accessing a single 
table: locktesttbl (ID INT, VALUE INT). I inserted 5 rows 
(1,1),(2,2),...,(5,5). The first thread executes 'SELECT * FROM 
locktesttbl WHERE ID=1 FOR UPDATE', waits 10 seconds, closes the 
resultset, executes 'SELECT * FROM locktesttbl WHERE ID=1', closes 
resultset and commits. The second thread, which is started 2 seconds 
after the first one, executes 'UPDATE locktesttbl SET VALUE=100 WHERE 
ID=1' then commits.

The only time the selected VALUE field in the first thread was equal at 
the beginning and the end of the transcation, was when the transcation 
isolation level was set to SERIALIZABLE. At all other levels, VALUE was 
100 at the end of the transaction (before commit). When I did this with 
the two other systems (MySQL and PostgreSQL), VALUE was always 1 within 
the transaction. This suggests SELECT .. FOR UPDATE is broken in Derby, 
and that the single instance of correct behavior seen is due to the 
transaction isolation level alone. I have not looked into the source 
code on this.

Does anyone have any comments on this?

I will add a JIRA bug issue under category SQL for this one in a few 
days (awaiting comments).


No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 17.08.2005

View raw message