Answer / 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 the STUFF function and how does it differ from the REPLACE function?

2 Answers  

---

How to maintain a fill factor in existing indexes?

0 Answers  

---

What is a print index?

0 Answers  

---

What is implicit mode in sql server?

0 Answers  

---

Explain how to maintain a fill factor in existing indexes?

0 Answers  

---

What is use of @@ spid in sql server?

0 Answers  

---

What is excution Plan in sql server

1 Answers   CarrizalSoft Technologies, TCS,

---

How do I find information about the install locations for the various instances running on a computer?

0 Answers  

---

Explain transaction server explicit transaction?

0 Answers  

---

What is the user of Primary key?

0 Answers  

---

How to insert multiple rows with one insert statement in ms sql server?

0 Answers  

---

What is 1nf 2nf and 3nf?

0 Answers  

---