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.

> > 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

>Sorry if this is stupid question....(It seems so, becouse nobody
>answered)

It's not a stupid question, though it is one that gets asked a lot. The
answer is that if you have update contention, you must keep transactions
short and that means that the user reads data in one transaction, creates
a plan to change it after committing that transaction, then when the
change is complete the application starts a new transaction to write out
the changes, validating the assumptions in the new transaction. If the
assumptions have changed, the application must go back to the user for
new data. That's a lot of work.

The other solution, when possible, is to design the application so
changes (meaning inserts, updates and deletes) don't conflict. That
too is hard.

>So, when I find some info related to this issue ?

The archives ... But essentially, as soon as you change a record,
you write a record version with your transaction id into the database.
That record version will prevent others from changing that record.
It doesn't matter whether the record is changed directly by your
code or by a trigger or stored procedure.

>I mean: how can I know that BLR compiled code is attempting to lock
>record when particular update instruction is reached in SP or just when
>SP is started or readed from cache ?

Unless you're using the explicit locking in Firebird 1.5 (select for
update with lock), there is no locking, only the creation of a record
version. The execution of an insert, update, or delete statement
creates a record version.

Regards,


Ann