| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: locking records
Hi !
I guess you are missing some basics here. It is not only related to to your 'transaction can not fit in to single rbs'.
There are n number of reasons for that. (like fetch acorrs commit, delayed block cleanout, 1578 in rbs... etc).
Have a look at www.ixora.com.au. Some excellent notes (in the forms of Q&A) about the CR read and rollback segments internals.
There is a draft note (which MAY contain some errs.) at http://www.geocities.com/kgkrish/transactions.html
Have a look at that. It may help you in clearing some of your concepts.
Have a nice day !!!
Best Regards,
K Gopalakrishnan
Bangalore, INDIA
> 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-----
> Sent: Tuesday, November 28, 2000 3:21 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Priya Karanam
> INET: priya.karanam_at_military-inc.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
> also send the HELP command for other information (like subscribing).
Received on Wed Nov 29 2000 - 03:00:53 CST
 |
 |