Re: Lock record - Mailing list pgsql-general

Andrew Sullivan wrote:

> On Thu, Jun 15, 2000 at 12:45:52AM +0200, Jan Wieck wrote:
>
> >     Believe  it  or  not,  but  holding  pure   DB   locks   over
> >     "interaction"  in  an  interactive application isn't what you
> >     really want! The user might go for coffee, and such long time
> >     locks  are  not  what  the  locking mechanism of databases is
> >     intended for - so it's not optimized for this kind of  abuse!
>
> Allow me to echo the above sentiment.  Our library automation system is
> built on a PICK back end (UniVerse), and the implementation locks any record
> that is in current use.  A good thing, that, in so far as you don't want,
> say, two people writing to the same patron record at the same time.  Problem
> is, patrons are frequently checking books out while someone else is checking
> in the items the patron had out before.  Kablooey.
>
> What's supposed to happen, of course, is that the ckeck-in or check-out
> clerk gets a message, "patron file is locked -- wait or quit?" In practice,
> there are too many cases where lock contention is not handled properly, and
> both terminals get locked up.  This is often a pain to resolve; I spend
> quite a bit of time just trying to figure out where the lock is coming from.
> And this doesn't even begin to touch the times where a staff member was
> helping a patron look at his/her holds (or whatever), and then just left the
> session logged in to that person's record (which is a problem of bad user
> behaviour, yes, but knowing that doesn't help when you're trying to break
> someone's 20 year bad habit).
>
> Use transactions.  Much better than locking.
>
> --
> Andrew Sullivan                                      Computer Services
> <sullivana@bpl.on.ca>                        Burlington Public Library
> +1 905 639 3611 x158                                   2331 New Street
>                                    Burlington, Ontario, Canada L7R 1J4

Why is a transaction better than a lock ? I have worked with locks without
transactions
and locks with transactions, and transactions alone. When you have two
transactions
on the same record, say

User A in program P
Begin transaction

A little time later
User B in program P
Begin transaction

Then user A inhibits user B from going further until transaction A has been
completed. If this person goes for a coffee, then transaction B will also be
held
up indefinitely.

With the systems I have worked with, the only thing you can do with
transactions
is begin them and commit or rollback, while the locks would be used to get hold

of the needed data. Further functionality of locks included always the
possibility
of specifying a time-out and getting the name of the user who currently holds
the lock.

Jurgen Defurne
defurnj@glo.be