Avoid resource deadlock: Identify opened transactions

Deadlocks are often caused by the following reasons:
1,ProgramAn error occurs. For example, a transaction needs to operate on two tables. Due to a program error, Table A is first operated on table B and table A is sometimes operated on after table B, the opportunity to wait for each other and cause a deadlock.
2. The transaction is too large. There are many tables for large transactions, many operation steps, and many deadlock opportunities.
3. Performance problems. Some statements have poor performance. long locks are easy to cause deadlocks with other transactions.

Because opened transactions may deadlock resources and lead to performance problems, it is helpful to know which transactions are opened in a dedicated database. Deadlocked resources may block users of other databases.

To find these opened transactionsQuery the sysprocesses table in the master database. The sysprocesses table has an open_tran column, which indicates whether the existing command is an open transaction. If the value is greater than 0, it indicates that it is an opened transaction. The sysprocesses table also has a spid column, indicating the ID of the system process that is accessing SQL Server. You canUse the spid column as the parameter of DBCC inputbuffer () system function. Only the SQL Server sysadmins account can execute this function. The output of this function is the 255-character command corresponding to the spid. You can determine which command is the culprit affecting the database performance, and then issue a kill command based on the spid.

Typical solution: (from Microsoft http://support.microsoft.com/kb/832524/zh-cn)

•	add and delete indexes.
•	index addition prompt.
•	modify the application to access the resource in similar mode.
•	delete an activity from the transaction image trigger. By default, a trigger is a transaction.
•	keep the transaction as short as possible.