At 07:36 AM 17/07/2003 +0100, you wrote:

>Unless I'm just off my rocker, I seem to remember that if you start
>updating a row, rows referenced
>by foreign key values will also get locked on that same transaction

Yes. This is what referential integrity is for.

> (or at least attempt to do so -- some sort of read-lock?)

No: it's a write lock. Unless isolation is TABLE STABILITY (i.e.
tiConsistency) readers will continue to see the last-committed versions of
both the parent and the dependent sets inside *their* transactions.

>In our case, this meant that if you're in the middle of updating
>someone's SSN, and you try to also update their address (popup-screen of
>dependent record on a
>different transaction) you'll get a deadlock (we use NOWAIT, so it's at
>least not too irritating.)

True. Because the DB does not support column-level locking. The engine
cannot tell in advance which column(s) each transaction will update - it
might be the linking key, or the row might even be deleted. So - with
optimistic locking - if your transaction manages to get the lock on the
master record, it means it also has locks on the dependent records, as long
as the DML stays pending.

>(when someone starts editing, we explicitely do 'update {tablename} set id
>= id where id = {value}' -- this conflicts with parent records already
>being edited.

NOWAIT is correct for this - all the app has to do is catch the lock
exception and tell the user that someone else is already updating this set
of data.

>as we've already selected the data, we don't bother reselecting 'for update'.)

Selecting 'for update' in an ordinary SELECT statement does absolutely
nothing to the lock status of any row in the output set. It simply causes
the server to send over one row at a time and wait to be asked for the next
one. It does have a purpose when the SELECT output is assigned to a named
cursor - it makes it valid for subsequent UPDATE and DELETE statements to
be applied to the current row pointed to by the cursor, via a WHERE CURRENT
OF <cursorname> construction.

>We've not really minded ... but I was wondering if someone could give me
>the theory behind this?

It's very simple - it's an optimistic row-level locking model: first come,
first served. You can read the set for ever and a day. Once you pick row
x and successfully request *any* sort of DML change to it, then row x and
all of its dependencies are protected, for whatever you want to do to them,
until the transaction ends.

You're treating it as "inconvenient" that one transaction is prevented from
deleting or changing rows that depend on a row that another transaction is
working on. It would be a heck of lot more "inconvenient" if transactions
were allowed to ignore the integrity rules.

>Why would you automatically lock rows referenced (such as master records)
>when updating others (dependent records)? Is it necessary to reduce
>logical errors from multiple transactions competing for resources that
>aren't the identical, but related?

"Related" is the key concept here. The integrity of relationships is
enforced by the ways in which rows are related. In an optimistic locking
system, where no locks exist until the moment that a DML request succeeds,
there is no room for the engine to guess the mind of the user. *You* are
choosing to crag up the dependencies by using pessimistic locking. That's
the price you pay for going against the optimistic model.

>In case it matters, methinks we're still running 1.0.2 ... and of course,
>again, I could just be remembering incorrectly.

I think if you have made it so that your DML requests never done anything
but force a pessimistic lock the moment a user starts editing a row -
apparently to avoid deadlocks!! - then you don't really understand how
optimistic locking works (or you do understand it but prefer to avoid
handling concurrency conflicts!). Pessimistic locking doesn't fit into the
optimistic locking model. Your client-side pess. locking is a hack for the
rare conditions when it is desirable. If you want to use it in unintended
ways so you don't have to handle any deadlocks, then you gets what you pays
for.

I've said it before and I'm saying it again - a deadlock is NOT a
problem. It is a database condition which the server conveniently signals
back to the client for immediate handling. Deadlocks are MEANT to
happen. Developers of client/server applications are MEANT to manage them.

Helen