Thread: Checking if a table locked from pl/pgsql
<div class="Section1"><p class="MsoNormal"><span class="SpellE"><font face="Arial" size="2"><span lang="ES-TRAD" style="font-size:10.0pt;font-family:Arial;mso-ansi-language:ES-TRAD">Hi</span></font></span><fontface="Arial" size="2"><spanlang="ES-TRAD" style="font-size:10.0pt;font-family:Arial; mso-ansi-language:ES-TRAD">,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="ES-TRAD" style="font-size: 10.0pt;font-family:Arial;mso-ansi-language:ES-TRAD"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US">Could anybody tell me if it is <span class="SpellE">posible</span> to check if<span class="GramE">a</span> if a table is being locked from pl/<span class="SpellE">pgsql</span> functions.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US">Regards</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;mso-ansi-language:EN-US">Jose Luis</span></font></div>
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
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. --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 >
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'llhave 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
On Wed, 27 Mar 2002, Jan Wieck wrote: > 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 Jan, I don't have this problem. A request was issued for an possible example as to WHY someone would need to determine who has a table locked. I am quite aware of the implications of table as well as row locking in a production situation. --Hal.
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 > -----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 >
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