An incisive explanation of SQL Server deadlock

Server

sysprocesses
The sysprocesses table holds information about the processes running on the Microsoft®sql Server™. These processes can be either client processes or system processes. sysprocesses is stored only in the master database.

Column name Data type description
The SPID smallint the SQL Server process ID.
Kpid smallint the Microsoft Windows NT 4.0® thread ID.
Blocked smallint the process ID (spid) of the chunking process.
waittype binary (2) reserved.
waittime int The current wait time, in milliseconds. 0 when the process is not waiting.
Lastwaittype NCHAR (32) represents the string of the last or current wait type name.
Waitresource nchar (32) A textual representation of the lock resource.
dbid smallint The database ID that is currently being used by the process.
UID smallint the user ID that executes the command.
Cumulative CPU time for the CPU int process. The entry is updated for all processes, regardless of whether the SET STATISTICS time on option is on or off.
Physical_io the cumulative disk reads and writes of the int process.
The number of pages in the process cache that the Memusage int is currently assigned to the process. A negative number that indicates that the process is releasing memory allocated by another process.
The time that the login_time datetime client process logged on to the server. For a system process, the time that the SQL Server startup occurs is stored.
The time that the Last_batch datetime client process last executed a remote stored procedure call or EXECUTE statement. For a system process, the time that the SQL Server startup occurs is stored.
Ecid smallint is used to uniquely identify the execution context ID of a child thread that represents an operation on a single process.
Open_tran the number of open transactions for the smallint process.
Status nchar (30) Process ID status (such as run, hibernate, etc.).
Globally unique identifier (GUID) for the SID Binary (85) user.
Hostname NCHAR (128) The name of the workstation.
Program_name NCHAR (128) The name of the application.
Hostprocess NCHAR (8) Workstation process ID number.
CMD nchar (16) the currently executing command.
Nt_domain NCHAR (128) client's Windows NT 4.0 domain (if you are using Windows authentication) or trust connected Windows NT 4.0 domains.
Nt_username NCHAR (128) The Windows NT 4.0 user name for the process (if you are using Windows Authentication) or trust the connected Windows NT 4.0 username.
Net_address NCHAR (12) is assigned to the unique identifier of the network interface card on each user station. When the user logs on, the identifier is inserted into the net_address column.
Net_Library NCHAR (12) is used to store columns for the Client network library. Each client process is entered on a network connection. Network connections have a network library associated with these processes that enables these processes to establish connections. For more information, see Client and server Net-library.
Loginame NCHAR (128) login name.

Nmliwei http://www.**************.com 2006-11-16 08:18

What is the condition of the deadlock?

Ah Chu http://www.**************.com 2006-11-16 08:42

A deadlock occurs when there is a circular dependency between two or more threads of a group of resources.

Deadlocks are a state that can occur in any multithreaded system, not just in a relational database management system. A thread in a multithreaded system may acquire one or more resources (such as locks). If the resource being fetched is currently owned by another thread, the first may have to wait for the owning thread to release the target resource. This means that the waiting thread is related to the owning thread on that particular resource.

If the owning thread needs to acquire another resource, which is currently owned by the waiting thread, this becomes a deadlock: two threads cannot free resources until the transaction is committed or rolled back, and they cannot commit or roll back the transaction because they are waiting for resources owned by the other. For example, a thread running transaction 1 T1 has an exclusive lock on a Supplier table. The thread running transaction 2 T2 has an exclusive lock on the part table, and then requires a lock on the Supplier table. Transaction 2 cannot get this lock because transaction 1 already owns it. Transaction 2 is blocked, waiting for transaction 1. Then, transaction 1 requires a lock on the part table, but the lock cannot be obtained because transaction 2 locks it up. A transaction cannot release a held lock until it is committed or rolled back. They cannot be committed or rolled back because the transaction requires a lock controlled by the other to continue.

Deadlocks are often confused with normal blocking. The second transaction waits for the lock to be freed when one transaction locks the resource required by another transaction. By default, SQL Server transactions do not timeout (unless LOCK_TIMEOUT is set). The second transaction is blocked instead of being deadlocked.

Nmliwei http://www.**************.com 2006-11-16 09:52

I mean, in the sysprocesses, check the deadlock where the conditions should be written, not to ask everyone to die. The definition of the lock, comrades.

[This post was last edited by Nmliwei at 2006-11-16 17:54]

Ah Chu http://www.**************.com 2006-11-16 10:32

Sorry, I didn't get what you meant at the beginning.

It might help to find the following.

Often see the problem of deadlock, generally is the kill process, but if you do not detect the cause of the deadlock, deadlock will often occur
Can be found by the operation of the deadlock, you can easily resolve the deadlock, is the day-to-day solution to the problem of the summary, may be helpful to everyone

1\ deadlock occurs, query for a deadlock-causing operation through the following syntax

Use master
Go
declare @spid int, @bl int
DECLARE S_cur CURSOR for
Select 0, blocked
From (SELECT * from sysprocesses where blocked>0) a
Where NOT EXISTS (SELECT * from sysprocesses where blocked>0) b
where A.blocked=spid)
Union select spid,blocked from sysprocesses where blocked>0
OPEN S_cur
FETCH NEXT from S_cur into @spid, @bl
While @ @FETCH_STATUS = 0
Begin
If @spid =0
Select ' Cause database deadlock is: ' + CAST (@bl as VARCHAR (10)) + ' process number, which executes the SQL syntax as follows '
Else
Select ' Process number spid: ' + cast (@spid as VARCHAR (10)) + ' + ' process # spid: ' + cast (@bl as VARCHAR (10)) + ' blocked, its current process executes the SQL syntax as follows '
DBCC InputBuffer (@bl)
FETCH NEXT from S_cur into @spid, @bl
End
Close S_cur
Deallocate s_cur

EXEC Sp_who2
2\ Find programs/databases, where this t_sql syntax is used
3\ analysis to find and solve the problem

EG:

/*
-------------------------------------------------------
The database deadlock is caused by the 71 process number, which executes the following SQL syntax

EventType Parameters EventInfo
-------------- ---------- ------------------------------------------------
Language Event 0
SELECT * FROM Test
Insert test VALUES (1,2)

(The number of rows affected is 1 rows)

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

------------------------------------------------------------------------------
The process number spid:64 is blocked by the process number spid:71, and its current process executes the following SQL syntax

EventType Parameters EventInfo
-------------- ---------- ------------------------------------------------
Language Event 0
SELECT * FROM Test
Insert test VALUES (1,2)

(The number of rows affected is 1 rows)

DBCC execution completed. If DBCC prints an error message, contact your system administrator.
                                                     
---------- --------------------------------------------------------------------
Process number spid:65 is blocked by the process number spid:64, The SQL syntax that its current process executes is as follows

EventType Parameters EventInfo
-------------- ---------- --------------------------------------------------------------------------------------- -----------
Language Event 0 BEGIN Tran
SELECT * FROM Test with (Holdlock)
waitfor time ' 12:00 '
SELECT * FROM Test
Commit

(The number of rows affected is 1 rows)

DBCC execution completed. If DBCC prints an error message, contact your system administrator.
                                                     
---------- --------------------------------------------------------------------
Process number spid:73 is blocked by the process number spid:64, The SQL syntax that its current process executes is as follows

EventType Parameters EventInfo
-------------- ---------- --------------------------------------------------------------------------------------- -----------
Language Event 0 BEGIN Tran
SELECT * FROM Test with (Holdlock)
waitfor time ' 12:00 '
SELECT * FROM Test
Commit

(The number of rows affected is 1 rows)

DBCC execution completed. If DBCC prints an error message, contact your system administrator. */