Lock rekord

"Pawe� Dubin" wrote:

Hello

I was listening Your discusion. I have practical problem for which I write my
own locking system:

Now A change zip and write all
B change adress and write

so zip is unchanged.

In pgsql I can solve it by SELECT FOR UPDATE but if user A goes for caffe
during his update
user B hangs for several minutes ...

Ther is no solution to check if record is in transaction ?

I usually prefer the following trick for preventing long locking times. On every
table I define a timestamp field which is updated every time the record is
written to the database. If a user edits a record (without locking) and commit his changes
the timestamp is returned from the client program unchanged. The program
reads the record again for update and compares the timestamp from the database
and the timestamp from the user program. If the timestamp has changed
in the meantime the record was updated from someone else and the
transaction will be rejected.

Regards Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I usually prefer the following trick for preventing long locking
times. On every
table I define a timestamp field which is updated every time the record is
written to the database. If a user edits a record (without
locking) and commit his changes
the timestamp is returned from the client program unchanged. The program
reads the record again for update and compares the timestamp from
the database
and the timestamp from the user program. If the timestamp has changed
in the meantime the record was updated from someone else and the
transaction will be rejected.

What happens if someone else updates the record *just* after the record is
reread for update and timestamp compared?

- Andrew

List z dnia: Sat, 17 Jun 2000, :

I usually prefer the following trick for preventing long locking
times. On every
table I define a timestamp field which is updated every time the record is
written to the database. If a user edits a record (without
locking) and commit his changes
the timestamp is returned from the client program unchanged. The program
reads the record again for update and compares the timestamp from
the database
and the timestamp from the user program. If the timestamp has changed
in the meantime the record was updated from someone else and the
transaction will be rejected.

and all the work done by the user (filling long form) gone out

What happens if someone else updates the record *just* after the record is
reread for update and timestamp compared?

It's ok. I do the same.
1. Update lock field for marked to transaction (everyone else need to know It
is locked)
2. begin transaction
3. fill form
4. write to database
5. end transaction
6. delete lock field contents

So applicaction should check if record is in transaction via select on lock
field

But it would be very nice to check it via system.
for example if SELECT FOR UPDATE on record being in transaction returns error.

Pawel

"Pawe� Dubin" wrote:

List z dnia: Sat, 17 Jun 2000, :

I usually prefer the following trick for preventing long locking
times. On every
table I define a timestamp field which is updated every time the record is
written to the database. If a user edits a record (without
locking) and commit his changes
the timestamp is returned from the client program unchanged. The program
reads the record again for update and compares the timestamp from
the database
and the timestamp from the user program. If the timestamp has changed
in the meantime the record was updated from someone else and the
transaction will be rejected.

and all the work done by the user (filling long form) gone out

Yes, but how often this really occurs? You may handle this case in your
application i.e. by showing the differences between the record in the
database and the changes submitted by the user. I made very good
experience in the past 10 years with this system even in big applications
where a large number of users were using the database
concurrently.

What happens if someone else updates the record *just* after the record is
reread for update and timestamp compared?

If you reread the record for updating nobody else should be able to
update the record. The record is locked. The method I suggested is
a way to keep the locking times as short as possible.

It's ok. I do the same.
1. Update lock field for marked to transaction (everyone else need to know It
is locked)

And what happens if a user is closing his browser i.e. in a web application?
What are you doing with the record marked as locked?

Regards Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I usually prefer the following trick for preventing long locking
times. On every
table I define a timestamp field which is updated every time the record is
written to the database. If a user edits a record (without
locking) and commit his changes
the timestamp is returned from the client program unchanged. The program
reads the record again for update and compares the timestamp from
the database
and the timestamp from the user program. If the timestamp has changed
in the meantime the record was updated from someone else and the
transaction will be rejected.

and all the work done by the user (filling long form) gone out

Yes, but how often this really occurs? You may handle this case in your
application i.e. by showing the differences between the record in the
database and the changes submitted by the user. I made very good
experience in the past 10 years with this system even in big applications
where a large number of users were using the database
concurrently.

If you reread the record for updating nobody else should be able to
update the record. The record is locked. The method I suggested is
a way to keep the locking times as short as possible.

It's ok. I do the same.
1. Update lock field for marked to transaction (everyone else need to know It
is locked)

And what happens if a user is closing his browser i.e. in a web application?
What are you doing with the record marked as locked?

I timed out locks in database and get phones from users
but it is little problem becouse my application is in java and browser
invokes finalyze method with unlock procedure.
I have only problems with browser crasches.

In my case locking problem is greater than in web apps (I thing)
because users frequently try to modify the same record
and I thing they will be not happy trying to fill form for the second, ....
once taking them about 2 minutes.

Pawel