what will happened to the victem of deadlock in sql server

In the article on Deadlock Analysis and Prevention, nosotros saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why ane process is selected every bit deadlock victim and not the other.

Deadlock Detection

SQL Server runs a lock monitor thread every five seconds to cheque if whatsoever deadlocks have occurred. If a deadlock is institute, a victim is selected and the interval for the lock monitor thread is reduced, this can be to as low as 100 milliseconds in some cases. If the lock monitor thread stops finding deadlocks the interval for deadlock detection is then periodically increased upwards to the default v seconds interval.

When a deadlock is detected, SQL Server then needs to select a victim of the deadlock.

Once this has been washed, the victim transaction is rolled back and all of the resources held by the victim are released. A 1205 fault is sent to the application that was running the victim transaction.

Deadlock Priority

By default, SQL Server chooses as the deadlock victim the transaction that is least expensive to rollback. In simple terms, a transaction that makes the fewest changes to the database is considered the least expensive.

However, users can ready custom priorities for a particular transaction using the SET DEADLOCK_PRIORITY argument. The process with the lowest deadlock priority will and so be the i chosen every bit the deadlock victim.

Case: Fix DEADLOCK_PRIORITY NORMAL

By default the priority for all the processes is normal. Information technology can be set to Depression, NORMAL or HIGH. It can also be set to an integer value in the range of -10 to 10.

Every bit noted earlier, the process or transaction with the lowest deadlock priority is chosen as the deadlock victim. If the priority of all the transactions involved in the deadlock is aforementioned, the transaction that is to the lowest degree expensive is chosen equally deadlock victim. In the unlikely event that both the deadlock priority and the cost of the transactions involved in the deadlock are equal, then deadlock victim will be selected randomly.

This is most easily explained by working through an case.

Creating Dummy Data

Let'southward create some dummy information. We will use this data in our deadlock instance. Execute the following script on your SQL Server.

1 two three 4 5 vi seven 8 9 10 eleven 12 13 xiv 15 xvi 17 18 nineteen 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

CREATE DATABASE testdb ; GO USE testdb ; CREATE Tabular array table1 ( id INT IDENTITY Principal KEY , student_name NVARCHAR ( 50 ) ) INSERT INTO table1 values ( 'James' ) INSERT INTO table1 values ( 'Andy' ) INSERT INTO table1 values ( 'Sal' ) INSERT INTO table1 values ( 'Helen' ) INSERT INTO table1 values ( 'Jo' ) INSERT INTO table1 values ( 'Wik' ) CREATE TABLE table2 ( id INT IDENTITY Master KEY , student_name NVARCHAR ( 50 ) ) INSERT INTO table2 values ( 'Alan' ) INSERT INTO table2 values ( 'Rik' ) INSERT INTO table2 values ( 'Jack' ) INSERT INTO table2 values ( 'Marker' ) INSERT INTO table2 values ( 'Josh' ) INSERT INTO table2 values ( 'Fred' )

Deadlock Priority Case

Start, let's take a expect at a practical instance of how deadlock victims beingness selected based on their deadlock priority.

We will run 2 parallel instances of SSMS. Each instance will run one transaction that will exist accessing a resource locked by the other transaction. A deadlock will occur and SQL server will then select every bit the victim the transaction that is the to the lowest degree expensive to roll back.

Script for the First Transaction:

Employ testdb ; -- Transaction1 Begin TRAN UPDATE table1 Ready student_name = student_name + 'Transaction1' WHERE id IN ( 1 , 2 , 3 , 4 , 5 ) UPDATE table2 Ready student_name = student_name + 'Transaction1' WHERE id = 1 COMMIT TRANSACTION

Script for the Second Transaction:

Use testdb ; -- Transaction2 BEGIN TRAN UPDATE table2 SET student_name = student_name + 'Transaction2' WHERE id = 1 UPDATE table1 Set up student_name = student_name + 'Transaction2' WHERE id IN ( 1 , 2 , 3 , four , v ) COMMIT TRANSACTION

The process of creating a deadlock is simple. Kickoff execute the showtime update statement from the first transaction and then execute the commencement update statement from the second transaction.

This will create locks on table1 and table2. Now execute the second update statement from transaction1. This argument tries to access table2 which is locked by transaction2.

Finally, execute the second update statement in transaction2. This statement tries to admission table1 which is locked and at this stage a deadlock will occur.

Now nosotros know that by default, the deadlock priority for both of the transactions is NORMAL, therefore SQL server will select that transaction as deadlock victim which is to the lowest degree expensive to rollback.

The first UPDATE statement of transaction1, information technology is updating 5 rows of table1 whereas the first UPDATE argument of transaction2 is updating just one row in table2. This means that transaction2 is the least expensive to rollback.

Later some time, y'all volition see SQL Server select transaction2 as the deadlock victim and it will then curlicue it back as expected. The error message is shown in the following screenshot:

Now if y'all select the information from table1 you should run into updated data every bit shown below:

id	student_name
1	JamesTransaction1
2	AndyTransaction1
3	SalTransaction1
iv	HelenTransaction1
five	JoTransaction1
half dozen	Wik

Deadlock with Deadlock Priority set to High

In the next example, we volition set the deadlock priority of transaction2 to HIGH and will once more create a deadlock.

This fourth dimension transaction1 will be selected every bit deadlock victim since it will have deadlock priority of NORMAL by default. Earlier we practise that allow's clean our tables and re-insert the information into it.

Let'due south truncate both the tables using post-obit commands:

TRUNCATE Table table1 ; TRUNCATE TABLE table2 ;

At present once more add some data in table1 and table2 by executing the post-obit script:

Utilise testdb ; INSERT INTO table1 values ( 'Andy' ) INSERT INTO table1 values ( 'Sal' ) INSERT INTO table1 values ( 'Helen' ) INSERT INTO table1 values ( 'Jo' ) INSERT INTO table1 values ( 'Wik' ) INSERT INTO table2 values ( 'Rik' ) INSERT INTO table2 values ( 'Jack' ) INSERT INTO table2 values ( 'Mark' ) INSERT INTO table2 values ( 'Josh' ) INSERT INTO table2 values ( 'Fred' )

Again, lets run two instances of SSMS in parallel and execute ii transactions in them.

The script for transaction1 is same as in the previous instance. In transaction2, we need to ready the deadlock priority to HIGH. The script for transaction2 is as follows:

Utilise testdb ; -- Transaction2 SET DEADLOCK_PRIORITY HIGH BEGIN TRAN UPDATE table2 SET student_name = student_name + 'Transaction2' WHERE id = 1 UPDATE table1 SET student_name = student_name + 'Transaction' WHERE id IN ( 1 , two , 3 , 4 , 5 ) COMMIT TRANSACTION

Now, repeat the aforementioned scenario as we did in the previous instance and create a deadlock. This time you will encounter that transaction1 running in the first instance will be called as deadlock victim as shown in the following screenshot.

Conclusion

SQL Server selects deadlock victim following these rules:

1. The procedure with the lowest deadlock priority is fix as deadlock victim.
2. If the deadlock priority of all the processes involved in deadlock is same, then the procedure that is least expensive to rollback is selected equally deadlock victim.
3. If both the deadlock priority and cost of processes involved in deadlock is same, then the process a process is selected randomly as deadlock victim.

Other great articles from Ben

• Author
• Recent Posts

Ben Richardson runs Vigil Grooming a leading provider of SQL training the Uk. Information technology offers a total range of SQL training from introductory courses through to advanced administration and data warehouse training – see hither for more details. Acuity has offices in London and Guildford, Surrey. He as well blogs occasionally on Acuity's blog

View all posts by Ben Richardson

geislerforbeartne.blogspot.com

Source: https://www.sqlshack.com/sql-server-selects-deadlock-victim/#:~:text=Deadlock%20Detection&text=When%20a%20deadlock%20is%20detected,was%20running%20the%20victim%20transaction.

Share this post