Ann W. Harrison wrote:

> At 07:31 AM 10/29/2004, =?ISO-8859-2?Q?Bogus=B3aw_Brandys?= wrote:
>
>
>>Bogusław Brandys wrote:
>>
>>>Assume that I had to update some integer value in sigle row in
>>>particular table and I hold lock on this single record using for example
>>>
>>>update table set number=number + 1 where id=?
>>>
>>>If I do that in the final stage of my transaction instead of just on
>>>start would this row be shorter locked or it doen't matter ?
>
>
> Locks are held for the duration of the transaction. However, waiting
> to lock that record will give you other problems - update conflicts if
> you're running in snapshot mode and data inconsistency if your running
> in read committed mode.

Sorry , I didn't mention that this question is related to my previous
question about unbroken series of document numbers wrapped monthly, not
with updating oridinary records per se.
According to You answer ,I will probably decide to use read committed
mode. In snapshot mode would be lock conflict for sure.

>>>Updating this row is bottleneck for my application right now, but I
>>>suspect becouse I lock record (and increment stored value) just in ON
>>>BEFORE INSERT trigger.
>
>
> That sort of logic causes problems in any transaction system. Here's a
> response from Tom Lane - the PostgreSQL optimizer guru - on their general
> list:
>
> From: Tom Lane
>
> Terry Lee Tucker <terry@...> writes:
> > I would like to be able to provide feedback to the user when they
> > select a row for update (using SELECT FOR UPDATE). At present, if the
> > row is being accessed (with SELECT FOR UPDATE) by another user, the
> > application just sits there waiting.
>
> To me, this says that you're already off on the wrong foot.
>
> You don't ever want your client application holding locks while a
> human user edits text, drinks coffee, goes out to lunch, or whatever.
> A better design is to fetch the data without locking it, allow the
> user to edit as he sees fit, and then when he clicks "save" you do
> something like
>
> begin;
> select row for update;
> if [ row has not changed since you originally pulled it ] then
> update row with changed values;
> commit;
> else
> abort;
> notify user of conflicts
> let user edit new data to resolve conflicts and try again
> fi
>
> In this design the row lock is only held for milliseconds.
>
> You need to provide some code to let the user merge what he did with the
> prior changes, so that he doesn't have to start over from scratch in the
> failure case. What "merge" means requires some business-logic knowledge
> so I can't help you there, but this way you are spending your effort on
> something that actually helps the user, rather than just tells him he
> has to wait. Performance will be much better too --- long-lasting
> transactions are nasty for all sorts of reasons.
>
>
> regards, tom lane
>

This is very important and interesting issue (could be included in
Firebird FAQ), I will use this idea, but not in this case. I must lock
this single row updating it ,in the same transaction after inserting
document header,detail,updating stock and so on... Of course there is
possibility to broke this into two transactions : the one which
generates document with NULL number and the later which assigns next
number for it, but this way I must create some failover to find and
delete lost documents with NULL number.
Probably is better to insert all what should be inserted, update stock
and other data and in the last stage of this read committed transaction
generate next document number.


Best Regards
Bogusław Brandys