Stored Procedures programmed to fire in separate serial transactions appear to execute simultaneously

Question

I have a .net application that has to execute a series of steps in a job. Each step lives in its own transaction, and all tasks are executed serially (no explicit async). The last two tasks are a data transform, and a data retrieval of the transformed data. We are getting deadlock errors from these last two steps, because the last step looks like it's trying to get at data that the previous step still has locked.

Is it possible that sql will report that execution is finished, before it actually is? Could a lock persist across a completed transaction? Is there anything else I should look for to understand what is happening?

By serial I mean one after another. Each transaction is completed before the next one starts. If any failures occur, that transaction is rolled back and the entire process halted to allow for a user retry.

Answer 1

Are you sure that a COMMIT or ROLLBACK is being issued before the last steps? Sql server will report completion for any query batch - but the end of a query batch does not necessarily mean the transaction has ended, unless that batch issued a COMMIT or ROLLBACK.

Locks should all be released at the end of a transaction, but be aware that transactions can be nested (http://msdn.microsoft.com/en-us/library/ms189336%28v=SQL.90%29.aspx)

Also, it may be that your deadlocks are from the same process executing concurrently - that is even though your process may not deadlock when run by itself, if it is running simultaneously (for example with multiple users) you could still get a deadlock, or if you have other different processes that also acquire the same locks. One way this can be mitigated by always acquiring your locks in the same order, but there are many other such strategies that can be used depending on your situation.

Edit: you may also want to check out this link: http://msdn.microsoft.com/en-us/library/ms178104%28v=SQL.90%29.aspx which gives some examples and information on how to determine what is causing the deadlock.

Answer 2

Usually that happens when you don't Dispose all involved disposable objects, such as Transactions and Connections.

Answer 3

What exactly do you mean by 'serial transactions'? Two transactions executing serially, or two transactions executing under serialization isolation level? Is your C# program guaranteed to be executing only one workflow at a time, or is there any concurrency between two instances of the program (common in ASP.Net, WCF, WF and many more 'hosted code' scenarios)?

Another common mistake would be if you have uncommitted steps in the workflow. Say Step1 starts a transaction on ConnecitonA, and then completes w/o committing. As the logic moves on to Step2, executes a new transaction on ConnectionB, it may well conflict and deadlock with the uncommitted work left from Step1. This would be actual parallelism in the database layer from complete serialized execution in the client.

Is it possible that sql will report that execution is finished, before it actually is?

No

Could a lock persist across a completed transaction?

No (for all practical reasons, session scope locks only apply to database use locks or applocks).

Is there anything else I should look for to understand what is happening?

The server will have plenty of means to troubleshoot this. Between Profiler, deadlock graph info, sys.dm_exec_requests and sys.dm_tran_locks, all the info you need is there.