SELECT FOR UPDATE WITH RS
Posted on 2007-08-01
1) We are using DB2 8.0.1 Express on Red Hat Linux 9
2) SELECT MAX(POINT_SEQ) + 1 FROM SFUCHECK WHERE POINT_DATE = CURRENT DATE FOR UPDATE WITH RS; is not working due to one of the many SELECT FOR UPDATE restrictions. Even just SELECT MAX(POINT_SEQ) is not allowed.
3) So, I used SELECT POINT_SEQ + 1 FROM SFUCHECK WHERE POINT_SEQ = (SELECT MAX(POINT_SEQ) FROM SFUCHECK WHERE POINT_DATE = CURRENT DATE) FOR UPDATE WITH RS;
This works fine till the point that succeeding waiting transactions read same MAX value & hence ultimately same value after exclusive read lock is released in remnant windows.
4) FOR UPDATE WITH RS is not allowed in inner query and so not MAX aggregate function in direct WHERE clause itself. Please suggest a work around.