RE: locking records

It depends on your application, and how long data sits around on the user's screen before they can do something with it. I don't like locking when it's unlikely that the user will make any changes, or the data may sit on the screen for very long (actually, I don't like to do it at all, on the appplication level). However, the following methods may work:

1. When you go to do an update, use all of the original values of the row in the where clause. If anything has changed in the meantime, your update will affect zero rows. If that has happened, return the new data to the user, explaining that changes have occurred since their query.
2. Go for your number one choice. The "snapshot too old" error is Oracle telling you that the record has changed underneath you (I think), so again, you can give the user a nice error message by handling that exception.
3. Actually, your number 3 method is not too bad, if you have an "update mode".
4. Add a timestamp column to all of your tables, and check against it before performing the update. This is really a variation on 1).

I guess my basic philosophy is: Oracle has done the locking for you, at least not allowing you to update a record that someone else has already updated but not committed -- do you really want to reinvent the wheel? You just want to make sure your user's don't stomp on someone else's changes, right?

Regards,
Diana

-----Original Message-----
From: Dennis Taylor [mailto:ismgr_at_pctc.com] Sent: Tuesday, November 28, 2000 1:26 PM To: Multiple recipients of list ORACLE-L Subject: locking records

I'm trying to figure out the optimum record locking scheme for our application. My choices are:

1. select record for update when initially reading it.
2. re-select record for update (using rowid) when user decides to write.
3. re-select record for update (using rowid) when user decides to start modifying.
4. Use some application-level locking scheme.

Pros and Cons:

1. Easiest, most dependable. However, it starts a transaction (I think) which can cause the "snapshot too old" error (is this right?)
2. Nice short lock/transaction durations. But logic has to be added to allow for the record having changed meanwhile.
3. Combines the worst aspects of 1 and 2.
4. Great for programming, but not enforceable for anyone using straight sql. Plus all programs have to be well-behaved.

I know this isn't primarily a development mailing list, but it seems to me as DBA's you'd have had to deal with the design and consequences of locking schemes. Any feedback on what works and what doesn't would be appreciated. Received on Tue Nov 28 2000 - 17:18:34 CST