------ Experience of deadlock in SQL Server2000 -----

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because there are only a few transactions:

 

 

Rollback cancels all tasks performed by the transaction.

The application resubmit the rollback during the deadlock.

The following methods help minimize deadlocks:

 

 

Access objects in the same order.

Avoid user interaction in transactions.

Keep the transaction brief and in a batch.

Use a low isolation level.

Use bind connection.

Access objects in the same order

 

If all concurrent transactions access objects in the same order, the possibility of deadlock will be reduced. For example, if two concurrent transactions obtain the lock on the Supplier table and then obtain the lock on the Part table, the other transaction is blocked on the Supplier table before one transaction is completed. After the first transaction is committed or rolled back, the second transaction continues. No deadlock occurs. The stored procedure is used to modify all the data to standardize the order of objects to be accessed.

Avoid user interaction in transactions

 

Avoid writing transactions that contain user interaction, because running batch processing without user interaction is much faster than manually responding to queries, for example, responding to application request parameter prompts. For example, if the transaction is waiting for user input, and the user goes to lunch or even goes home for a weekend, the user suspends the transaction so that it cannot be completed. This will reduce the system throughput, because any lock held by the transaction will be released only when the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.

 

Keep the transaction brief and in a batch

 

A life-and-death lock is often used to concurrently execute multiple transactions that require long running in the same database. The longer the transaction runs, the longer it takes to hold the exclusive lock or update the lock, blocking other activities and possibly causing a deadlock.

 

Keeping transactions in one batch can minimize the number of network communications to and from the transaction, reduce the possible latency of completing the transaction and release the lock.

 

Low isolation level

 

Determine whether the transaction can run at a lower isolation level. The committed read operation allows the transaction to read the data that has been read (not modified) by another transaction without waiting for the completion of the first transaction. Using a lower isolation level (for example, commit read) instead of a higher isolation level (for example, serializable read) can shorten the time for holding shared locks, thus reducing lock contention.

 

Bind a connection

 

Bind the connection so that two or more connections opened by the same application can cooperate with each other. Any lock obtained by the secondary connection can be held as the lock obtained by the primary connection, and vice versa, so it will not block each other.

 

Deadlock Detection

 

If a deadlock occurs, how can we check which SQL statement or stored procedure is causing the deadlock?

 

At this time, we can use the following stored procedures to detect the process and SQL statements that cause the deadlock. The built-in system stored procedures sp_who and sp_lock of SQL Server can also be used to find blocking and deadlocks, but the method described here is not easy to use.

 

 

Use master
Go
Create procedure sp_who_lock
As
Begin
Declare @ spid int, @ bl int,
@ IntTransactionCountOnEntry int,
@ IntRowcount int,
@ IntCountProperties int,
@ IntCounter int

Create table # tmp_lock_who (
Id int identity (1, 1 ),
Spid smallint,
Bl smallint)
 
IF @ ERROR <> 0 RETURN @ ERROR
 
Insert into # tmp_lock_who (spid, bl) select 0, blocked
From (select * from sysprocesses where blocked> 0)
Where not exists (select * from sysprocesses where blocked> 0) B
Where a. blocked = spid)
Union select spid, blocked from sysprocesses where blocked> 0

IF @ ERROR <> 0 RETURN @ ERROR

-- Find the number of records in the temporary table
Select @ intCountProperties = Count (*), @ intCounter = 1
From # tmp_lock_who
 
IF @ ERROR <> 0 RETURN @ ERROR
 
If @ intCountProperties = 0
Select 'no blocking and deadlock information' as message

-- Start of Loop
While @ intCounter <= @ intCountProperties
Begin
-- Retrieve the first record
Select @ spid = spid, @ bl = bl
From # tmp_lock_who where Id = @ intCounter
Begin
If @ spid = 0
Select '+ CAST (@ bl as varchar (10) +' causes database deadlock. The SQL statement executed by 'process No. 'is AS follows'
Else
Select 'process No. SPID: '+ CAST (@ spid as varchar (10) +' quilt '+' process no. SPID: '+ CAST (@ bl as varchar (10 )) + 'blocking, the SQL syntax executed by the current process is as follows'
Dbcc inputbuffer (@ bl)
End

-- Move the loop pointer down
Set @ intCounter = @ intCounter + 1
End

Drop table # tmp_lock_who

Return 0
End

 

 

Killing locks and processes

 

How to manually kill processes and locks? The simplest way is to restart the service. But here we will introduce a stored procedure. through explicit calling, the process and lock can be killed.

 

 

Use master
Go

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [p_killspid] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [p_killspid]
GO

Create proc p_killspid
@ Dbname varchar (200) -- Name of the database for which the process is to be disabled
As
Declare @ SQL nvarchar (500)
Declare @ spid nvarchar (20)

Declare # tb cursor
Select spid = cast (spid as varchar (20) from master .. sysprocesses where dbid = db_id (@ dbname)
Open # tb
Fetch next from # tb into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill '+ @ spid)
Fetch next from # tb into @ spid
End
Close # tb
Deallocate # tb
Go

-- Usage
Exec p_killspid 'newdbpy'

 

 

View lock Information

 

How can I view the details of all locks in the system? In the enterprise management manager, we can see some process and lock information. Here we introduce another method.

 

 

-- View lock Information
Create table # t (req_spid int, obj_name sysname)

Declare @ s nvarchar (4000)
, @ Rid int, @ dbname sysname, @ id int, @ objname sysname

Declare tb cursor
Select distinct req_spid, dbname = db_name (rsc_dbid), rsc_objid
From master .. syslockinfo where rsc_type in (4, 5)
Open tb
Fetch next from tb into @ rid, @ dbname, @ id
While @ fetch_status = 0
Begin
Set @ s = 'select @ objname = name from ['+ @ dbname +'] .. sysobjects where id = @ id'
Exec sp_executesql @ s, n' @ objname sysname out, @ id int ', @ objname out, @ id
Insert into # t values (@ rid, @ objname)
Fetch next from tb into @ rid, @ dbname, @ id
End
Close tb
Deallocate tb

Select process id = a. req_spid
, Database = db_name (rsc_dbid)
, Type = case rsc_type when 1 then 'null Resource (not used )'
When 2 then 'database'
When 3 then 'file'
When 4 then 'index'
When 5 then 'table'
When 6 then 'page'
When 7 then 'key'
When 8 then 'Extended disk region'
When 9 then 'rid (row ID )'
When 10 then 'application'
End
, Object id = rsc_objid
, Object name = B. obj_name
, Rsc_indid
From master .. syslockinfo a left join # t B on a. req_spid = B. req_spid

Go
Drop table # t