Hello,

I am very new to SQL and DB2. I am trying to use the select for update sql command to read a row and temporarily lock it so that another transaction cannot access that row. However, it appears that other transactions are reading a row that is locked and should not be read, and it is causing errors. To be specific, here is what I want to do:

• My Java servlet is invoked via a web request.
• The servlet sends a 'select for update' statement to the DB2 table to read a row and temporarily lock it
• The servlet then immediately sends another request to the DB2 table to execute an update command on one of the columns (a flag that indicates that row is in use)
• The servlet then continues processing
• The sevlet completes processing and sends another update statement to the DB2 table to release that row (update the flag column to indicate no longer in use)

The problem comes between the 2nd and 3rd bullets. If another transaction comes in after the 'select for update' statement but before the in-use-flag is updated, it appears to be reading the same row that should have been locked.

Could it be that the lock only prevents the row from being updated, but still allows another transaction to read the locked row?

I hope I made a little sense. Any help will be greatly appreciated.

Thanks,
lugos

That may very well be the case.

See if the following makes sense:

http://www.dbforums.com/t1202151.html

http://www.dbforums.com/t862330.html

Check the documentation for isolation levels.
You might want to upgrade to RS or RR (default is CS cursor stability, which only locks the row at hand).

A lock generated by a select is compatible with a lock generated by select for update. So if you want the other application to wait if someone else has a select for update lock, then make the application also do a select for update, even if they have no intention of udpating the row.