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

Explain “@@rowcount” and “@@error” in sql server?

1062

---

Explain magic tables in sql server?

1034

---

Can a stored procedure call itself or a recursive stored procedure? How many levels of sp nesting is possible?

1058

---

What are the different types of sql server replication? : sql server replication

1240

---

How does Report Builder support Analysis Services cubes?

153

---

What is difference between cte and view?

894

---

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

1011

---

What are the different type of replication in sql server?

968

---

Explain table valued parameters in sql server? Why tvp used?

1001

---

Explain different forms of normalization?

927

---

Find first and last day of current month in sql server

1008

---

Do you know what is replace and stuff function in sql server?

992

---

do you know what is a deadlock and what is a live lock? How will you go about resolving deadlocks? : Sql server database administration

965

---

How many types of functions are there in sql server?

917

---

What is acid properties?

1075

---