Skip site navigation (1) Skip section navigation (2)

Re: User Feedback

From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
To: pgsql-interfaces(at)postgresql(dot)org
Cc: terry(at)esc1(dot)com
Subject: Re: User Feedback
Date: 2003-03-04 09:58:17
Message-ID: 3E6478B8.DD5596@rodos.fzk.de (view raw or flat)
Thread:
Lists: pgsql-interfaces
>
> We are porting an application from Progress to PostgreSQL. I'm
> experimenting with the UI and issues of record locking. We are using
> libpq-fe and C for the X-Windows front end. I need some suggestions on

> how to get input back to the user when their update is waiting on the
> release of a transaction where another user is updating the same
record.
> I thought that the C routines for Asynchronous queries would work. I
> have finally gotten the asynchronous routines working such that at
least
> the UI is being updated during the wait, but still the user doesn't
know
> what is going on. Can someone point me in a sane direction regarding
> this issue? ;-)
>
There was a similar thread on [HACKERS] recently.
You'll find below what I've recorded.
BTW, I think [INTERFACES] is the wrong list for questions like this.

Regards, Christoph

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Subject: [HACKERS] Can pessimistic locking be emulated?
Date: Wed, 26 Feb 2003 12:26:20 -0500

I am trying to emulate a pessimistic locking system you would find in an

old school database file system, for example cobol.  Generally, when a
cobol program tries to read a record that is locked by somebody else,
the read fails and either a message is displayed by the user or a error
handling procedure is executed.  I would like to emulate this behavior
for legacy code while using mvcc for newer procedures I write.

4 questions:
1. Can you query if a tuple is locked by another transaction (the
documentation unclearly suggests this can't be done via the pg_lock
view) before executing select for update...?
2. If so, is this reasonable efficient to do, i.e. straight join on
oid/xid?
3. If so, is this possible to query without a race condition regarding
the lock status?
4. If so, is this likely to be possible in future versions of postgres
without non-trivial changes?

In other words, if User B attempts to select for update a record that
user A has selected for update, it would be nice if User B's query would

fail with a NOTICE to act upon.

Thanks in advance,
Merlin

From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 13:33:11 +0100

>
> I am trying to emulate a pessimistic locking system you would find in
an
> old school database file system, for example cobol.  Generally, when a

> cobol program tries to read a record that is locked by somebody else,
> the read fails and either a message is displayed by the user or a
error
> handling procedure is executed.  I would like to emulate this behavior

> for legacy code while using mvcc for newer procedures I write.
>
> 4 questions:
> 1. Can you query if a tuple is locked by another transaction (the
> documentation unclearly suggests this can't be done via the pg_lock
> view) before executing select for update...?
> 2. If so, is this reasonable efficient to do, i.e. straight join on
> oid/xid?
> 3. If so, is this possible to query without a race condition regarding

> the lock status?
> 4. If so, is this likely to be possible in future versions of postgres

> without non-trivial changes?
>
> In other words, if User B attempts to select for update a record that
> user A has selected for update, it would be nice if User B's query
would
> fail with a NOTICE to act upon.
>
No idea if this is of any help, but you may have a look into
PostgreSQL 7.3 Documentation
3.4. Run-time Configuration
STATEMENT_TIMEOUT (integer)
Aborts any statement that takes over the specified number of
milliseconds. A value of zero turns off the timer.

Regards, Christoph

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Subject: RE: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 10:40:20 -0500

That's my fallback position.  Obviously, this will lead to false
positives depending on server load.  In my case, I'm targeting between
30-50 users so its likely to throw timeouts for various reasons other
than locks even though my queries of interest are generally select a
from b where id = c type of thing.  This is a kludgy solution but its
still better than writing cobol.

The bigger issue is that a timeout will not return the reason the query
timed out.  There are cases where I would like to run a select for
update over a range of records and handle the locked records and
unlocked records differently.  A query that could match locked oids vs
the oids I am interested in would be super.  I could then aggregate my
select for updates into larger queries and reap massive performance
gains.

Another way of putting it is this: waiting for your select to timeout is

kind of like parking in Manhattan: you back your car up until you hit
the next car.  I would sort of like to, uh, look in the rear view mirror

first.

Merlin

From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 17:04:24 +0100

>
> That's my fallback position.  Obviously, this will lead to false
> positives depending on server load.  In my case, I'm targeting between

> 30-50 users so its likely to throw timeouts for various reasons other
> than locks even though my queries of interest are generally select a
> from b where id = c type of thing.  This is a kludgy solution but its
> still better than writing cobol.
>
> The bigger issue is that a timeout will not return the reason the
query
> timed out.  There are cases where I would like to run a select for
> update over a range of records and handle the locked records and
> unlocked records differently.  A query that could match locked oids vs

> the oids I am interested in would be super.  I could then aggregate my

> select for updates into larger queries and reap massive performance
> gains.
>
> Another way of putting it is this: waiting for your select to timeout
is
> kind of like parking in Manhattan: you back your car up until you hit
> the next car.  I would sort of like to, uh, look in the rear view
mirror
> first.
>
I see your point.
> 1. Can you query if a tuple is locked by another transaction (the
> documentation unclearly suggests this can't be done via the pg_lock
> view) before executing select for update...?
Where did you find this?

Regards, Christoph

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Subject: RE: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 11:43:09 -0500

I was referring to 10.3 in the administrator's guide, regarding the
pg_lock view.  According to the documentation, the view only contains
table level locks.  However, the view also contains an xid for
transactions.  The unclear part, at least to me, was what the role of
the xid was in the view and if it could be used to produce a list of
locked tuples somehow.  The xid is referred to as a 'lockable object'.
I wasn't sure of the xid's role in the mix.  I see now how it all works
together.

In my case, being able to view outstanding row level locks would be
enormously useful.  I'm assuming this is not possible for structural or
performance reasons.  I'm aware of the possible nasty side affects of
repeated query calls to the lock manager.  I'm also aware what I'm
asking about may be folly or silly, my understanding of how mvcc and
transactions work together is not very refined.

A curious thought struck me: does the pg_lock view follow the mvcc
rules, i.e. if you query the pg_lock view inside a transaction, and an
external effect introduces new locks into the server are you able to see

those locks?

Merlin


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 12:37:54 -0500

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:
> In my case, being able to view outstanding row level locks would be
> enormously useful.

The only way to do that would be to grovel through every table in the
database, looking for rows that are marked locked by transactions that
are still alive.

> A curious thought struck me: does the pg_lock view follow the mvcc
> rules,

No, not really.  If it did I don't think it'd be real useful ...

   regards, tom lane

From: Rod Taylor <rbt(at)rbt(dot)ca>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: 27 Feb 2003 12:55:49 -0500

> In my case, being able to view outstanding row level locks would be
> enormously useful.  I'm assuming this is not possible for structural
or

Agreed -- but they're stored on the row themselves.  You might be able
to write a function which executes dirty reads on the table and tells
you if the row is locked or not, but it's not going to be simple.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 15:02:39 -0500

Rod Taylor <rbt(at)rbt(dot)ca> writes:
> Agreed -- but they're stored on the row themselves.  You might be able

> to write a function which executes dirty reads on the table and tells
> you if the row is locked or not, but it's not going to be simple.

Actually, I don't think you need a dirty read at all.  A locked row
can't be deleted as well (because there's only one xmax slot), so if you

can see it (ie, you think its xmin is committed) then you can in
principle find out whether it's locked or not.  We just don't expose the

info at the moment.  (You can see xmax at the user level, but you can't
easily tell if xmax is trying to delete the row or just lock it, because

you don't have access to the infomask bit that would tell you.)

   regards, tom lane

From: Rod Taylor <rbt(at)rbt(dot)ca>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: 27 Feb 2003 15:07:50 -0500

On Thu, 2003-02-27 at 15:02, Tom Lane wrote:
> Rod Taylor <rbt(at)rbt(dot)ca> writes:
> > Agreed -- but they're stored on the row themselves.  You might be
able
> > to write a function which executes dirty reads on the table and
tells
> > you if the row is locked or not, but it's not going to be simple.
> Actually, I don't think you need a dirty read at all.  A locked row

I see.  That will make it quite a bit easier then.  Perhaps I'll write a

function sometime.  It would make it useful for fetching things out of a

persistent work queue.  Right now I deal with userlocks -- but those can

be clumsy.

Rod Taylor <rbt(at)rbt(dot)ca>


From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 17:14:44 -0500

This directly answers my question (wasn't previously aware that xid
could be queried out in such a useful fashion).  Not only does this
accomplish what I need, but now allows me to not use select ... for
update and stick with a transaction based locking mechanism.   The 'Why'

isn't that interesting in my case: merely that the knowledge that the
record is involved in a transaction is enough.

I've felt for a while that the descriptions of transactions, mvcc, and
row level locking in the official docs could use a little bit better
treatment (selfishly motivated, I could never figure them completely
out!) but this is the wrong list for that :).

Many thanks to the hackers for helping me with my problem.
Merlin


From: Josh Berkus <josh(at)agliodbs(dot)com>
Subject: Re: [HACKERS] Can pessimistic locking be emulated?
Date: Thu, 27 Feb 2003 15:16:40 -0800

Merlin,

Just as a suggestion:  In most of my applications, we have a security
layer
which is implemented through server-side functions.  These functions
keep a
table updated which contains:

lock_table
record_id
lock_user
time_locked

This allows us to avoid nasty "your update cannot be processed"-type
error
messages by showing the user up front which records are locked, as well
as
allowing the admin to decide when locks should "time out".

I tend to find in general that database locking mechanisms are a very
poor
locking strategy for a good UI.


From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>

>
> Just as a suggestion:  In most of my applications, we have a security
layer
> which is implemented through server-side functions.  These functions
keep a
> table updated which contains:
>
> lock_table
> record_id
> lock_user
> time_locked
That's an excellent and even portable idea.
>
> This allows us to avoid nasty "your update cannot be processed"-type
error
> messages by showing the user up front which records are locked, as
well as
> allowing the admin to decide when locks should "time out".
>
> I tend to find in general that database locking mechanisms are a very
poor
> locking strategy for a good UI.
>
True. But you circumvented it elegantly.

From: Josh Berkus <josh(at)agliodbs(dot)com>

Christoph,

> > table updated which contains:
> >
> > lock_table
> > record_id
> > lock_user
> > time_locked
>
> That's an excellent and even portable idea.

Thanks.  It's actually part of an intranet framework that my team uses
to
build all of our browser-based applications.  We've been casually
discussing
for the last few months whether to keep the framework to ourselves, or
to
take it Open Source.   Mostly we've been way too busy to make a serious
decision ....




pgsql-interfaces by date

Next:From: Wei WengDate: 2003-03-04 20:11:40
Subject: Is this a bug?
Previous:From: Terry Lee TuckerDate: 2003-03-03 12:39:35
Subject: User Feedback

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group