Chapter 3 Handling locks, blocking, and deadlocks (3) using SQLServerProfile

Source: Internet
Author: User
As a DBA, there may often be deadlocks reported by colleagues or customers, which affects the use of the system. In this case, you need to detect and handle such problems as soon as possible. A deadlock occurs when two or more transactions are blocked. In this case, two transactions will wait for the other party to release resources for Operation indefinitely. Below is the deadlock

As a DBA, there may often be deadlocks reported by colleagues or customers, which affects the use of the system. In this case, you need to detect and handle such problems as soon as possible. A deadlock occurs when two or more transactions are blocked. In this case, two transactions will wait for the other party to release resources for Operation indefinitely. Below is the deadlock

Preface:

As a DBA, you may often encounter deadlocks reported by colleagues or customers, affecting the system'sUse. In this case, you need to detect andProcessingThis type of problem.

A deadlock occurs when two or more transactions interact with each other.Blocking. In this case, two transactions will wait for the other party to release resources for Operation indefinitely. The following is a deadlock:



This article willUseSQLServer Profiler to track deadlocks.

Preparations:

To detect deadlocks, we need to first simulate deadlocks. In this exampleUseTwo different sessions create two transactions.

Steps:

1. Open SQLServer Profiler

2. Select New Trail to connect to the instance.

3. Select the blank template:


4. On the event selection page, expand the Locks event and select the following events:

1. Deadlock graph

2. Lock: Deadlock

3. Lock: Deadlock Chain


5. Open the TSQL event and select the following events:

1. SQL: StmtCompleted

2. SQL: StmtStarting


6. Click column filter. In the tracking attribute, select the database name to be detected.UseAdventureWorks.


7. In the Organization column, adjust the order as follows:


8. Click Run.

9. Enable SQLServer and enable two connections.

10. Enter and execute the following script in the first window:


USE AdventureWorks GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRANSACTIONSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 121316



11. Enter and execute the following script in the second window:


USE AdventureWorksGOSET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTIONSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 121317



12. Return to the first form and run the following script:


UPDATE Sales.SalesOrderDetailSET OrderQty=2WHERE SalesOrderDetailID=121317



13. Enter the following statement in the second window:


UPDATE Sales.SalesOrderDetailSET OrderQty=2WHERE SalesOrderDetailID=121316



14. The following message is displayed in the second window:


15. Switch to SQLServer Profiler and you can see the following:


16. Click Deadlock graph to display the Deadlock image:


17. You can save the deadlock image, right-click and choose export event data, and save it as an xdl file:


The XML format is as follows:

Analysis:

In this article, first create a blank Profiler template, and then select the following event for monitoring:


1. Deadlock graph

2. Lock: Deadlock

3. Lock: Deadlock Chain

4. SQL: StmtCompleted

5. SQL: StmtStarting

Then, you can limit the range of Monitored Objects by limiting the database.

After configuration, run the trail and the script in ssms. SQLServer automaticallyProcessingAnd detect deadlocks of this type. Then, the system will receive the 1205 error in the second form.

In SQLServer Profiler, we demonstrate how to collect deadlock events. In the tracking result, we can see that two transactions try to add an exclusive lock on a key with a shared lock. The deadlock image shows the details of the deadlock.

To avoid or minimize the occurrence of deadlocks, you can refer to the following suggestions:

1. Make sure that your transaction is as small as possible. Here it refers to the scope.

2,UseTransactions at a lower isolation level.

3. for possible queries,UseNOLOCK query prompt.

4. standardized database design.

5. Create an index on the required columns so that the table does not need to be scanned frequently to reduce the occurrence of lock issues.

6. The sequence of controlling database object access is the same.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.