Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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)?

Show all headers | 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