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

Groups > comp.databases.ms-sqlserver > #572 > unrolled thread

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

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

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

Hi,

-> 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.

-> Description (longer): <------------------
I have a stored procedure (procA) for statistics that are summarized in a 
table (tableA).
It's calling a sub-procedure (procB, via execute)  to get a key token (ID) 
from another table (tableB).

The file structure, fields or logic of table B cannot be modified by me.
It's populated by a SAP system.

- procA is running about 20 minutes (it's ok, millions of rows and many 
statistics)
- procB uses an updlock to retrieve a key token and increment it by 1 
(selecting and updating tableB)

-> Problem(s): <------------------
As long as procA is running no other user/process can access procB to 
retrieve the next free key token and increment it.
Under no circumstances two users should ever get the same token.

-> Looking for: <------------------
A solution to release the update lock of the sub-procedure while the caller 
is still running?

How would I go about that?

Mark

[toc] | [next] | [standalone]

#577

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

[toc] | [prev] | [next] | [standalone]

#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" <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
> 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web