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

Mark Schneider (Mark.Schneider (AT) dexag (DOT) at) writes:
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:

That is a big challenge. Not the least:

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 (AT) sommarskog (DOT) 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

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 (AT) sommarskog (DOT) se> schrieb im Newsbeitrag
news:Xns9F3094E5B67Yazorman (AT) 127 (DOT) 0.0.1...