Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

In comp.databases.informix Anthony Mandic <no_sp.am_at_agd.nsw.gov.au> wrote:
: Joel Garry wrote:
: No, I've been implying that "read for update" or "select with lock"
: or whatever its called is the wrong approach. The read/select
: should not be part of the real transaction. They could be held
: indefinitely, in theory. The real tansaction is the
: insert/update/delete.
: These should be fast. If there are a lot of them in one hit, I'd
: consider rethinking the approach used to design the app that
: does this. The implication here is that the app may be far too
: complex, thus being more vulnerable to problems. Keeping it
: simple never hurts.

Let's suppose an order-entry app, with a customer table that has row-level locking, and an order table with page-level locking.

Why can't the app do a SELECT FOR UPDATE on the customer table for the requested customer, do a non-locking SELECT on the order table, then UPDATES on the order table, then release the lock on the customer table?

This would seem to be the best of both worlds, with row-level locking overhead only on the table that needs it, and it is kept while the user is browsing the order table.

Can't do this without the capability of row-level locking, and the nice thing is you can do RLL only on the tables that need it.

How are people locking the rows while people are browsing if they use PLL? My guess is they are using the external lock mechanisms mentioned, like lock table with lock entries.

-- 
Bruce Momjian                          |  830 Blythe Avenue
root_at_candle.pha.pa.us                  |  Drexel Hill, Pennsylvania 19026 
  +  If your life is a hard drive,     |  (610) 353-9879(w) 
  +  Christ can be your backup.        |  (610) 853-3000(h)