Re: Checking if a table locked from pl/pgsql

Jose Luis LG wrote:
> In my case I have several clients updating a table at the same time with
> each client having several threads updating tables. With what mechanism
> other than locks can you insure for example that at any instant
> duplicate keys are not generated?
>
> The lock in the application is released as soon as the thread finishes
> work.
> Jose

As said, with an advisory lock. Let's make an example, we
edit customer account 4711.

Before we enter the EDIT screen, we try to insert a row into
a central lock table:

INSERT INTO locktab (lockkey, holder, since)
VALUES ('CUST.4711', 'Jose', CURRENT_TIMESTAMP);

There is a unique constraint on lockkey, so if that INSERT
fails with a duplicate key error, we don't enter the EDIT
screen but tell Jose "Sorry, 4711 is locked, try again
later".

If it succeeds, we read the current account information and
display the EDIT screen. But we commit the DB transaction
before displaying it.

When Jose leaves the EDIT screen (aborting the EDIT), we
simply DELETE the locktab entry.

When he modifies the account information and clicks on SAVE,
we UPDATE account 4711 and DELETE the locktab entry then.

This way, all the DB transactions are very short, don't hold
any resources over user interaction, but still 2 users cannot
edit the same account at the same time.

In addition, we could let the application check on SAVE
first, if the entry in locktab is still there and still reads
'Jose'. That way, an administrator could deal with the
situation that Jose went to lunch but Eileen needs to edit
4711 immediately, because the customer lost his checkbook and
the account needs to be disabled NOW. When returing from
lunch, Jose might click SAVE and get the message that Arnold
broke his lock and he has to restart the EDIT from scratch.

Your problem is, that you try to do an entire business
process in one database transaction. Business processes are
sometimes called transactions on the application design
level, what's confusing. There is no reason why a business
process shouldn't span multiple database transactions. The
above mechanism is exactly what ERP systems like SAP R/3 use.
And it's been that way in R/2 or ADABAS (the app development
environment, not the database), back in the good old CICS and
UTM days on mainframes.

Jan

>
> > -----Original Message-----
> > From: Jan Wieck [mailto:janwieck(at)yahoo(dot)com]
> > Sent: miércoles, 27 de marzo de 2002 16:37
> > To: Hal Davison
> > Cc: Jan Wieck; Jose Luis LG; 'PostgreSQL-interfaces'
> > Subject: Re: Checking if a table locked from pl/pgsql
> >
> > Hal Davison wrote:
> > >
> > > In an operational sense, if a user is updating a table for some
> > accounting
> > > function then decides to go to lunch leaving the table locked.
> >
> > Exactly what I expected. You have a severe design flaw in
> > your application. Database transactions have to be short and
> > never held over actions that could infinitely block, such as
> > user interaction, period!
> >
> > You need application level advisory locks. The abuse of
> > database locks for that will not get you very far. Your
> > application will not scale and suffer from problems like the
> > one you already face.
> >
> > Ever thought about adding a WEB interface to that
> > application? If a user editing some account needs to hold a
> > DB lock all the time, you'll not implement it with
> > Apache/PHP, that's for sure already. And you'll have to make
> > alot of painfull pushups to do it with any other WEB server
> > technology.
> >
> >
> > Jan
> >
> > >
> > > --Hal.
> > >
> > > ===========================================================
> > > Hal Davison Internet Petroleum Distribution
> > > Davison Consulting LSE Linux V1.22
> > > 6850 Myakka Valley Tr PostgreSQL 7.03 - Sun Forte - JAVA
> > > Sarasota, Florida 34241 Phone: (941) 921-6578
> > > http://www.faams.net FAX: (941) 924-7135
> > > ===========================================================
> > >
> > > On Mon, 25 Mar 2002, Jan Wieck wrote:
> > >
> > > > Jose Luis LG wrote:
> > > > > Hi,
> > > > >
> > > > > Could anybody tell me if it is posible to check if a if a table
> is
> > being
> > > > > locked from pl/pgsql functions.
> > > >
> > > > Yes, it is not.
> > > >
> > > > That you ask for such a functionality leads to the question,
> > > > why can a lock exist long enough that you want to check at
> > > > all?
> > > >
> > > >
> > > > Jan
> > > >
> > > > --
> > > >
> > > >
> >
> #======================================================================#
> > > > # It's easier to get forgiveness for being wrong than for being
> right.
> > #
> > > > # Let's break this rule - forgive me.
> > #
> > > > #==================================================
> JanWieck(at)Yahoo(dot)com
> > #
> > > >
> > > >
> > > >
> > > > _________________________________________________________
> > > > Do You Yahoo!?
> > > > Get your free @yahoo.com address at http://mail.yahoo.com
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)-------------------------
> > --
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > > http://archives.postgresql.org
> > > >
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
> >
> > --
> >
> >
> #======================================================================#
> > # It's easier to get forgiveness for being wrong than for being right.
> #
> > # Let's break this rule - forgive me.
> #
> > #================================================== JanWieck(at)Yahoo(dot)com
> #
> >
> >
> >
> >
> > _________________________________________________________
> >
> > Do You Yahoo!?
> >
> > Get your free @yahoo.com address at http://mail.yahoo.com
> >
>

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com