Actually I would like to find answers for the following situation: Let's say I have a stored procedure (Usp1) which at some moment calls an other stored procedure (usp2). Each of the stored procedures is a transaction. So there are nested transactions.

  1. Can a deadlock occur in nested transactions while modifying the same table from 2 different sql server sessions?
  2. Are there any possibilities of conflicts which leads to deadllock between the inner and outer transactions of the same stored procedure in a single session?

Examples would be really appreciated.

  • 1
    Different sessions are needed for both blocking and deadlocking. – Dan Guzman May 26 '17 at 21:53
  • 1
    SQL Server doesn't actually have a concept of "nested transactions"... If you do BEGIN TRAN 1000 times. This will increase the @@trancount to 1000 but there is still only one transaction. You can then do 900 COMMITs to decrease the @@trancount to 100. Then, a single ROLLBACK will roll back EVERYTHING, including what you thought was "committed". – AMtwo May 26 '17 at 21:54
  • 2
    You're asking if a session will deadlock itself in this situation? A session will only deadlock on itself due to certain scenarios of parallelism (like what happens when using MERGE), but not due to something that happened in a prior statement. – AMtwo May 26 '17 at 21:57
  • 1
    @DanGuzman intra-query deadlocks within a single session do happen--just not in the scenario the OP describes. – AMtwo May 26 '17 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.