Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!weretis.net!feeder4.news.weretis.net!npeer.de.kpn-eurorings.net!npeer-ng0.de.kpn-eurorings.net!feed.news.schlund.de!schlund.de!news.online.de!not-for-mail From: "Mark Schneider" Newsgroups: comp.databases.ms-sqlserver Subject: Re: stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? Date: Tue, 2 Aug 2011 07:23:17 +0200 Organization: 1&1 Internet AG Lines: 45 Message-ID: References: NNTP-Posting-Host: p4fe4032b.dip0.t-ipconnect.de X-Trace: online.de 1312262621 31238 79.228.3.43 (2 Aug 2011 05:23:41 GMT) X-Complaints-To: abuse@einsundeins.com NNTP-Posting-Date: Tue, 2 Aug 2011 05:23:41 +0000 (UTC) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 X-RFC2646: Format=Flowed; Original Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:582 Until now I only tried to use transactions and commits (to no avail in my scenario). I'll try your suggestion with save transaction and rollback as soon as I get back to my database server (it just locked me out of all VPN/RDP sessions...). Thank you for your input! "Erland Sommarskog" schrieb im Newsbeitrag news:Xns9F3094E5B67Yazorman@127.0.0.1... > Mark Schneider (Mark.Schneider@dexag.at) writes: >> -> Description (short): <------------------ stored procedure calling >> sub-procedure with updlock select. How could I release the updatelog if >> sub-proc is finished (calling proc still running)? >> MS SQL Server 2000. > > Locks are held until the transaction is committed. Thus, you need to > break up the transaction in two, if you want to release the UPDLOCK. > > At the same time: > >> Under no circumstances two users should ever get the same token. > > That is a big challenge. Not the least: > >> The file structure, fields or logic of table B cannot be modified by me. > > You could perform SAVE TRANSACTION before calling procB and then rollback > after the call. That might release the lock. But can you then guarantee > that a concurrent user gets the same token? Most likely he will, depending > on how procB is implemented. > > Since I don't see the code, I cannot say with certainty that it's im- > possible. But it sounds like an uphill battle. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >