Re: Is the row updated since or while I am looking at it?

On Tue, 22 Dec 1998, David Spaisman wrote:
>
> The question posed was: "How do I know if a row was updated since or
> while I am looking at it"?

Two approaches to this problem are typical:

• When you select the row to work on it, issue a "select ... fur update;" This locks the row so that other people trying to work on the row will have to wait until the person locking it is done.
• After selecting the row and changing it, "select ... for update;" the row again just before updating it, to verify that no changes have been made while the application was playing with the row. If the data has changed out from underneath the user, they have to start over.

It is generally felt that #2 is a better approach, because #1 allows big locking pileups to occur is a heavy OLTP envirenment. For instance, if an application has selected several other rows in other tables for update, but then has to wait on a lock, others may come along and have to wait on the rows that the first waiting session is holding. This has a cascading effect that results in big locking pileups. If the person at the top of the lock chain goes out for a smoke, a DBA has top come along and kill the blocking session off. This does not exactly promote "smooth operation."

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah