SQL Server deadlocks

Return to previous page

  • 4/7/2015
  • 127 Views

Comments

127 Views

SQL Server deadlocks

Last updated 3 years ago

Comments

Common

(Enter the content of this article below)

Advanced

 

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:


References


Contents
No sections defined
Try Evoq
For Free
Start Free Trial
Request
a Demo
See Evoq Live
REGISTER FOR THE WEBINAR
Why a Killer Website Starts with a Great CMS: in 30 minutes or less!
REGISTER NOW
Latest Blog
How to Make Advocate Marketing Work for You
Read It