How to solve the session blocking problem in SqlServer, sqlserversession

How to solve the session blocking problem in SqlServer, sqlserversession

Introduction

It is common for database O & M personnel to create sessions or query problems. The following describes a very effective method to solve similar problems without using third-party tools.

Recently, I started to work with O & M, So I summarized some solutions to help colleagues who do not know the database to solve some minor database problems. There are a lot of theories about similar methods, so I will not go into details, that is, simply write a scheme for cainiao to use.

Blocking Comprehension

In SQL Server, when a transaction in a database session is locking one or more resources that other session transactions want to read or modify, Blocking will occur ). It is usually no problem to block for a short period of time, and it is required by busy applications. However, poorly designed applications can lead to long-time blocking, which means you don't have to lock resources and block other sessions from reading and updating them.

Example

For better illustration, the following is an example. Create a table, insert data, and create different sessions. Colleagues can block sessions. The specific code is as follows:

1. Create Table Employee

2. Insert Test Data

Now we have a test table with 12 pieces of data. Open another query dialog box in SSMS (meaning a new session is created)

3. In the new query window, start the transaction and write an insert statement.

Here we can see that a transaction is started. However, there is no end tran to terminate the transaction. Therefore, the transaction state is "open". Now, run the script to check whether the current session is in the "open" state.

Now we can see that the session in the open state is being queried as shown in the following figure. If we execute this command but do not finish it, DBA will contact the creator of this session to complete the transaction or roll back the transaction.

Now let's create another session and update a record without submitting it. That is, let the query session State be "open ". Therefore, write a statement in the new query window to execute the following:

Here we can see the status of the statement not completed after the system is running (because the previous transaction is not closed, resulting in the table lock, this cannot be inserted). Now we can query the blocking situation in another window, check the blocked session as follows.

As shown above, the blocked session ID is 58, and 54 of the execution is blocked due to our update query. 54 is the batch processing of the uncommitted data inserted.

Now we can figure out the cause of the blocking and solve it with ease.

Solution

Solution 1

When you understand the business, you can directly use the kill session ID statement to terminate a blocked session.

Solution 2

Add the "set lock_timeout 1000" statement at the beginning of the transaction to be executed. This indicates that the request will be terminated if the blocking duration exceeds 1000 milliseconds.

Solution 3

Roll back or commit a transaction. This is not detailed.

The code for all statements is as follows:

/****Creating dummy table Employee ****/ CREATE TABLE Employee ( Empid int NOT NULL, Name nchar(10) NULL, City nchar(10) NULL ) ON [PRIMARY] GO /**** Insert dummy data in Employee table *****/ Insert into Employee Values(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'), (1175,'Pete','Topeka'), (875,'Petron','Vienna'), (2311,'Kohli','Mumbai'), (1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'), (957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), (2954,'Ganeshan','Mcclean')/***** Insert query in new session ****/ BEGIN TRAN Insert into Employee Values(1245,'George','Jax') /**** Query to check currently running sessions ****/ SELECT DISTINCT name AS database_name, session_id, host_name, login_time, login_name, reads, writes FROM sys.dm_exec_sessions LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id WHERE resource_type <> 'DATABASE' --AND name ='specific db name' ORDER BY name/**** update query in new session ****/ update Employee set name = 'SHERAZ' where empid = 1245 /**** Query to check blocking queries with session id ****/ SELECT session_id, blocking_session_id, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle); /*** Command if you want to kill blocking session ****/ kill (54)

Summary

I have also used a variety of different statements to query, locate, block, or even deadlock, and then solve the problem. Here is also a temporary solution. The root cause is the blocking caused by many problems in code and database design, such as missing indexes, query performance and logic order problems in transactions, T-SQL statement performance and so on. Some DBAs who solve similar problems all the year round have no value, but those who do not understand the database can solve some urgent problems temporarily, of course, in the end, we still need to lay a good theoretical foundation to try to eliminate similar situations as much as possible.

The above section describes how to solve the session blocking problem in SqlServer. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!