Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to solve the problem of unable to access the website caused by deadlock, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let the editor take you to know it.

How to solve the problem of inaccessible website caused by deadlock

Problem: the website can not be accessed, after the host restarts, the website is running normally.

Solution: caused by database deadlock.

Cause of deadlock:

To extract the corresponding data of query data and modify the Stat table is to modify the same data and operate with a large amount of data. when multiple users operate at the same time, the database will be deadlocked and blocked.

Knowledge expansion:

1. SQL deadlock and blocking.

2, deadlock test method: the database operation in the program, cycle operation 10,000 times, open multiple windows to execute at the same time.

3. The method of finding the cause of database deadlock.

After the following SQL statement is run, you can find the source of the deadlock and blocking of the SQLServer.

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 (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 'caused the database deadlock:

'+ CAST (@ bl AS VARCHAR (10)) +' process number, which executes SQL syntax as follows'

Else

Select 'process number SPID:'+ CAST (@ spid AS VARCHAR (10)) +' by'+'

Process number SPID:'+ CAST (@ bl AS VARCHAR (10)) + 'blocking, whose current process executes 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

How to solve the problem of inaccessible website caused by deadlock

4. View the current process, or deadlock process, and kill the dead process automatically:

Deal with deadlock

View the current process, or deadlock process, and automatically kill the dead process. Because it is for death, if there is a deadlock process, you can only view the deadlock process. Of course, you can only look at the deadlock process through parameter control, with or without deadlock.

Call example:

Exec p_lockinfo-- * / create proc p_lockinfo @ kill_lock_spid bit=1,-- whether to kill deadlocked processes, 1 kills, 0 only displays @ show_spid_if_nolock bit=1-if there is no deadlocked process, whether normal process information is displayed, 1 shows that 0 does not display as 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, cumulative CPU time = cpu, login time = login_time, open transactions = open_tran, process status = status, workstation name = hostname, application name = program_name, workstation process ID=hostprocess, domain name = nt_domain, network card 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, s1praga.spidjue s2prag0 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, s 1 from master..sysprocesses a where blocked0) an order by s 1 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, a nvarchar (30), b Int,EventInfo nvarchar (255)) if @ kill_lock_spid=1 begin declare @ spid varchar (10), @ flag varchar (10) while @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.