SQL blocking (excerpt from Network)

/*

The so-called "blocking" refers to a transaction in a database session that is locking resources that other session transactions want to read or modify.

Causes requests made by these sessions to enter a waiting state. SQL Server defaults to allow blocked requests to wait indefinitely,

Until the original transaction releases the associated lock, or until it times out (according to SET Lock_timeout), the server shuts down,

Process is killed. In general systems, occasional short-time blockages are normal and reasonable, but poorly designed programs can cause long-term blockages,

This eliminates the need to lock resources unnecessarily and blocks the need for other sessions to be read or updated. In this case, it may be necessary to manually exclude the blocked state.

*/

--The lock timeout in the current session

--The execution result defaults to-1, which means that the object or record to be accessed is locked for an indefinite period of time.

SELECT @ @LOCK_TIMEOUT

--The next 3000, which is in milliseconds, that will wait for the locked object for 3 seconds

SET Lock_timeout 3000

--set lock_timeout-1

--in SSMS, two sessions (query window) are tested, session A creates a blocking transaction process, and session B accesses the locked record.

--Session A

BEGIN TRAN;

UPDATE Orders SET employeeid=7 WHERE orderid=10248

--rollback; -intentionally not committing or rolling back

--Session B

SELECT * from Orders WHERE orderid=10248

/*

MSG 1222, Level 16, State 51, line 3rd

The lock request time-out period has been exceeded.

*/

--when blocking occurs, the following command shows which session ID was blocked, and how many "milliseconds" (ms) elapsed during the session

Select blocking_session_id, Wait_duration_ms, session_id from Sys.dm_os_waiting_tasks

--through the following two commands, we can also see the lock and block details of the entire database:

SELECT * FROM Sys.dm_tran_locks

EXEC sp_lock

--another kill command that directly kills the process that caused the blockage, as follows:

KILL 53

-To solve the problem of waiting indefinitely, there is a more convenient way to do this than the SET lock_timeout command described above.

--Add the WITH (NOLOCK) keyword after the table name to indicate that SQL Server is required, without having to consider the locking state of the table.

--so you can also reduce the chance of a deadlock (dead lock) happening. However, with (NOLOCK), INSERT, UPDATE, DELETE are not available.

SELECT * from Orders with (NOLOCK) WHERE orderid=10248

--similar to the function, before the SQL statement, set the "Transaction isolation Level" to "dirty Read (Dirty Read)".

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * from Orders WHERE orderid=10248

--to know if a blockage has occurred, of course, look at the Master Library's sysprocess table to see if any process is blocking the other process, the following statement:

Select * FROM Master. sysprocesses where blocked > 0

EXEC sp_lock

--there is a blocked = 51 blocking a lot of processes (see blocked columns visible), in order to further identify the blocking statements, use the following statement

DBCC INPUTBUFFER (51);

--spid = 51 this line, Mode = X indicates an exclusive lock, status = Wait indicates waiting (that is, is blocked), dbid = 14 is the database Id,objid = 206623779 is the object ID of the lock, we can get the database and the table by the following functions: SPID = 51 this line, Mode = X indicates an exclusive lock, status = Wait indicates waiting (that is, is blocked), dbid = 14 is the database Id,objid = 206623779 is the object ID of the lock, we can get the database and the table by the following functions:

Select db_name (@dbid)-----> book_db

Select object_name (@objid)-------> T_book

--that is, the T_book table of the book_db library is locked, and then the p_book_content stored procedure is checked carefully.

-Relevant information can be found in book online,

--Keywords: sp_lock, sysprocesses, DBCC INPUTBUFFER, db_name (), object_name ()

This article is from the "Imagine the Sky" blog, please be sure to keep this source http://kinwar.blog.51cto.com/3723399/1410842