When a process in SQL Server performs an operation (query or data modification) against a table, it performs a lock on it.
There are different types of locks depending on the operation and its configuration:
Live locks: Temporary locks on a table.
Dead/cyclic Blocks: It is when two transactions are locking two tables, and each is waiting for the other to complete.
When Dead lock: Sql does Auto-kill 1 process. If not, then you can identify the less important process and kill it.
When does lock ends: Any Query/DML continue to till commit/rollback.
So to reduce locks, divide queried data into batch(iterations), and partition tables.
Why do locks happen: One of three reasons: Poor concurrency, index, or query.
Any DBA can reduce not eliminate blocks.
Xlock (x) vs nolock (IS)
How to check for locks: Check .waitresource (format: DBId:fileId:pageId, example – 4:1:172 ) in:
SELECT * FROM SYSPROCESSES
To further investigate, capture the SQL Process ID (SPID) from above query and run:
Then check the view: Sys.dm_tran_locks, check the column request_mode, it contains the lock type. Some important values are:
X: exclusive lock.
IX:intent exclusive lock.
IS: intent Shared lock.
Also check resource_associated_enttity_Id column : this is the TableID
Then once you are sure that the process is not important, you can kill it by applying the command:
After killing a process, this process will be in a ROLLBACK state: basically undoing what it attempted to do. This rollback can take time up to or even more than the original process running period. To have an estimate on the time to complete such rollback, run this:
KILL SPID WITH STATUSONLY