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

Mention the differences between sql server and mysql.

0 Answers  

---

What is an sql server agent?

0 Answers  

---

When I run the sql server 2000 setup, it just hangs. What do I do?

0 Answers  

---

Can we rewrite subqueries into simple select statements or with joins? Example?

2 Answers   ASD Lab,

---

explain the storage models of olap? : Sql server database administration

0 Answers  

---

How to rename an existing table with the "sp_rename" stored procedure in ms sql server?

0 Answers  

---

Questions regarding Raiseerror?

1 Answers  

---

Which virtual table does a trigger use?

3 Answers  

---

Can the query output be sorted by multiple columns in ms sql server?

0 Answers  

---

Mention the differences between substr and charindex in sql server.

0 Answers  

---

Why is sql server log file full?

0 Answers  

---

What are the steps you should follow to start sql server in single-user mode?

0 Answers  

---