Refer to the below screenshot with the highlighted area. Click on the Events Selection tab.Ĭheck Show all events, expand the Locks section to select Deadlock graph, Lock: Deadlock and Lock: Deadlock Chain. To open this, go to Tools -> SQL Server Profiler So, the next task is to trace the Deadlock and resolve this.įollow the below steps to trace and detect Deadlock.Ĭopy below queries in 3 different Query windows and execute in the order it is mentioned.īefore executing the above query, first, open SQL Server Profile. Once you know that frequent Deadlock occurs in your application database and in result the application performance gets hampered. How to trace and detect a Deadlock in SQL Server? Syntax – select * from Products with(NOLOCK) where Id = 1 It stops read and write transactions from blocking the same resource. NOLOCK is used to read data from the SQL Server table by ignoring any locks imposed on it. Syntax – select * from Products with(ROWLOCK) where Id = 1 The UPDLOCK is used to apply a lock on a resource that possibly can be updatedĪ select statement before updating the record in the same transaction, this will help to prevent a Deadlock situation. The below table tells about UPDLOCK, ROWLOCK and NOLOCK uses in SQL Server. Now, run both queries and you will not see any Deadlock error. We will not make any changes in the second query window. UPDATE #Table2 SET City= 'Chennai' WHERE Id = 1 UPDATE #Table1 SET Name = 'Shyam' WHERE Id = 1 SELECT Id FROM #Table2 WITH (UPDLOCK) WHERE Id=1 - This line Added So, the first query window script will look like this – BEGIN TRANSACTION SELECT Id FROM #Table2 WITH (UPDLOCK) WHERE Id=1 In order to resolve this, add the below line in the first query window. Also, try to impose a lock for a short span of time. If we talk about the above script, simply we can use UPDLOCK to avoid SQL Server Deadlock. The very first thing is to optimize your stored procedures and make sure to use small transaction blocks.Īnother way is to use a lock hint within your query. There are multiple ways to avoid the Deadlock situations in SQL Server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |