Page History: SQL Server deadlocks

Compare Page Revisions



Older Revision - Back to Page History - Newer Revision


Page Revision: 2011/09/21 03:27 PM


Background»

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

AssemblyVersion: 6.0.1
PortalID: -1
PortalName: 
UserID: -1
UserName: 
ActiveTabID: -1
ActiveTabName: 
RawURL: /SynchronizeCache.aspx?message=aBC4Cfo9zep3vqcp-UAIWw%3d%3d&data=MV5yVet9vws%3d
AbsoluteURL: /SynchronizeCache.aspx
AbsoluteURLReferrer: 
UserAgent: 
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: 5fed256f-5d88-4515-919e-615a8ae3bfc4
InnerException: Unhandled Error: 
FileName: 
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Data.SqlClient.SqlConnection.OnError
StackTrace: 
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:
  • Locks
  • 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.

Diagnosing deadlocks»

The most common way to diagnose deadlocks are via DBCC SQL Profiler Extended properties

References»



Copyright 2014 by DNN Corp | Terms of Use | Privacy | Design by Parker Moore Design