This is the 21st day of my participation in the August More Text Challenge
Remember a transaction uncommitted problem in SQL Serer that mimics a deadlock (this could just be a very unexpected problem).
What kind of transactions are uncommitted?
Let me explain what the problem is:
Simulate the execution of two transactions and generate deadlocks; The transaction (usually transaction 1) of the deadlock “victim” is terminated and rolled back; Normally, transaction 2 will complete and commit.
But the problem is that the session of transaction 2 does not seem to commit, although the number of affected rows is returned, but the query statement, the two tables involved in the transaction, is blocked and cannot be completed!!
SQL > select @@trancount; SQL > select @@trancount; Returns 1, that is, there is an unfinished transaction! Select @@trancount; select @@trancount; .
In other words, the two deadlocked transactions, as the “victim” of the deadlock transaction is terminated and rolled back, and the remaining transactions should be completed normally, although the execution of the message, but did not commit, has been holding the lock, resulting in the table can not be accessed query!!
Of course, this problem only occurred once, and after n tests, the “victim” transaction terminated and rolled back, the other transaction committed, and there were no outstanding transactions.
The follow-up did not reproduce the original problem. SQL Server2016, SSMS uses V18.9.
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.05026.. 0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042:)Copy the codeMake a note here. It also notes how to find blocked transaction processes, terminate sessions, and so on.
Simulation of a deadlock
The following simulation implements a deadlock by suspending two transactions for 10 seconds and 11 seconds respectively (using WAITFOR DELAY) :
- Transaction 1
1 - affairs
BEGIN TRANSACTION
UPDATE Product
SET product_name='deadlock'
where product_id='0001';
WAITFOR DELAY '00:00:10';
UPDATE ProductCopy
SET product_name='deadlock'
where product_id='0012';
COMMIT;
Copy the code- Transaction 2
2 - affairs
BEGIN TRANSACTION
UPDATE ProductCopy
SET product_name='deadlock'
where product_id='0012';
WAITFOR DELAY '00:00:11';
DELETE FROM Product
where product_id='0001';
COMMIT;
Copy the codeLog in to SSMS twice (open two SSMS processes) and paste the SQL statements for transaction 1 and transaction 2 in SSMS respectively.
Then click on SSMS execution of transaction 2 first, and SSMS execution of transaction 1 first. A deadlock is then generated.
Transaction 1 is aborted and rolled back to the beginning of the transaction as a deadlock “victim” :
(1 row affected)
Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Copy the codeTransaction 2 executes normally and returns a message:
(1 row affected)
(1 row affected)
Copy the codeProblem found – blocking problem after deadlock occurs
Although the above deadlock is handled by SQL Server. However, a query on the product or productCopy table will block… Cannot use two tables properly.
As shown in the figure below, there are only 4, 5 or 7, 8 data in the two tables respectively.
That is, after a deadlock occurs, while the database engine automatically handles the deadlock and rolls back the victim transaction, other transactions are executed. But there was still a problem, blocking, and the table could not be used.
If you look at the blocked session section below, you can see that the SPID of the connection that shows the blocked execution is the session in which the normally executed transaction (i.e., the transaction that is not the victim of the deadlock) of the transaction in which the deadlock occurred.
View the blocked process/session SPID
Keep a blocked query running and then look to determine which session is blocking.
Sp_who stored procedure
The SP_WHO stored procedure displays information about users, sessions, and processes in an SQL Server instance.
Sp_who returns the BLK sessionID of the blocked process (if present) or 0 if not.
View the session of the blocking process
EXEC sp_who;
Copy the code
sys.dm_exec_requestsThe system view
Sys. dm_EXEC_requests blocking_session_id returned by the system view can also show the sessionID of the blocked process.
Sys. dM_EXEC_requests returns information about each request executed in SQL Server
View the session of the blocking process
-- Check the session of the blocking process
select * from sys.dm_exec_requests where blocking_session_id>0;
Copy the code
Dm stands for Dynamic Management View
select @@spid;View the ID of the current session
select @@spid; Can return the session ID of the current connection.
The solution
Method 1: End the transaction
The SPID of the blocked session is 67. The session ID is the connection that should and has been shown to normally execute transaction 2 of the two deadlocked transactions.
Query @@trancount in the session of normally executed transaction 2. , you can see that there are still unfinished transactions, that is, transaction 2 has executed but has not finished!
SELECT @@SPID;
select @@TRANCOUNT;
Copy the code
The current session_ID (67) is the session that caused the execution of other transactions to block.
A separate commit (or rollback) is performed to end the current transaction because there is an outstanding transaction, which resolves the blocking of other transactions:
COMMIT;
Copy the code
Method 2: Abort a blocked session
1) Activity Monitor Terminates a blocked session or process
- Activity monitor using SSMS (
Activity Monitor) Terminate the session
Start the SSMS Activity Monitor in SSMS:
In the process section, find the SPID to terminate:
Right-click the current Process and select Kill Process
2) Run the KILL command to terminate the session
Directly using the KILL < session_id > | < spid >; Terminates the session of the blocking process.
KILL 67;
Copy the codeKILL spid is not a rude termination of the session, but rather a rollback of transactions that need to be rolled back, and other processing operations (such as the aborted recovery of a backup operation).
Therefore, executing the KILL spid usually does not end immediately, but is a very safe way to end a session or transaction.
Once we KILL SPID in SQL Server, it becomes an uncompleted transaction. SQL Server must undo all changes performed by the uncompleted transaction and database objects should be returned to the original state before these transactions. Due to this reason, you can see the status of SPID as KILLED\ROLLBACK once the rollback is in progress. Rollback might take less time in most of the case; however, it entirely depends upon the changes it needs to rollback.
Difference between KILL spID and KILL spID WITH STATUSONLY
If you want to kill any session connection, you can use it directly
KILL spid;
Copy the codeKILL is used to terminate the session. If there are transactions in the session that need to be rolled back, the rollback will start.
Sometimes rolling back a transaction takes a long time. If you want to know the status of the transaction being rolled back (KILL), you can run KILL (spID) WITH STATUSONLY to check.
KILL spid WITH STATUSONLY Displays the status of the transaction being rolled back and does little else.
Simulation of other transaction deadlocks
To reproduce the problem, repeat the above transaction several times; UPDATE transaction 2; DELETE transaction 2; UPDATE transaction 2;
1 - affairs
BEGIN TRANSACTION
UPDATE Product
SET product_name='deadlock'
where product_id='0002';
WAITFOR DELAY '00:00:10';
UPDATE ProductCopy
SET product_name='deadlock'
where product_id='0012';
COMMIT;
Copy the code2 - affairs
BEGIN TRANSACTION
UPDATE ProductCopy
SET product_name='deadlock'
where product_id='0012';
WAITFOR DELAY '00:00:11';
UPDATE Product
SET product_name='deadlock'
where product_id='0002';
COMMIT;
Copy the codeTransaction 2 and transaction 1 are executed in sequence.