First page Back Continue Last page Graphics

…Locking Issues: Lost Update


Notes:

The application could include a FOR UPDATE NOWAIT clause on the SELECT statement that retrieves the row just prior to the update. Perhaps the user has view a list of rows and decided which row(s) to update. This SELECT with the FOR UPDATE NOWAIT clause attempts to place an exclusive (TX) lock on the row. The NOWAIT option will cause Oracle to return an error saying the row is in use (leaving NOWAIT off could cause the session to hang, waiting for another session to release a lock). In this case the application may tell the user “row in use, try again later”. The downside to this technique is that the row is locked until the user commits the update, and could lead to excessively long locks being held.

The second option tries to update the row, but includes the old (original) values in the WHERE clause. If the value has been changed, the UPDATE will return a +100 (no data found), and the application inform the user that “the row has changed, reread row before updating.”