![]() Deadlock is said to be one of the most feared complications in DBMS as no task ever gets finished and is in waiting state forever.įor example: In the student table, transaction T1 holds a lock on some rows and needs to update some rows in the grade table. The DeadlockGraph column should contain an XML document that shows all the properties of the deadlock graph event.A deadlock is a condition where two or more transactions are waiting indefinitely for one another to give up locks. Examine the contents of the DeadlockEvents table by running the following script: SELECT * FROM DeadlockEvents SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) Īfter provoking the deadlock, wait several moments for SQL Server Agent to activate the alert and run the job. After a short time-out, SQL Server will choose either this script or the script in the sample as the deadlock victim and end the transaction. This script blocks, waiting to acquire a lock on Production.Location. Run the following script in the first query tab. SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) This script produces one result set and then blocks, waiting to acquire a lock on Production.Product. Run the following script in the second query tab. SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) This script produces one result set and finishes. Run the following script in one of the query tabs. In SQL Server Management Studio, open two SQL Query tabs and connect both queries to the same instance. change MSSQLSERVER to the name of the instance.ĮXEC _add_alert to * FROM Deadlock Graph' To monitor deadlocks for a different instance, Add an alert that responds to all DEADLOCK_GRAPH events for Set the job server for the job to the current instance of SQL Server.ĮXEC _add_jobserver = N'Capture Deadlock Graph' Add a jobstep that inserts the current time and the deadlock graph intoĮXEC = N'Capture Deadlock graph into N'INSERT INTO DeadlockEvents (AlertTime DATETIME, DeadlockGraph XML) ĮXEC _add_job Deadlock for responding to DEADLOCK_GRAPH events' IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL To change the instance that the alert monitors, substitute the instance name for MSSQLSERVER in the for the alert.įor SQL Server Agent to receive WMI events, Service Broker must be enabled in msdb and AdventureWorks2022. The WQL statement requests any DEADLOCK_GRAPH event in the default instance. For this alert, SQL Server Agent monitors the default instance on the local computer. For a WMI alert, SQL Server Agent creates a notification query using the namespace and WQL statement specified. The alert runs the job whenever a deadlock graph trace event would be logged. Because the job step uses the Transact-SQL subsystem, the job step does not specify a proxy. Notice that SQL Server implicitly converts the string into XML format. The job step retrieves the TextData property of the WMI event instance and inserts that value into the DeadlockGraph column of the DeadlockEvents table. The script first creates the job that the alert will run, adds a job step to the job, and targets the job to the current instance of SQL Server. The table contains two columns: The AlertTime column holds the time that the alert runs, and the DeadlockGraph column holds the XML document that contains the deadlock graph. Exampleįirst, a table is created in the AdventureWorks database to hold the deadlock graph event. ![]() Notice that, although several Service Broker objects are involved in processing the notification message, the WMI Event Provider handles the details of creating and managing these objects. SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event. ![]() The following sample presents a simple alert that saves XML deadlock graph events in a table for later analysis. One common way to use the WMI Event Provider is to create SQL Server Agent alerts that respond to specific events. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |