Database Deadlock Resolution __ Database

Deadlock Reason:

To extract the corresponding data of query data and modify the Stat table, all of them modify the same data, carry out large amount of data, and simultaneously, cause database deadlock and block;

Related knowledge:

1. SQL deadlock and blocking: http://searchdatabase.techtarget.com.cn/tips/275/2080775.shtml

2, Deadlock test method: The program in the database operation, circular operation 10,000 times, open multiple windows simultaneously

3, find the reasons for the database deadlock: http://www.51testing.com/html/57/785.html

methods for analyzing the reason of database deadlock
Article Origin: Reprint Author: Unknown release time: 2005-11-25
	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. ------------------------------------------------------------------------------ The process number spid:65 is blocked by the process number spid:64, and its current process executes the following SQL syntax 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. ------------------------------------------------------------------------------ The process number spid:73 is blocked by the process number spid:64, and its current process executes the following SQL syntax 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. */

After the following SQL statement is run, you can find out the source of deadlock and blocking for SQL Server

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

4, view the current process, or deadlock process, and can automatically kill the process: http://www.5do8.com/blog/news.asp?id=363

/*--Handle Deadlocks

View the current process, or the deadlock process, and automatically kill the process

Because it is dead, so 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

--Jiangjian 2004.4--*/

/*--Call Example

EXEC P_lockinfo
--*/
Create proc P_lockinfo
@kill_lock_spid Bit=1,--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
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 @ 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)
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
End
Go

-----------------------------------------------------------------------------------------------------------

A deadlock occurs if two user processes lock different resources and then attempt to lock resources that are locked by the other. At this point, SQL Server automatically selects and aborts one of the processes to unlock the deadlock, allowing another process to continue processing. The system will rollback the aborted transaction and send an error message to the user who is being rolled back.


Most well-designed applications will resubmit the transaction after receiving this error message, and the likelihood of submitting success is high. However, this often occurs on the server, which can significantly degrade server performance. To avoid deadlocks, design applications should follow certain principles, including:

▲ allow applications to access server resources in the same order each time.


▲ prohibits any user input during a transaction. User input should be collected before the transaction begins.


▲ Keep the transaction short and simple as far as possible.


▲ Specify the lowest possible isolation level for the user connection running the transaction, as appropriate. [Apply to 6.5,7.0,2000]


In addition, for the deadlock problem with SQL Server, here are some tips that are useful in practice.


Use SQL Server Profiler's Create Trace wizard to run the "identify the cause of a deadlock" trace to assist in identifying the deadlock problem, which will provide the raw data to help find the cause of the database's deadlock. [Apply to 7.0,2000]
If you cannot eliminate all deadlocks in your application, make sure that you provide a program logic that automatically resubmit transactions at random intervals after a deadlock occurs and a user transaction is aborted. The randomness of waiting time is important here because another competing transaction may be waiting, and we should not allow two competing transactions to wait for the same time and then execute them at the same time, which would result in a new deadlock. [Apply to 6.5,7.0,2000]
Simplify all T-SQL transactions as much as possible. This will reduce the number of locks of various types and help improve the overall performance of SQL Server applications. If possible, split more complex transactions into simpler transactions. [Apply to 6.5,7.0,2000]
All conditional logic, variable assignment, and other related preliminary setup operations should be done outside of the transaction and should not be placed within the transaction. Never suspend a transaction in order to accept user input, and user input should always be done outside of the transaction. [Apply to 6.5,7.0,2000]
Encapsulates all transactions within a stored procedure, including the BEGIN TRANSACTION and COMMIT TRANSACTION statements. The move helps reduce blocked locks in two ways. First, it restricts communication between the transaction Run-time client and SQL Server, so that any message between the two can only occur in a non transactional run time (reducing the time the transaction runs). Second, it prevents the user from leaving an unfinished transaction (leaving an unlocked lock) because the stored procedure forces the transaction that it initiates or completes, or aborts. [Apply to 6.5,7.0,2000]
If the client program needs to check the data for a certain amount of time first, then it may update the data, or it may not update the data, it is best not to lock the record during the entire record check. Assuming that most of the time is checking the data rather than updating the data, one way to deal with this particular situation is to select the record (without the update clause). The UPDATE clause adds a shared lock to the record and then sends it to the customer.
If the user only views the record but never updates it, the program can do nothing; Conversely, if a user decides to update a record, he can check whether the current data is the same as the data that was previously extracted, and then execute update.

Similarly, we can also check the time identity column in the record, if it exists. If the data is the same, an update operation is performed, and if the record has changed, the application should prompt the user so that the user can decide what to do with it. Although this method needs to write more code, but it can reduce the lock time and times, improve the overall performance of the application. [Apply to 6.5,7.0,2000]

Specify the least restrictive transaction isolation level for user connections as much as possible, rather than always using the default Read committed. To avoid any other problems arising therefrom, you should refer to the effects of different isolation levels and carefully analyze the characteristics of the transaction. [Apply to 6.5,7.0,2000]
The use of cursors reduces concurrency. To avoid this, you should use the READ_ONLY cursor option if you can use a read-only cursor, otherwise, if you need to make an update, try using the optimistic cursor option to reduce the lock. Try to avoid using the scroll_locks cursor option, which increases the problem caused by record locking. [Apply to 6.5,7.0,2000]
If the user complains that they have to wait for the system to complete the transaction, you should check that the resource lockout on the server is the cause of the problem. You can use SQL Server Locks object:average wait Time (ms) for such checks to measure the average latency of various locks.
If one or more types of locks can be identified that result in a transaction delay, it is possible to further explore the possibility of determining which transaction produced the lock. Profiler is the best tool for this kind of concrete analysis. [Apply to 7.0,2000]
Using sp_who and Sp_who2 (SQL Server Books Online does not have a description of Sp_who2, but Sp_who2 provides more detailed information than sp_who) to determine which users may be blocking other users. [Apply to 6.5,7.0,2000]
Try one or more of the following recommendations to help avoid blocking locks: 1 Use clustered indexes for frequently used tables, 2 try to avoid a single T-SQL statement that affects a large number of records at once, especially insert and UPDATE statements, and 3 try to have the update and DELETE statements use the index ; 4 avoid commit and fallback conflicts when using nested transactions


--------------------------------------------------------------------------------------------------------------- ----------------
Find the deadlock table

--Deadlock detection
Use master
Select * from sysprocesses where blocked<>0
--Find the SPID
EXEC sp_lock
--Find ObjID according to the SPID
Select object_name (85575343)
--Find the table name based on ObjID

-----------------------------------------------------------------------

Top Five queries

SELECT Top 5
Total_worker_time/execution_count as [Avg CPU time],
(SELECT SUBSTRING (TEXT,STATEMENT_START_OFFSET/2, Case statement_end_offset =-1 then LEN (nvarchar (max), Text) * 2 ELSE statement_end_offset End-statement_start_offset)/2) from Sys.dm_exec_sql_text (sql_handle)) as Query_ Text
From Sys.dm_exec_query_stats
Order BY [AVG CPU time] DESC

--------------------------------

Memory usage

DBCC Memorystatus