The phenomenon of database blocking and deadlock:
One, the database blocking phenomenon: The first connection occupies the resource is not released, and the second connection needs to obtain this resource. If the first connection is not committed or rolled back, the second connection waits until the first connection releases the resource. For blocking, the database cannot be processed, so the database operation should be committed or rolled back in a timely manner.
Second, the phenomenon of database deadlock: The first connection occupies a resource without release, is ready to get the resources occupied by the second connection, and the second connection occupies a resource that is not freed and is ready to take the resources that the first connection occupies. This phenomenon of mutual possession of the resources that each other needs to acquire is called deadlock. For deadlocks, database processing methods: Sacrificing one connection to ensure that another connection is executed successfully.
For example, a client application thread has two open connections. The thread initiates the transaction asynchronously and issues a query on the first connection. The application then initiates another transaction, issues a query on another connection, and waits for the result. When SQL Server returns the results of one of the connections, the application begins to process the results. The application processes the result until the query that generated the result is blocked by a query executed on another connection, causing no further results to be available. At this point the first connection is blocked, waiting indefinitely for more results to be processed. The second connection is not blocked on the lock but still attempts to return the result to the application. However, as the application blocks and waits for the result on the first connection, the result of the second connection is not processed.
Here's how to view and process SQL Server 2000 blocking and deadlocks:
Copy Code code 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 the deadlock process, and automatically kill the process
Because of the deadlock, if there is a deadlock process, only the deadlock process can be viewed
Of course, you can control by parameter, whether or not deadlock, only check the view lock process
*/
/*--Call Example
EXEC P_lockinfo
--*/
Create proc P_lockinfo
@kill_lock_spid Bit=0,--whether to kill the deadlock process, 1 kill, 0 show only
@show_spid_if_nolock Bit=1-If no deadlock process, display normal process information, 1 display, 0 does not show
As
SET NOCOUNT ON
DECLARE @count int,@s nvarchar (1000), @i int
Select Id=identity (int,1,1), logo,
Process Id=spid, thread id=kpid, block process id=blocked, database id=dbid,
Database name =db_name (dbid), user Id=uid, username =loginame, Cumulative CPU time =CPU,
Login time =login_time, open transaction number =open_tran, process state =status,
Workstation name =hostname, application name =program_name, workstation process id=hostprocess,
Domain =nt_domain, network card address =net_address
Into #t from (
Select flag = ' Deadlock 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 (1,1), a nvarchar (), b int,eventinfo nvarchar (255))
If @kill_lock_spid =1
Begin
DECLARE @spid varchar (10), @ Logo varchar (10)
While @i<= @count
Begin
Select @spid = Process id,@ flag = flag from #t where id=@i
Insert #t1 EXEC (' DBCC INPUTBUFFER (' + @spid + ') ')
If @ @rowcount =0 insert #t1 (a) VALUES (NULL)
IF @ flag = ' deadlock 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 issues through Transact
