Thread: resolving locking conflicts
I am using Postgres 6.5 on Linux through the libpq interface I have a situation where an existing record can be opened by one user, updated or rolled back. While the first user has the record I need to make sure no other user can open it. I use SELECT .... FOR UPDATE This works but the second process blocks (looking to the user like a hung program), until the first process releases the record. Since updating a record can take some time the blocking could be there for some time. I would like the second SELECT .... FOR UPDATE to fail with an error I can catch telling me of a conflict (and hopefully which process has the lock), allowing me to backout gracefully with a consolatory message to the user. I would like normal SELECTs (without FOR UPDATE) to work an normal. I think that this is an option in pg_options but I'm not sure. I have looked in the source (lock.c and lmgr.c) to try and figure out what to do. I found something called "user locks" which looks promising but I'm still not sure. Help ! Trevor ********************************************************************** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk on +44 (0) 01908 256 050. Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 5HJ. **********************************************************************
To the best of my knowledge, normal SELECTs should continue to work and should be able to see the data as it appeard before your "locking" process made any changes. Oracle has the syntax SELECT... FOR UPDATE NOWAIT to do what you want (except it doesn't tell you who has the lock). Perhaps Postgres needs something similar? At 01:04 PM 7/14/99 +0100, you wrote: >I am using Postgres 6.5 on Linux through the libpq interface > >I have a situation where an existing record can be opened by one user, >updated or rolled back. While the first user has the record I need to make >sure no other user can open it. I use > >SELECT .... FOR UPDATE > >This works but the second process blocks (looking to the user like a hung >program), until the first process releases the record. Since updating a >record can take some time the blocking could be there for some time. > >I would like the second SELECT .... FOR UPDATE to fail with an error I can >catch telling me of a conflict (and hopefully which process has the lock), >allowing me to backout gracefully with a consolatory message to the user. I >would like normal SELECTs (without FOR UPDATE) to work an normal. > >I think that this is an option in pg_options but I'm not sure. I have looked >in the source (lock.c and lmgr.c) to try and figure out what to do. I found >something called "user locks" which looks promising but I'm still not sure. > >Help ! > >Trevor >********************************************************************** >This message (including any attachments) is confidential and may be >legally privileged. If you are not the intended recipient, you should >not disclose, copy or use any part of it - please delete all copies >immediately and notify the Hays Group Email Helpdesk on >+44 (0) 01908 256 050. > >Any information, statements or opinions contained in this message >(including any attachments) are given by the author. They are not >given on behalf of Hays unless subsequently confirmed by an individual >other than the author who is duly authorised to represent Hays. > >A member of the Hays plc group of companies. >Hays plc is registered in England and Wales number 2150950. >Registered Office Hays House Millmead Guildford Surrey GU2 5HJ. >********************************************************************** > > >
An alternative strategy is to use logical locks, rather than
physical locks. To do this, add a column to your table to
indicate whether or not a given record is locked. You might, for
example, leave the column NULL when the record is not locked, and
set it to the id of the locking user when it is locked.
Then, to lock a record do something like:
BEGIN;
SELECT locked, ... FROM table WHERE ...;
if (locked is not NULL)
{Error("row locked by user %s\n", locked)
}
else
{UPDATE table SET locked = <current_user_id>;
}
COMMIT;
You can expand on this by adding a timestamp to indicate when
the lock was created and allow it to be broken if held "too long".-rwhit
> Date: Wed, 14 Jul 1999 13:04:06 +0100
> From: "Burgess, Trevor - HMS" <Trevor.Burgess@haysdx.co.uk>
> Subject: resolving locking conflicts
>
> I am using Postgres 6.5 on Linux through the libpq interface
>
> I have a situation where an existing record can be opened by one user,
> updated or rolled back. While the first user has the record I need to make
> sure no other user can open it. I use
>
> SELECT .... FOR UPDATE
>
> This works but the second process blocks (looking to the user like a hung
> program), until the first process releases the record. Since updating a
> record can take some time the blocking could be there for some time.
>
> I would like the second SELECT .... FOR UPDATE to fail with an error I can
> catch telling me of a conflict (and hopefully which process has the lock),
> allowing me to backout gracefully with a consolatory message to the user. I
> would like normal SELECTs (without FOR UPDATE) to work an normal.
>
> I think that this is an option in pg_options but I'm not sure. I have looked
> in the source (lock.c and lmgr.c) to try and figure out what to do. I found
> something called "user locks" which looks promising but I'm still not sure.
>
> Help !
>
> Trevor