How do you simulate a deadlock for testing purposes
- 1 Answers
- 9569 Views
- I also Faced
- E-Mail Answers
Answer Posted / jay
Below is a quick recipe for a dead lock. Two transactions,
one first updating table 1, then 2 and the other one doing
it in reverse order.
Both transactions wait in the middle for 20 seconds to give
you some time to execute them 'simulaneously'.
When you run the two in transactions in two windows 'at the
same time', you'll only have to wait ~20 seconds, and one of
the windows will experience a dead lock.
CREATE TABLE t1 (i int);
CREATE TABLE t2 (i int);
INSERT t1 SELECT 1;
INSERT t2 SELECT 9;
/* in one window enter: */
BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY '00:00:20'
UPDATE t2 SET i = 99 WHERE i = 9
COMMIT
/* in a second window (another transaction) enter: */
BEGIN TRAN
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:20'
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT
| Is This Answer Correct ? | 8 Yes | 1 No |
Post New Answer View All Answers
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
What is difference between drop truncate and delete?
What is the difference between index seek vs. Index scan?
How to generate create view script on an existing view?
What are the differences between char and varchar in ms sql server?
What is the meaning of sql server?
Can you explain what are various ways to enhance the ssrs report?
What is an entity-relationship diagram (erd)?
What stored by the msdb? : sql server database administration
What does select 1 mean?
Explain transaction isolation levels in sql server?
What is the use of set nocount on/off statement?
What are examples of triggers?
What is index in an assignment?
What are relationships and mention different types of relationships in the dbms
