Hi Christian,
At 12:11 PM 28/04/2003 +0200, Christian Gütter wrote:

>Hi Helen,
>
> > Do you understand that this is not for normal use? It runs
> > contrary to the
> > architecture of Firebird and potentially locks up huge numbers of
> > records. It is for use only in the very particular
> > circumstance where you
> > need updates to be truly serial - a very rare requirement. Avoid it,
> > except where there is an absolute requirement for serial updates.
>
>your posting makes me think that I might have misunderstood
>the concept of "SELECT FOR UPDATE WITH LOCK" [SFUWL].
>
>I agree with you that it is very useful for serial updates. But
>I always thought it is not limited to this particular use, because
>IMHO you can use it as a good replacement for dummy updates.
>
>Imagine that two users would like to edit a large document which
>is stored in a BLOB. In this case, you have to ensure that only
>one user at a time can do this. (If you have ever talked to a
>user who has lost two hours of work due to a deadlock, you know
>what I am talking about.) To accomplish this lock with FB 1.0x,
>you have to use a dummy update or you have to establish some
>kind of 'hand-made' locking. I have always used dummy updates.
>This worked, but it was quite annoying that all triggers fired
>twice.
>AFAIU you can use SFUWL to solve this problem avoiding the
>disadvantages of dummy updates by just selecting the single
>record which contains the blob mentioned above.

Yes, in this context you could exactly replace your current "dummy update"
row-level pessimistic lock with the internally-implemented pessimistic lock
by selecting that ONE row and ONLY that row. In both cases, you have
valid uses of ROW-LEVEL pessimistic locking.
So for me, SFUWL serves two purposes: it enables you to do

>serial updates and it enables you to lock (pessimisticly)
>single records in an efficient way (as I described above).

Quite so - if you use it only for locking a single row.

Have you thought about the effect of this?

SELECT * FROM CUSTOMER
FOR UPDATE WITH LOCK

>But due to the fact that you insisted on SFUWL making only
>sense with serial updates, I am quite unsure if I got
>everything right. So it would be great if you could comment
>on my example and perhaps restore my certainty of mind ;-)

Let's put this into the Delphi/IBO context (or any other application
context where you SELECT a resultset and, inside the same transaction,
scroll the set in order to perform DML operations on the focused
row). What happens to the rest of the company whilst this is going on, if
every row in the scrolling set is locked for update?

With the dummy update, you have the convenience of setting a pessimistic
lock on the focused row, while leaving all of the other rows available for
update by other users/transactions/applications.

That's why I say AVOID it unless you absolutely want that serialized
updating AND your application has absolute ability to restrict the scope of
the lock.

heLen