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.

---

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. Received on Tue Nov 28 2000 - 12:25:18 CST