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 Master database in SQL server ?

1142

---

How to bind a view to the schema of the underlying tables?

1047

---

How to defragment indexes with alter index ... Reorganize?

1137

---

1.how to find the dead lock in sql server? 2.How to fine the memory leaks in sql server? 3.suppose transaction log file increasing what action will take ?

2079

---

What is the difference between ROW_NUMBER and Ranking function in SQL SERVER?

1202

---

Explain acid?

1142

---

What is sql injection? How to protect against sql injection attack?

1041

---

Why we use the openxml clause?

1056

---

How to find tables without indexes?

1155

---

How to connect php with different port numbers?

1147

---

Can group functions be mixed with non-group selection fields in ms sql server?

999

---

Do you know what is normalization of database? What are its benefits?

1206

---

What is master database? : SQL Server Architecture

1083

---

What are group functions in query statements in ms sql server?

1065

---

What are the basic functions for master, msdb, model, tempdb and resource databases?

1036

---