RE: locking records

Hi Dennis,

1. It's bad. It's called 'pessimistic locking' and causes increasings of locking times for particular records or ranges of records.
2. and 3) not sure I've understood the difference between these methods Maybe it depends on how you define your transactions.
3. You answered yourself.

Some more ideas... Your target is define the same order of updating groups of related tables, it decreases the possibility of deadlocks. But I know that is not always possible. Try to keep your transactions short. Try to avoid development of any kind of 'arbitrary tables'

I think, generally, you don't need to worry about 'snapshot too old' errors except situations when you develop long batch tasks and probably want to give an option of choosing of particular rollback segment on application level

HTH Ed

>
>
> 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
> ---
> Don't worry about people stealing your ideas. If your ideas
> are any good,
> you'll have to ram them down people's throats.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Dennis Taylor
> INET: ismgr_at_pctc.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
Received on Wed Nov 29 2000 - 02:23:00 CST