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 is an execution plan? How would you view the execution plan?

806

---

how to trace the traffic hitting a sql server? : Sql server database administration

1419

---

Can we write ddl in trigger?

650

---

What is the difference between ddl,dml and dcl commands?

810

---

What is the difference between varchar and varchar types?

745

---

---

What are the differences between left join and inner join in sql server?

770

---

You have to store user responses of ‘yes’ and ‘no’ what kind of data type is best suited for this task?

708

---

How can sql server instances be hidden? : sql server security

747

---

What is a subquery in a select query statement in ms sql server?

805

---

How can I track the changes or identify the latest insert-update-delete from a table?

715

---

What do you understand by mirroring?

744

---

What are commonly used odbc functions in php?

722

---

What is the difference between a view and a stored procedure?

759

---

How we can compare two database data?

686

---

How you can add messages to the nt event log from within a stored procedure?

775

---