The transaction (process ID) and another process have been deadlocked on the lock resource, and the transaction has been selected as the deadlock victim. Run the transaction again.

The deepest cause of all deadlocks is: resource competition performance 1:
A user a accesses Table A (locking table A) and then accesses Table B
Another user B accesses table B (Table B is locked) and then attempts to access Table

At this time, user a has locked table B because user B has to wait for user B to release table B to continue. Well, the old man will have to wait honestly.
Similarly, user B has to wait for user a to release Table A to continue.
Solution:
This deadlock is caused by bugs in your program,You have no choice but to adjust the logic of your program.
Carefully analyze the logic of your program,
1: Try to avoid locking two resources at the same time
2: When two resources must be locked at the same time, ensure that the resources should be locked in the same order at any time.

Performance 2:
User A reads a record and modifies it.
This is the record modified by user B.
Here, the nature of the lock in user a's transaction is increased from the share lock attempt to the exclusive lock (for update), and the exclusive lock in user B must wait for a to release because A has a share lock.
The shared lock is released, and the exclusive lock that a cannot rise due to the exclusive lock of B cannot be released, so a deadlock occurs.
Such deadlocks are relatively hidden, but they often occur in projects that are a little larger.
Solution:
Let user a's transactions (that is, the first read and then write operations), In the SELECT statement, update lock is used.
Syntax:
Select * From Table1 with (updlock) where ....

======================================

In the database application system of online transaction processing (OLTP), The concurrency of multiple users and multiple tasks is one of the most important technical indicators of the system. To improve concurrency, most RDBMS currently adopt lock technology. However, due to the complexity of the real environment, the use of lock technology inevitably produces a deadlock problem. Therefore, the key to developing an online transaction processing system is to use the Locking Technology reasonably and effectively to minimize deadlocks.
Cause of deadlock
In the online transaction processing system, there are two main reasons for the crash. On the one hand, due to the concurrency of multiple users and multi-tasks and the integrity requirements of transactions, when multiple transactions access multiple resources at the same time, if both parties have locked a part of resources but both of them need resources locked by the other party, they will be in an infinite waiting state if they cannot fully obtain the required resources within a limited period of time, this causes deadlocks on resource requirements.
On the other hand, different database systems may encounter special deadlocks when the locking mechanism of the database itself is implemented in different ways. For example, in Sybase SQL Server 11, the minimum locks are 2 k pages instead of Row-level locks. If the number of records in a table is small and the record length is short (that is, the record density is high, such as the system configuration table or system parameter table in the application system), the Access frequency is high, it is easy to cause deadlocks on this page.
Deadlock conditions and Solutions
In the liquidation application system, deadlocks are prone to the following situations:
● Different stored procedures, triggers, and dynamic SQL statement segments Access Multiple tables in different order at the same time;
● A table with frequent records is added during the exchange, but non-cluster indexes (non-clustered) are used for the table );
● The number of records in the table is small, and a single record is short, so the Access frequency is high;
● The whole table is frequently accessed (for example, the query of the Code comparison table ).
The corresponding solution to the above deadlock situation is as follows:
● During system implementation, it should be specified that all stored procedures, triggers, and dynamic SQL statement segments always use the same sequence for operations on multiple tables. For example, if you have two stored procedures: proc1 and proc2, you need to access three tables: zltab, z2tab, and z3tab. If proc1 is accessed in the order of zltab, z2tab, and z3tab, proc2 should also access the three tables in the above Order.
● For tables with frequent Records added during the exchange, cluster indexes (clustered) are used to reduce the number of users who add records to the last page of the table and generate hot spots at the end of the table, cause a deadlock. This type of table is mostly a flow meter for current accounts. It features that a large number of records need to be appended at the end of the table during the exchange period, and the added records are not deleted or less.
● For tables with a small number of records and frequent select or updata during the exchange period, you can set the maximum row on each page to reduce the data storage density in the table, simulate row-level locks to reduce the occurrence of deadlocks on the table. Most of these tables are tables with complicated information and few records.
For example, system configuration table or system parameter table. Add the following statement when defining the table:
With max_rows_per_page = 1
● In the stored procedure, trigger, and dynamic SQL statement segments, if select operations are performed frequently on some entire tables, a deadlock may occur between the table and other users accessing the table. For non-critical statements such as checking whether the account exists but the Checked fields are not updated during the check, you can use the at isolation read uncommitted clause in the SELECT command. This method actually reduces the lock level of the SELECT statement on the entire table and improves the concurrency of other users on the table. This method is particularly effective when the system runs at a high load.
For example:
Select * from titles at isolation read uncommitted
● For the sequence number generator field of the sequential number class, you can first execute the updata sequential number field + 1, and then execute the select method to obtain the sequential number.
Summary
During the stress testing of the local clearing system, the author respectively tests the two systems that adopt the above Optimization Method and do not adopt the optimization method. When other conditions are the same, the deadlock occurs in the same number of business transactions and within the same time as below:
Systems Using Optimization Methods: 0 times/10 thousand businesses;
Systems that do not adopt Optimization Methods: 50 ~ 200 transactions/transactions.
Therefore, after using the above optimization methods, especially in the high-load operation of the system, the effect is particularly significant. In short, when designing and developing database application systems, especially OLTP systems, we should optimize the systems separately based on the specific circumstances of the application system and the above principles, it lays a good foundation for developing an efficient and reliable application system.

================
-- Transfer
/*************************************** *****************
// Create:
// Date:
// Modify:
//
// Description: view the blocking and deadlock conditions in the database
**************************************** ****************/

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 (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 ))
+ 'Process number. The SQL syntax executed by the process is as follows'
Else
Select 'process No. spid: '+ Cast (@ spid as varchar (10) + 'be'
+ '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
Go
======================================
Haha, it is not very useful to solve the deadlock. I used this stored procedure to clear the deadlock.
I used the following methods to solve the deadlock:
1. Optimize Indexes
2 for all reports, non-transactional select statements are added with the with (nolock) statement after the from statement
3. Use the same update sequence to execute all transactional updates.
Now we have solved the deadlock problem and hope it will help you.

The usage of with (nolock) is flexible. It can be said that as long as there is a from, the with (nolock) mark can be added to cancel the generation of the image lock, which can be used here in Delete update, in the from behind select and inner join, it is helpful for improving the performance of the entire system.

======================================
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 deadlock process and automatically kill the dead Process

Because it is intended for dead, if there is a deadlock process, you can only view the deadlock Process
Of course, you can use Parameter Control to check only the deadlock process, whether there is a deadlock or not.

-- Producer build 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: only show
@ Show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed
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, accumulated CPU time = CPU,
Login Time = login_time, number of opened transactions = open_tran, Process status = status,
Workstation name = hostname, application name = program_name, workstation process id = hostprocess,
Domain name = nt_domain, NIC 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,
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 (255), a nvarchar (30), B INT, eventinfo nvarchar ))
If @ kill_lock_spid = 1
Begin
Declare @ spid varchar (10), @ sign varchar (10)
While @ I <= @ count
Begin
Select @ spid = process ID, @ sign = sign from # t where id = @ I
Insert # T1 exec ('dbcc inputbuffer ('+ @ spid + ')')
If @ sign = 'deadlocked 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