Page History: SQL Server deadlocks
Compare Page Revisions
Page Revision: 2011/09/21 03:27 PM
Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
Deadlocks can use up SQL Server’s resources, especially CPU power, wasting it unnecessarily, so if your application see's error in the following format you should attempt to diagnose the deadlock issue and resolve it
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
InnerException: Unhandled Error:
Message: System.Exception: Unhandled Error: ---> System.Data.SqlClient.SqlException: Transaction (Process ID 60) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior
Common causes of deadlocks
DotNetNuke itself uses fine-grained sql actions that typically execute extremely quickly so it's very, very rare to see any deadlock issues related to DotNetNuke (typically only when the database server is under extreme load). More commonly deadlock issues are caused by 3rd party modules/custom code running long running database actions e.g. generating monthly reports etc.
The most common causes of deadlocks include:
- long running or incomplete transactions e.g. database operations within a BEGIN TRAN...COMMIT/ROLLBACK TRAN block, or stored procedures where a BEGIN TRAN occurs but the transaction is never committed or rolled back
- Parallel query execution-related resources
- Multiple Active Result Sets (MARS) resources.
The most common way to diagnose deadlocks are via