Instance analysis solution for Website access failure due to database deadlock

This article uses instances to analyze the solutions for Website access failures caused by database deadlocks.

Problem: the website cannot be accessed. After the host is restarted, the website runs normally.

Solution: Database deadlock.

Cause of deadlock:

Extracting and querying the corresponding data and modifying the Stat table are all operations that modify the same data record for a large amount of data. When multiple users operate simultaneously, database deadlock and blocking are caused;

Knowledge Development:

1. SQL deadlock and blocking.

2. deadlock Test method: The program executes database operations 10 thousand times in a loop and opens multiple windows for execution at the same time.

3. Find the cause of the database deadlock.

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

Use mastergodeclare @ spid int, @ bl intDECLARE s_cur CURSOR FORselect 0, blockedfrom (select * from sysprocesses where blocked> 0) awhere not exists (select * from sysprocesses where blocked> 0) bwhere. blocked = spid) union select spid, blocked from sysprocesses where blocked> 0 OPEN s_curFETCH next from s_cur INTO @ spid, @ blWHILE @ FETCH_STATUS = 0 beginif @ spid = 0 select 'causes a database deadlock:' + CAST (@ bl as varchar (10) + 'process number, the SQL syntax is AS follows: 'elselect' process ID SPID: '+ CAST (@ spid AS VARCHAR (10) +' quilt '+' process ID SPID: '+ CAST (@ bl as varchar (10) +' blocking. The SQL syntax executed by the current process is AS follows: 'dbcc INPUTBUFFER (@ bl) FETCH NEXT FROM s_cur INTO @ spid, @ blendCLOSE s_curDEALLOCATE s_curexec sp_who2

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

1 2 Next Page

[Content navigation]
Page 1st: instance analysis solution for website inaccessible due to database deadlock	Page 2nd: instance analysis solution for website inaccessible due to database deadlock