Re: Checking if a table locked from pl/pgsql - Mailing list pgsql-interfaces

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
userscannot   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
stillreads   'Jose'.  That way,  an  administrator  could  deal  with  the   situation  that  Jose  went to lunch but
Eileenneeds to edit   4711 immediately, because the customer lost his checkbook and   the  account  needs  to  be
disabledNOW.  When returing from   lunch, Jose might click SAVE and get the message that  Arnold   broke his lock and
hehas 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
whatERP systems like SAP R/3 use.   And  it's been that way in R/2 or ADABAS (the app development   environment, not
thedatabase), back in the good old CICS and   UTM days on mainframes.
 


Jan

>
> > -----Original Message-----
> > From: Jan Wieck [mailto:janwieck@yahoo.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@Yahoo.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@postgresql.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@Yahoo.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@Yahoo.com #



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