Troubleshooting a SQL Server R2 deadlock

Problem scenario: In the customer that encountered a operation card dead phenomenon

Problem solving:

1, how to hook is a deadlock problem: through code tracing, the discovery is to execute a SQL statement timeout, so conjecture may be the table locked

2. If the confirmation is a matter of thought: Deadlock found through SQL, the following is the relevant SQL

Select request_session_id spid,object_name (resource_associated_entity_id) TABLENAME from sys.dm_tran_locks where Resource_type= ' OBJECT ' above this SQL is to query the current database of what deadlock, SPID for process ID, TABLENAME for table name PS: When Baidu has seen with SQL Tracker (SQL SERVER Profiler) can track out deadlocks
DBCC InputBuffer (SPID) finds the corresponding SQL based on the process ID, and with SQL you can confirm the location of the code 3. WORKAROUND: Change the database transaction ISOLATION level ALTER DB ng0001 set Read_ Committed_snapshot on with rollback immediate 4, Summary: This problem due to the relatively urgent plus is the night overtime, so there is no specific view of the lock category and the original database transaction isolation level, the SQL Server lock and Transaction isolation level these 2 knowledge points need to know more

Troubleshooting a SQL Server R2 deadlock