Deadlock means that more than one entity is locking some sources, and no one of them is capable to finish, because there are blocking sources in a cyclic order.
How to identify deadlocks?
Deadlocking happens when more than one SQL Server process have locks on each database objects and when the other processes have priorly jammed, each process is trying to absorb a lock on an object.
For example, process 1 is in exclusive lock on obj. 1, process 2 is in exclusive lock on Obj. 2, and process 1 wants an exclusive lock on obj. 2, and obj.2 wants an exclusive lock on obj. 1. Because more than one process cannot have an exclusive lock on the same object at the same time, the two processes become occupied in a deadlock, with never development willing to capitulate of personal side-line. Since a deadlock is not a good thing for an app, SQL Server 2005 is smart enough to identify the problem and closes the deadlock by erasing one of the processes - usually the process that has used the least count of server resources up to that point. The collapsed transaction is rolled back and the infamous "deadlock victim" error message is throws to the application. The other process can then continue to run as normal.
If the application is deadlock prone, it can resubmit the erased transaction automatically and the user may never know the deadlock happened. If the application is not deadlock prone, then most likely an error message appears on the application’s interface and you get a call from a disgruntled user. Besides disgusting users, deadlocks can use up SQL Server's resources unnecessarily as transactions are erased, rolled back, and resubmitted again.
How to categorize and find out SQL Server troubles?
Deadlocks have been the bane of a DBA. Deadlocks may be a major problem for - how I can say this modestly? It is “Less intelligent” application code.
In SQL Server 2000 and prior, the most common way to identify deadlock issues was to use a trace flag. In SQL Server 2005, trace flags can be used (1204 or 1222), but it is not always easy. When SQL Server 2005 was launched, new procedures were further to the SQL Server Interpreter that makes identify deadlocks very effortless.
There are many distinct combinations of events you can use to identify deadlocks using SQL Server Profiler; you may want to consider the following. For education purposes, I have included more events here than you really need, but they have been included for completeness.
The events you might consider collecting include:
How to recognize and identify SQL Server Problems?
This event is trapped whenever a deadlock happens, also every time the Deadlock Graph event is trapped, generating redundant data. I have included it here because it makes it a little bit easier to see what is occurring but, if you prefer, you can drop this event from your trace.
Lock Escalation Parameters
Lock Escalation parameters are one of the hottest topics being discussed in forums on the Internet today (and probably for a long time to come as well.) This set of options can lead you down the lane of eternal server Adjustments, if we do not look at all the reasons we are having locking issues on your server in the first position.
It is easy to assume that you need to edit one of these parameters to overcome deadlocks and hung processes. In some occurrence, the source of your problem is in fact a wrongly configured locking scheme, but in reality, most locking problems are the result of questionable query methods and database design. The Lock Escalation Thread maximum is intended as an upper limit for the server to use. The server will hold this maximum value of actual 2000 page locks per statement executed before used to escalate the lock to a table lock. The lower limit is used to help keep SQL Server from locking a small table with only a small no of rows in it every time a query is run against it. The minimum is used in conjunction with the Lock Escalation Used Percentage to control this situation and keep locks from getting out of control. All of these options can be overridden on a query-by-query basis with optimizer hints. Including hints in your SQL statements will cause these thresholds to be ignored. Great care should be taken when overriding occurs.
SQL Server 2005 incorporated Read Committed Snapshot Isolation (RSCI)
When Should You Use READ COMMITTED SNAPSHOT ISOLATION (RCSI)?
If you really covet my judgment on the focus: forever. If you’re deceitful a new function, turn on RCSI from the get go and sketch your hardware just about alive in a world of splendid. tempDB tradition will be upper because that’s where SQL Server keeps all of the extra version. Many DBAs will be bothered about supplementary tempDB consumption, but there are ways to keep tempDB running well.
The question is why we use READ COMMITTED SNAPSHOT ISOLATION (RCSI)?
Use RCSI to eradicate lock, jamming Deadlocks, meager function presentation, and universal dumpiness of gasp.
RCSI may not treat pleurisy, but it’s going to prospect evidence your function. Wherever downward the road, if you’re unbeaten, you’ll have to contract with deadlocks. Turning on RCSI is going to eradicate that apprehension, or make it so minimum that you’ll be amazed when it lastly occurs.
Receiving free of Deadlocks in perform
There are physical ways to achieve eradicate deadlocks, but they require noteworthy attempt to mean and execute. In many cases deadlocks can be eradicated by using either read committed snapshot isolation or snapshot isolation. Making the preference premature in an exe’s progress, sooner all through architectural decision, can make this change effortless, unproblematic, and can be premeditated into the purpose from the set up, assembly deadlocks a object of the earlier period.
To avoid a deadlock for demonstration purposes, I ran this Procedure
CREATE PROCEDURE [dbo].[ NexID]( @IDNm nvarchar(250) ) AS BEGIN DECLARE @Retry int; DECLARE @fN int, @fS int, @fT int; SET @Rtry = 5; DECLARE @NwID int; SET operation loneliness juncture Sequential; SET NOCOUN ON; WHILE @Retry > 0 BEGIN BEGIN TRY BEGIN TRANSACTION; SET @NwID = COALESCE((SELECT LasID FROM tblIDs WHERE IDNm = @IDNm),0)+1; IF (SELECT COUNT(IDNm) FROM tblIDs WHERE IDNm = @IDNm) = 0 INSERT INTO tblIDs (IDNm, LastID) VALUES (@IDNm, @NwID) ELSE UPDATE tblIDs SET LasID = @NwID WHERE IDNm = @IDNm; COMMIT TRANSACTION; SET @Rtry = -2; END TRY BEGIN CATCH IF (ERROR_NUMBER() = 1205) SET @Rtry = @Retry - 1; ELSE BEGIN SET @Rtry = -1; SET @EN = ERROR_NUMBER(); SET @ES = ERROR_SEVERITY(); SET @ET = ERROR_STATE() RAISERROR (@fN,@fS,@fT); END ROLLBACK TRANSACTION; END CATCH END IF @Retry = 0 BEGIN SET @fN = 1205; SET @fS = 13; SET @fT = 1 RAISERROR (@fN,@fS,@fT); END ELSE SELECT @NwID AS NwID; END GO OR We can go for : BEGIN TRY BEGIN TRANSACTION COMMIT TRANSACTION END TRY BEGIN CATCH SET @er = @@ERROR IF @er = 1205 ROLLBACK TRANSACTION INSERT INTO ErrorLog (erID, erMsg) VALUES (@rr, 'Deadlock recovery attempt.') WAITFOR DELAY '00:00:10' GOTO RETRY IF @er = 2627 SET @erMsg = 'PK Violation.' IF @erMsg IS NULL SET @erMsg = 'Other Error.' INSERT INTO ErrorLog (ErID, ErMsg) VALUES (@er, @erMsg) END CATCH SQL SERVER DEADLOCK SIMULATION dbcc traceon (1204, 3605, -1) go dbcc tracestatus(-1) go -- Deadlock reproduction - correlation (sitting) 1 USE Masterworks; WHILE ( 1< 2) BEGIN BEGIN TRANSACTION UPDATE pur.purOrderHeader SET changedDate= changedDate UPDATE sal.salOrderHeader SET changedDate = changedDate COMMIT TRANSACTION END -- Deadlock reproduction - correlation (sitting) 2 USE Masterworks; WHILE (1< 2) BEGIN BEGIN TRANSACTION UPDATE Sal.SalOrderHeader SET changedDate = changedDate UPDATE Pur.PurOrderHeader SET changedDate = changedDate COMMIT TRANSACTION END
A target lock is a fair pennon or a caution dead bolt that alerts other communication to the truth that incredible further is departing on. The principal rationale of a target lock is to look up presentation. Because a target lock is second-hand for all type of tresses and for all bolt granularities, SQL Server has many types of target locks.
Plan tresses guard the database plan. SQL Server will apply plan constancy bolt during any doubt to avert DDL instructions. A plan amendment bolt is useful only while SQL Server is adjusting the substantial plan. If SQL Server is in the focus of totaling a discourse to a tuple, the plan lock will put off any other dealings from presentation or changing the facts at some stage in the plan-modification function.
Excellent with automation the possibilities are endless - Some of the fastest things that come to my mind are:
- We can make an event contribution and use the Forwarded Events log to track all from different servers which have SQL Server instances installed on them.
- We can edit the Powershell script to perform additional post processing or send out email to your DBAs for the occurrence of such happenings.
- We could even add more post process to the Powershell script to perform more tasks. If we have SCOM or any other Event log identifier software, only thing that we need is to make a routine alert in lieu of system to generate an error message.