RE: locking records

Just for clarifying "Snotshot too old" is not an oracle message indicating changes to records. It actually has to do with the rollback segments. The message "snapshot too old" seems to have no connection with what it actually means.
When a transaction cannot fit into one single rollback segment, this error is generated. The transaction cannot continue because the snapshot-image of the table/database object before the transaction will be getting overwritten for lack of rollback segment space in the particular rollback segment the trasaction is assigned. Also, transactions cannot span rollback segments they are required to fit in a single one. This requires the DBA to provide for a bigger rollback segment and to be extra-sure that your transaction is utilizing this bigger rollback segment, you could set your transaction using alter session set transation user rollback segment <your-RBS-big-name>;

As for the locking question, I stick to your basic philosophy- oracle did a good job in transaction-control as regards locking mechanisms. I would not mess with it, unless I really need to.

-----Original Message-----

From: Diana Duncan [mailto:Diana_at_fileFRENZY.com] Sent: Tuesday, November 28, 2000 3:21 PM To: Multiple recipients of list ORACLE-L Subject: 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-----

Sent: Tuesday, November 28, 2000 1:26 PM To: Multiple recipients of list ORACLE-L

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.

---

Dennis Taylor
---

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Diana Duncan
  INET: Diana_at_fileFRENZY.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may