Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Identifing deadlocks in SQL Server

In this article we will describe the details about dead locks in SQL server DB. Dead lock is one of the important areas where every data base developer should pay more attention.

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.

Deadlock events

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

Target Locks

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.

Schema Lock

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.

Conclusion

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.


Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login