How to resolve session blocking problem in SQL Server _mssql

Brief introduction

For database operators to create a session or query when creating a problem is a general situation, the following describes a very effective and do not use third-party tools to solve similar problems.

Recently began to contact the operation of the work, so I summarized a number of programs to facilitate those who do not understand the database to solve some of the less critical database problems. Similar method many theories also many, I do not delve into, is simply writes a plan, is easy for the rookie to use.

Blocking understanding

Blocking (Blocking) occurs in SQL Server when a transaction in a database session is locking a resource that one or more other session transactions want to read or modify. Typically, short time blocking is not a problem and is required by a busier application. However, poorly designed applications can lead to prolonged blocking, which unnecessarily locks resources and blocks other sessions from reading and updating them.

Example

To better illustrate, here is an example to introduce. Create a table and insert the data, then create a different session, and the coworker blocks the session. The specific code screenshot is as follows:

1. CREATE TABLE Employee

2. Insert test data

Now that we have the test table with 12 data, open another Query dialog box in SSMs (meaning to recreate a session)

3. In the new Query window, first open the transaction, and then write an INSERT statement

In this place, we can see that a transaction has been opened. But there is no end tran to terminate the transaction, so the transaction state is "open", and now run the script to look at the session that is currently seen running in the "open" state.

You can now see that the running query is in the open session as shown in the previous illustration. We executed the command but did not end it, and the DBA contacted the creator of the session to complete the transaction or rollback the transaction.

Now let's create another session, update a record and not commit, that is, let the query session's status be open. So write a statement in the new query window to do the following:

Here you will see that the system is running without completing the statement (because the last transaction did not close caused the table lock, this cannot be inserted), you can now query the blocking situation in another window, check the blocked session as follows.

As shown above, the blocked session ID is 58, because our update query has blocked 54 of the execution, 54 is the batch that we inserted the data uncommitted.

Now that we can figure out the cause of the blockage, we can resolve the blockage in a calm way.

Solve

Programme 1

In the case of understanding the business, you can use the statement of the kill session ID directly to terminate a blocked session.

Programme 2

Add the "SET LOCK_TIMEOUT 1000" statement at the beginning of the transaction that indicates that the request will be terminated if the block exceeds 1000 milliseconds.

Programme 3

Rollback or commit a transaction. This is not to elaborate.

The following is the code for all statements:

/****creating Dummy Table employee ****/CREATE table employee (Empid int NOT NULL, Name nchar (a) 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 the new session ****/BEGIN TRAN insert INTO Em Ployee Values (1245, ' George ', ' Jax ')/**** Query to check currently running sessions SELECT ****/name as DISTINCT E_name, session_id, host_name, Login_time, login_name, reads, writes from sys.dm_exec_sessions left OUTER JOIN Sys.dm_tra N_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 the 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)

Summarize

I have also used a number of different statements to query the location of blocking or even deadlock, and then solve, this is also a temporary solution. Same, in the final analysis, because there are many problems in the code and even database design, such as missing index, query performance in the transaction and logical order problems, T-SQL statement performance and so on. For some of the years to solve similar problems of the DBA is not valuable, but for people who do not understand the database can still temporarily solve some urgent problems, of course, finally, the theoretical basis to the best possible to eliminate the similar situation.

The above is a small set of SQL Server to introduce how to resolve the session blocking problem, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!