Mutex Lock for Stored Procedure

Posted in 2010

Topics: Stored Procedures & SPL, Triggers, Constraints & Referential Integrity

James Brunskill — Tue, 27 Apr 2010 02:34:26 — source: IIUG Forums & Mailing Lists

Hi All, I'm writing a stored procedure that is triggered from an update to a table. I only want the database to run one copy of the proc at a time, eg. two copies running at the same time would be bad J I was thinking I could get an exclusive lock on a table somewhere, but I'm not aware of a way to release the lock again. The client sessions will stay connected after the procedure is finished. How do others handle this? Is there a best practice for ensuring you don't get multiple copies of a procedure running? Regards, James Brunskill Computer Engineer - Manufacturing Execution Systems Automation & Process Control Group NZ Technical Fonterra Fonterra Extn: 77808 Ext Line: +64 7 849 2411 x 77808 email: james.brunskill@fonterra.com <mailto:jarrod.teale@fonterra.com> DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/

↪ replying to James Brunskill

Art Kagel — Tue, 27 Apr 2010 03:38:42 — source: IIUG Forums & Mailing Lists

The only way is to take an exclusive lock on the triggering table during the update. That will single thread the updates and so the triggered procedure execution. Question: Why this rather odd sounding requirement? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 26, 2010 at 10:34 PM, James Brunskill < James.Brunskill@fonterra.com> wrote: > Hi All, > > I'm writing a stored procedure that is triggered from an update to a > table. > > I only want the database to run one copy of the proc at a time, eg. two > copies running at the same time would be bad J > > I was thinking I could get an exclusive lock on a table somewhere, but > I'm not aware of a way to release the lock again. > > The client sessions will stay connected after the procedure is finished. > > How do others handle this? > > Is there a best practice for ensuring you don't get multiple copies of a > procedure running? > > Regards, > > James Brunskill > > Computer Engineer - Manufacturing Execution Systems > > Automation & Process Control Group > > NZ Technical > > Fonterra > > Fonterra Extn: 77808 > > Ext Line: +64 7 849 2411 x 77808 > > email: james.brunskill@fonterra.com > <mailto:jarrod.teale@fonterra.com> > > DISCLAIMER: > This email contains confidential information and may be legally privileged. > If > you are not the intended recipient or have received this email in error, > please notify the sender immediately and destroy this email. > You may not use, disclose or copy this email or its attachments in any way. > Any opinions expressed in this email are those of the author and are not > necessarily those of the Fonterra Co-operative Group. > http://www.fonterra.com/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636b2bcbb06450e04852fa120

↪ replying to Art Kagel

James Brunskill — Tue, 27 Apr 2010 04:13:28 — source: IIUG Forums & Mailing Lists

Doesn't the exclusive lock remain for the remainder of the session? I'm basically doing a pivot table. Data is provided by the client using and update for each id. Eg. Id Val Updated_dt 1 0.3 2010-04-27 16:06:05 2 0.4 2010-04-27 16:06:05 3 0.5 2010-04-27 16:06:05 I want to record the updates in another table like this: Timestamp val1 val2 val3 2010-04-27 16:04:05 0.1 0.2 0.3 2010-04-27 16:05:05 0.2 0.3 0.4 2010-04-27 16:06:05 0.3 0.4 0.5 Of course I want only the first update with the new timestamp to do an insert into the second table.It seems unlikely there would be a collision but want to make sure... James -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, April 27, 2010 3:39 PM To: ids@iiug.org Subject: Re: Mutex Lock for Stored Procedure [19861] The only way is to take an exclusive lock on the triggering table during the update. That will single thread the updates and so the triggered procedure execution. Question: Why this rather odd sounding requirement? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 26, 2010 at 10:34 PM, James Brunskill < James.Brunskill@fonterra.com> wrote: > Hi All, > > I'm writing a stored procedure that is triggered from an update to a > table. > > I only want the database to run one copy of the proc at a time, eg. two > copies running at the same time would be bad J > > I was thinking I could get an exclusive lock on a table somewhere, but > I'm not aware of a way to release the lock again. > > The client sessions will stay connected after the procedure is finished. > > How do others handle this? > > Is there a best practice for ensuring you don't get multiple copies of a > procedure running? > > Regards, > > James Brunskill > > Computer Engineer - Manufacturing Execution Systems > > Automation & Process Control Group > > NZ Technical > > Fonterra > > Fonterra Extn: 77808 > > Ext Line: +64 7 849 2411 x 77808 > > email: james.brunskill@fonterra.com > <mailto:jarrod.teale@fonterra.com> > > DISCLAIMER: > This email contains confidential information and may be legally privileged. > If > you are not the intended recipient or have received this email in error, > please notify the sender immediately and destroy this email. > You may not use, disclose or copy this email or its attachments in any way. > Any opinions expressed in this email are those of the author and are not > necessarily those of the Fonterra Co-operative Group. > http://www.fonterra.com/ > > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636b2bcbb06450e04852fa120 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.