When would an XLOCK in a "nested transaction" be released?
In SQL Server 2008, I know there is no such thing as nested transactions. These only appear to exist. I also know that some experts strongly implore developers not to use the available pseudo nested transactions. For examp开发者_StackOverflow社区le, here.
However, I have a case where I need to take an XLOCK on a row in a particular nested procedure, but only for the duration of that procedure. It has to be that lock and I obviously want to drop the lock ASAP. In pseudo code:
...outer procedure
BEGIN TRAN Inner
EXEC InnerProcedure
...InnerProcedure...
BEGIN TRAN Inner
SELECT ... WITH (XLOCK, ROWLOCK)
COMMIT TRAN Inner
-- NEVER rollback here; get the outer procedure to do this
...End InnerProcedure...
COMMIT TRAN Outer
My questions are:
- Is this a best practices pattern considering that SQL Server does not truly nest transactions?
- Will the XLOCK be released at COMMIT TRAN Inner, or only at COMMIT TRAN Outer? I assume the answer is yes.
No. Do not use 'nested' transactions (even in TSQL). At best, they won't work as you think: A SQL Server DBA myth a day: (26/30) nested transactions are real (False)
The XLOCK will be released at the outer transaction scope. [Updated: to include Remus's advice]
精彩评论