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

Define Unique Key?

937

---

Write query to return all rows sql?

1003

---

What is bcp? When does it used?

919

---

Any one plz send me SQL Server Developer/DBA resume for 4 years experience

2662

---

What is user-defined function? Explain its types i.e. Scalar and inline table value user-defined function?

839

---

How to achieve Paging of records in SQL SERVER?

1026

---

You want to check the syntax of a complicated update sql statement without executing it. What command should you use?

905

---

Help!!!!!!!!!!!! My database has gone offline, it is highlighted as 'Suspect'. Foolishly, i haven't got a recent back up. Is there a way of quickly restoring the database? Thank you

1706

---

What are the advantages of sql azure?

138

---

what are cursors? : Sql server database administration

988

---

Can you explain various data region available in ssrs with their use?

915

---

What is the use of =,==,=== operators?

956

---

What are the difficulties faced in cube development? : sql server analysis services, ssas

997

---

How to rename databases in ms sql server?

1123

---

How many types of triggers are there?

1001

---