Example of database deadlock check

The database on a project goes down, not because of my workflow. I am not a database expert either. I can only learn and use it with my scalp. I suspect that the database is deadlocked and I am heading in this direction.

Log on to the query analyzer and run DBCC traceon (1204,360 5,-1). The parameter 1204 indicates the type of the lock involved in the deadlock and the currently affected command, 3605 indicates that the trace information must be recorded in the log, and-1 indicates that the status of all open trace tags is returned. Note: 1. The event probe does not follow the deadlock SQL statement because it only records the executed SQL statement, these deadlocked SQL statements in the transaction will be rolled back; 2. The database will record the logs only after these marks are opened. Opening these marks will affect the database performance, remember to use DBCC traceoff (Mark Number) to turn it off when it is used up.

Then the deadlock is reproduced, so that the implementer can remotely find 5 or 6 people and click this function at the same time. Hey hey, it's good. It's just coming out. Send the database log, generally under Program Files \ Microsoft SQL Server \ mssql.4 \ MSSQL \ log \ errorlog. Open the file and carefully check the following logs.

2008-05-31 10:10:46. 20 spid5s node: 1

10:10:46. 20 spid5s RID: 5: 1: 6815: 21 cleancnt: 3 mode: X flags: 0x2
2008-05-31 10:10:46. 20 spid5s Grant List 2:
10:10:46. 20 spid5s owner: 0x3711e420 mode: X flg: 0x0 Ref: 1 life: 02000000 spid: 59 ECID: 0 xactlockinfo: 0x387a26fc
10:10:46. 20 spid5s spid: 59 ECID: 0 statement type: Delete line #: 133
10:10:46. 20 spid5s input Buf: RPC event: Proc [Database ID = 5 Object ID = 1254295528]
2008-05-31 10:10:46. 20 spid5s requested:
10:10:46. 20 spid5s restype: lockowner stype: 'or' xdes: 0x533ebc80 mode: s spid: 67 batchid: 0 ECID: 0 taskproxy :( 0x1f092374) value: 0x2962ae40 cost :( 0/0)
2008-05-31 10:10:46. 20 spid5s
2008-05-31 10:10:46. 20 spid5s node: 2

10:10:46. 20 spid5s key: 5: 72057594056212480 (fc0199b9be7a) cleancnt: 2 mode: s flags: 0x0
2008-05-31 10:10:46. 20 spid5s Grant List 2:
10:10:46. 20 spid5s owner: 0x2028ad80 mode: s flg: 0x0 Ref: 1 life: 00000000 spid: 67 ECID: 0 xactlockinfo: 0x533ebca4
2008-05-31 10:10:46. 20 spid5s spid: 67 ECID: 0 statement type: select line #: 1
10:10:46. 20 spid5s input Buf: RPC event: Proc [Database ID = 5 Object ID = 484053356]
2008-05-31 10:10:46. 20 spid5s requested:
10:10:46. 20 spid5s restype: lockowner stype: 'or' xdes: Unsupported mode: X spid: 59 batchid: 0 ECID: 0 taskproxy :( 0x576fe374) value: 0x1eb9ad20 cost :( 0/84)
2008-05-31 10:10:46. 20 spid5s
2008-05-31 10:10:46. 20 spid5s victim resource owner:
10:10:46. 20 spid5s restype: lockowner stype: 'or' xdes: 0x533ebc80 mode: s spid: 67 batchid: 0 ECID: 0 taskproxy :( 0x1f092374) value: 0x2962ae40 cost :( 0/0)

Node1 (spid: 59) has an exclusive lock mode: X for a resource, and needs to apply for a shared lock mode: S, node2 (spid: 67) for another resource) with the share lock of the last resource, the exclusive lock of the previous resource is requested. According to the sporadic knowledge in the school, we can still understand this truth. It is reasonable to say that there is no deadlock in this case, but if in the transaction, the shared lock will be released after the transaction ends.

There is some information on books online that can be used to view this log.

Type Column

The "type" column displays the currently locked resource types.

Resource Type
Description

RID
The row identifier used to lock a row in the table.

Key
The row lock in the index. Used to protect the key range in a serializable transaction.

PAG
Data or index page.

EXT
A group of eight adjacent data pages or index pages.

Tab
The entire table, including all data and indexes.

DB
Database.

Resource Column

The "resource" column provides information about the resources being locked.

Resource Type
Description

RID
The row identifier of the locked row in the table. A row is identified by a combination of fileid: page: RID. The RID is the row identifier in the page.

Key
The hexadecimal number used internally by SQL Server.

PAG
Page number. The page is identified by a combination of fileid: page, where, fileid isSysfilesTableFileidAnd page is the logical page number in the file.

EXT
The first page number in the extended disk area being locked. A page is identified by a combination of fileid: page.

Tab
BecauseObjidThe column already contains the table Object ID, so no information is provided.

DB
BecauseDbidThe column already contains the database ID of the database, so no information is provided.

InSp_lockIn the result set of, the resource description in the locked rid resource type is. This indicates that the lock is applied to the row with the row ID 2 in fileid 1, the page number 1225, and the page number.

The most critical information I think is

[Database ID = 5 Object ID = 1254295528] and [Database ID = 5 Object ID = 484053356]
Two objects of the lock are specified.

Select db_name (5) and the result is cwbase_zclzj.
Connect the cwbase_zclzj account set,
Select object_name (484053356) Result: erp_ls_billcoderule_generatenewbillcode
Select object_name (1254295528): erp_ls_billflownorecords_newrecordsave

It turns out that two stored procedures are a ghost and there is a deadlock when generating document numbers, because they are placed in the same thing, which verifies our above conjecture. A simple solution is not to put it in the same thing. Other solutions should be managed by maintenance personnel.

I will write it here. In fact, most of the logs still do not understand it. I will wait for the senior to explain it. If there is anything wrong with the analysis, please reply and point out that I will also learn more. Thank you!