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 are “lock” hints?

1053

---

How to implement service broker?

1009

---

What is usually the first word in a sql query?

1133

---

What is the recovery model?

985

---

Differentiate between a having clause and a where clause.

965

---

tell me what is blocking and how would you troubleshoot it? : Sql server database administration

899

---

what is an extended stored procedure? : Sql server database administration

1052

---

What is a derived table?

925

---

What is the process of normalization?

1120

---

What are secondary xml indexes?

1026

---

Can we do dml on views?

952

---

Explain the phases a transaction has to undergo?

1057

---

Explain filtered indexes benefits?

997

---

How to create user defined functions with parameters?

1229

---

What is the parse query button used for?

1036

---