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

How to create a user name in a database?

996

---

What is difference between inner join and join?

986

---

When we should use and scope of @@identity?

953

---

What is row-level compre?

944

---

Are semicolons required at the end of sql statements in sql server 2005?

934

---

What is usually the first word in a sql query?

1073

---

What is query optimization process?

941

---

Explain foreign key in sql server?

978

---

What is the default Port No on which SQL Server listens?

1071

---

What is the difference between system objects and user objects?

1103

---

How will you know when statistics on a table are obsolete?

1034

---

What is difference between equi join and inner join?

860

---

How can you stop stored procedures from recompiling?

849

---

What are the differences between “row_number()”, “rank()” and “dense_rank()”?

1038

---

What are logical database components? : SQL Server Architecture

962

---