Groups > comp.databases.ms-sqlserver > #582

Re: stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)?

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" <Mark.Schneider@dexag.at>
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	<j181ks$ug6$1@online.de> (permalink)
References	<j0pahg$8h7$1@online.de> <Xns9F3094E5B67Yazorman@127.0.0.1>
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

Show key headers only | View raw

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" <esquel@sommarskog.se> 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
>

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? "Mark Schneider" <Mark.Schneider@dexag.at> - 2011-07-27 17:23 +0200
  Re: stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? Erland Sommarskog <esquel@sommarskog.se> - 2011-07-28 00:03 +0200
    Re: stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? "Mark Schneider" <Mark.Schneider@dexag.at> - 2011-08-02 07:23 +0200

csiph-web