SQL server 2000 blocking and deadlock check and Solution

Database congestion and deadlock:

1. Database blocking: the first connection occupies the resource but is not released, and the second connection needs to obtain the resource. If the first connection is not submitted or rolled back, the second connection will wait until the first connection releases the resource. The database cannot handle blocking operations. Therefore, database operations must be submitted or rolled back in a timely manner.
2. Database deadlock: resources occupied by the first connection are not released, and resources occupied by the second connection are ready to be obtained. Resources occupied by the second connection are not released, prepare to obtain the resources occupied by the first connection. This phenomenon of mutual possession of the resources required by the other party is called a deadlock. For deadlocks, the database processing method: Sacrifice a connection to ensure that another connection is successfully executed.

For example, a client application thread has two open connections. This thread starts the transaction asynchronously and sends a query on the first connection. The application then starts other transactions, issue queries on another connection, and wait for the results. When SQL Server Returns a connection result, the application starts to process the result. The application processes the results in this way until the query of the generated results is blocked by the query executed on another connection, resulting in no available results. At this time, the first connection is blocked and will wait for more results to be processed indefinitely. The second connection is not blocked by the lock, but still tries to return the result to the application. However, the result of the second connection will not be processed because the application is blocked and the result is waiting for the first connection.

The following describes how to view and handle SQL server 2000 blocking and deadlock:

Copy codeThe Code is as follows:
Use master -- must be created in the master database
Go
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [p_lockinfo] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [p_lockinfo]
GO
/* -- Handle deadlocks
View the current process or deadlock process and automatically kill the dead Process
Because it is for deadlocks, if there is a deadlock process, you can only view the deadlock Process
Of course, you can use Parameter Control to check only the deadlock process, whether there is a deadlock or not.
*/
/* -- Call example
Exec p_lockinfo
--*/
Create proc p_lockinfo
@ Kill_lock_spid bit = 0, -- whether to kill the deadlock process; 1: Kill; 0: only display
@ Show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed
As
Set nocount on
Declare @ count int, @ s nvarchar (1000), @ I int
Select id = identity (int, 1, 1), flag,
Process ID = spid, thread ID = kpid, block process ID = blocked, Database ID = dbid,
Database Name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = cpu,
Login Time = login_time, number of opened transactions = open_tran, Process status = status,
Workstation name = hostname, application name = program_name, workstation process ID = hostprocess,
Domain name = nt_domain, NIC address = net_address
Into # t from (
Select flag = 'deadlocked process ',
Spid, kpid, a. blocked, dbid, uid, loginame, cpu, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = a. spid, s2 = 0
From master .. sysprocesses a join (
Select blocked from master... sysprocesses group by blocked
) B on a. spid = B. blocked where a. blocked = 0
Union all
Select '| _ victim _> ',
Spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = blocked, s2 = 1
From master .. sysprocesses a where blocked <> 0
) A order by s1, s2
Select @ count = @ rowcount, @ I = 1
If @ count = 0 and @ show_spid_if_nolock = 1
Begin
Insert # t
Select flag = 'normal process ',
Spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, cpu, login_time,
Open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
From master .. sysprocesses
Set @ count = @ rowcount
End
If @ count> 0
Begin
Create table # t1 (id int identity (255), a nvarchar (30), B Int, EventInfo nvarchar ))
If @ kill_lock_spid = 1
Begin
Declare @ spid varchar (10), @ sign varchar (10)
While @ I <= @ count
Begin
Select @ spid = process ID, @ sign = sign from # t where id = @ I
Insert # t1 exec ('dbcc inputbuffer ('+ @ spid + ')')
If @ rowcount = 0 insert # t1 (a) values (null)
If @ sign = 'deadlocked process' exec ('Kill '+ @ spid)
Set @ I = @ I + 1
End
End
Else
While @ I <= @ count
Begin
Select @ s = 'dbcc inputbuffer ('+ cast (process ID as varchar) +') 'from # t where id = @ I
Insert # t1 exec (@ s)
If @ rowcount = 0 insert # t1 (a) values (null)
Set @ I = @ I + 1
End
Select a. *, the SQL statement of the Process = B. EventInfo
From # t a join # t1 B on a. id = B. id
Order by process ID
End
Set nocount off
Go

SQL server 2000 blocking and deadlock can be solved through Transact-SQL.