开发者

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:

  1. Is this a best practices pattern considering that SQL Server does not truly nest transactions?
  2. Will the XLOCK be released at COMMIT TRAN Inner, or only at COMMIT TRAN Outer? I assume the answer is yes.


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

  2. The XLOCK will be released at the outer transaction scope. [Updated: to include Remus's advice]

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜