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

What is self contained scalar sub query?

975

---

What is update locks?

893

---

List some case manipulation functions in sql?

1035

---

What is the difference between getdate and sysdatetime?

1014

---

what is a transaction and what are acid properties? : Sql server database administration

1027

---

What is a full text index?

926

---

your distribution database is full what will u do

2174

---

Define magic tables in sql server?

1070

---

What is merge?

1021

---

What is the difference between set and select?

1098

---

What is a raid and what are different types of raid configurations?

1267

---

How to turn off warning messages during php execution?

1047

---

How to create an index on a view?

1070

---

Write an SQL query to obtain the 2nd highest salary.

1079

---

What is named calculation? : sql server analysis services, ssas

988

---