It will then insert a record into table Tbl2 with a value of 2 for the ‘col’ field, which references the primary key in Tbl1. This script will also start a new transaction. Open up another New Query window and execute the following script. it won’t be committed or rolled back (yet). The new record will contain a primary key field value of 2.
This script will start a transaction and insert a record into table Tbl1. Once you’ve created the above mentioned tables, open up a New Query window and execute the following script. Tbl1 ( id )) Script to be used in Session #1 there is a foreign key between the 2 tables on the col field in the second table).ĬREATE TABLE Tbl1 ( id INT NOT NULL PRIMARY KEY CLUSTERED, col INT )ĬREATE TABLE Tbl2 ( id INT NOT NULL PRIMARY KEY CLUSTERED, col INT REFERENCES dbo. The id column in each table is the primary key (with a clustered index) and the col field in the second table references the id field in the first (i.e. The script shown below contains the logic to create 2 simple tables containing 2 integer columns each. Scripts to create required objects in the database I hope this assists you in evaluating your chosen method to log details on deadlocks. The logic behind why the deadlock occurs has also been described below. This post contains scripts I wrote recently to simulate a deadlock. More often than not, deadlocks require a certain level of concurrency and timing which users are rarely able to simulate, requiring you as the DBA to find programmatic ways to force a deadlock. However, deadlocks are rarely easily reproducible through users running applications. There are plenty of posts on the internet which will give you adequate information on all of these techniques.Īfter you’ve decided on a particular technique, you should look to test the results of the chosen technique in a non-production environment.
There are many possible ways to log the details of a deadlock such as setting trace flags 1204 & 1222, running a SQL trace with the TSQL_Locks template, or using third party detection tools like SQL Deadlock Detector from Lakeside SQL. You would then try and find ways to log details when the next deadlock occurs. Unless you have a system in place for detecting and analysing deadlocks, you’re probably going to get an email with a snapshot of a screen in some application where a user has encountered a cryptic message which has the word ‘deadlock’ in it. Troubleshooting deadlocks is one such example.
#SIMPLE SQL DEADLOCK EXAMPLE SOFTWARE#
And on most occasions, we are at the mercy of software vendors or in-house application developers who believe it is their God-given right to leave complex database issues to ‘someone else’. As DBAs, we are constantly looking to proactively find and fix issues before they become major problems.
0 kommentar(er)
