Get Rid of Deadlocks

Published Mar 23 2019 12:23 PM 439 Views
Microsoft
First published on TECHNET on Dec 12, 2011

Locks are used by relational database management systems to increase user concurrency (more users) while guaranteeing data consistency. A deadlock is when two locks interfere each other, and is caused by one process locking a row, page, partition, or table while it waits for another row, page, partition, or table to become available, but the one it's waiting on is locked by another process that's waiting for what the first process has locked. When that happens, SQL Server will detect it and roll back one of them (the one that requires the least work to redo will be the one that gets rolled back).


Occasional deadlocks are common, but it's uncommon for them to occur so often that they become a problem. Deadlocking does become a problem sometimes, though, and when my customers run into it, they usually ask for my assistance. While there are many articles about how to detect deadlocks, I haven't found much info about how to eliminate them, reduce them, or minimize their impact, so I'm going to list all the alternatives I'm aware of here, both for future reference for myself and in case it might help someone else:


SQL Server-based changes (DBA tasks):



Code-based changes (Developer tasks):



  • Change code to trap SQL Server 1205 errors and resubmit the request. A 1205 error means the request was chosen as a deadlock victim, and a try/catch can resubmit without the user ever being aware of it.

  • Redesign the application to ensure that all database requests are serialized. Depending on the application, that can be easy, or it can be a major effort requiring the introduction of middleware.


If anyone else know of any other options, please let me know in the comments!


%3CLINGO-SUB%20id%3D%22lingo-sub-383947%22%20slang%3D%22en-US%22%3EGet%20Rid%20of%20Deadlocks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383947%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Dec%2012%2C%202011%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ELocks%20%3C%2FSTRONG%3E%20are%20used%20by%20relational%20database%20management%20systems%20to%20increase%20user%20concurrency%20(more%20users)%20while%20guaranteeing%20data%20consistency.%20A%20%3CSTRONG%3E%20deadlock%20%3C%2FSTRONG%3E%20is%20when%20two%20locks%20interfere%20each%20other%2C%20and%26nbsp%3Bis%20caused%20by%26nbsp%3Bone%20process%20locking%20a%20row%2C%20page%2C%20partition%2C%20or%20table%20while%20it%20waits%20for%20another%20row%2C%20page%2C%20partition%2C%20or%20table%20to%20become%20available%2C%20but%20the%20one%20it's%20waiting%20on%20is%20locked%20by%20another%20process%20that's%20waiting%20for%20what%20the%20first%20process%20has%20locked.%20When%20that%20happens%2C%20SQL%20Server%20will%20detect%20it%20and%20roll%20back%20one%20of%20them%20(the%20one%20that%20requires%20the%20least%20work%20to%20redo%20will%20be%20the%20one%20that%20gets%20rolled%20back).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOccasional%20deadlocks%26nbsp%3Bare%20common%2C%20but%20it's%20uncommon%20for%20them%20to%20occur%20so%20often%20that%20they%20become%20a%26nbsp%3Bproblem.%20Deadlocking%20does%20become%20a%20problem%20sometimes%2C%20though%2C%20and%20when%26nbsp%3Bmy%20customers%20run%20into%20it%2C%20they%20usually%20ask%20for%20my%20assistance.%20While%20there%20are%20many%20articles%20about%20how%20to%20detect%20deadlocks%2C%20I%20haven't%20found%20much%20info%20about%20how%20to%20eliminate%20them%2C%20reduce%20them%2C%20or%20minimize%20their%20impact%2C%20so%20I'm%20going%20to%20list%20all%20the%20alternatives%20I'm%20aware%20of%20here%2C%20both%20for%20future%20reference%20for%20myself%20and%20in%20case%20it%20might%20help%20someone%20else%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESQL%20Server-based%20changes%20(DBA%20tasks)%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20Change%20indexes.%20%3C%2FSTRONG%3E%20After%20you%20enable%20the%201204%20and%201222%20trace%20flags%20and%20determine%20which%20indexes%20are%20getting%20deadlocked%2C%20you%20can%20often%20eliminate%20deadlocks%20by%20adding%20an%20index%2C%20changing%20an%20index%2C%20or%20every%20once%20in%20a%20while%20by%20deleting%20an%20index.%20(See%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms178104.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms178104.aspx%20%3C%2FA%3E%20.)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20Enable%20page-level%20and%20row-level%26nbsp%3Blocking.%20%3C%2FSTRONG%3E%20Page%20and%20row%20locking%20are%20allowed%20by%20default%2C%20but%20if%20someone's%20turned%26nbsp%3Bthem%20off%2C%20you%20might%20need%20to%20consider%20turning%26nbsp%3Bthem%20back%20on.%20(See%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms189076.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms189076.aspx%20%3C%2FA%3E%20and%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms188388.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms188388.aspx%20%3C%2FA%3E%20.)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20Turn%20on%20row-versioning.%20%3C%2FSTRONG%3E%20(See%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms177404.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms177404.aspx%20%3C%2FA%3E%20.)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20Turn%20off%20parallel%20operations.%20%3C%2FSTRONG%3E%20If%20parallel%20operations%20are%26nbsp%3Bpossible%20(the%20server%20has%20multiple%20processors%2Fcores)%20and%20allowed%2C%20it's%20possible%20that%20turning%20it%20off%20(by%20setting%20MaxDOP%20to%201)%20will%20reduce%20or%20eliminate%20deadlocking.%26nbsp%3BYou%20should%20avoid%20this%20if%20something%20else%20will%20work%2C%20so%20that%20most%20workloads%20can%20benefit%20from%20parallelization.%20(See%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms181007.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms181007.aspx%20%3C%2FA%3E%20.)%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ECode-based%20changes%20(Developer%20tasks)%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3EChange%20code%20%3C%2FSTRONG%3E%20to%20trap%20SQL%20Server%201205%20errors%20and%20resubmit%20the%20request.%20A%201205%20error%20means%20the%20request%20was%20chosen%20as%20a%20deadlock%20victim%2C%20and%20a%20try%2Fcatch%20can%20resubmit%20without%20the%20user%20ever%20being%20aware%20of%20it.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ERedesign%20the%20application%20%3C%2FSTRONG%3E%20to%20ensure%20that%20all%20database%20requests%20are%20serialized.%20Depending%20on%20the%20application%2C%20that%20can%20be%20easy%2C%20or%20it%20can%20be%20a%20major%20effort%20requiring%20the%20introduction%20of%20middleware.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EIf%20anyone%20else%20know%20of%20any%20other%20options%2C%20please%20let%20me%20know%20in%20the%20comments!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383947%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20TECHNET%20on%20Dec%2012%2C%202011%20Locks%20are%20used%20by%20relational%20database%20management%20systems%20to%20increase%20user%20concurrency%20(more%20users)%20while%20guaranteeing%20data%20consistency.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383947%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerSecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 12:23 PM
Updated by: