When will XLOCK come out in a "nested transaction"?

Question

In SQL Server 2008, I know that there are no such things as nested transactions. They seem to exist. I also know that some experts strongly beg developers not to use available nested nested transactions. For example, here .

However, I have a case where I need to take XLOCK in a string in a specific nested procedure, but only for the duration of this procedure. It must be that castle, and I obviously want to reset the castle as soon as possible. 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:

• Is this a best practice example given that SQL Server doesn’t really make transactions?
• Will XLOCK be released on COMMIT TRAN Inner or only on COMMIT TRAN Outer? I guess the answer is yes.

Answer 1

• . "" ( TSQL). , : SQL Server : (26/30) (False)

• XLOCK . [: ]