One of my friends finds this issue in the systems he is managing. In my locking and blocking series, I mentioned that when a record is being modified it always acquires IX lock on the object, IX lock on the page, and X lock on the record. In rare case, this may also hold IX lock on another object. This is cased by sp_rename.

Here is code my friend gave me to re-produce the issue

-- Create tables
Create table dbo.tBase(test varchar(100))
GO
Create table dbo.tStage(test varchar(100))
GO

-- Session 1
Begin tran
Insert into tBase select 'ttt1'
-- commit

-- Session 2
Begin tran
	EXEC sp_rename 'tBase','tTemp','OBJECT'
	EXEC sp_rename 'tStage','tBase','OBJECT'
	EXEC sp_rename 'tTemp','tStage','OBJECT'
Commit

Begin tran
	Truncate table dbo.tStage
Commit

-- Session 3
Begin tran
Insert into tBase select 'ttt3'

Session 1 is inserting data into tBase. Session 2 swaps the table name between tBase and tStaging and then truncates table tStage. Session 3 inserts data into tbase. This is a very common scenario for data processing. tBase is a temporary table receiving transactions from multiple sessions. Periodically, during the night most likely, the tbase table is swapped to staging table and gets processed. After the table gets processed, the records in the staging will be wiped out for the swap in next schedule. Follow steps below to reproduce the issue

Now you will see Session 1 blocks Session 2 and Session 2 blocks Session 3. This is reasonable. Session 1 holds IX lock on the object tBase before Session 2 is requesting X lock on the object. When session 3 kicks in, it requires a IX lock on the object. Since an X lock is in front of it, Session 3 will have to wait.
Now you commit Session 1, most of the programmer may think Session 2 and Session 3 will all complete execution. but the fact you will see is that Session 2 which truncating tStaging is blocked by Session 3 which inserting record into tBase. How could that happen.

Here is the explanation
Let’s give 2 IDs for objects tBase and tStaging, ID1 and ID2.
When Session 1 blocks Session2 which blocks Session 3. the lock chain is

• IX on ID1, Session 1
• Waiting X -> ID1, Session 2
• Waiting IX > ID1, Session 3

When Session 1’s transaction completes.

• IX on ID1 released in Session 1
• X -> ID1, Session 2, performing table name swapping by sp_rename. After the table name swapping, ID1 will be table tStage and ID2 will be table tBase.
• Waiting IX -> ID1, Session 3
• X on ID1 released in Session 2 because sp_rename is done
• IX -> ID1, Session 3 now get IX lock on ID1 which is the resource this session previously waiting for, even though ID1 is not longer the interest Session 3 should have. SQL server figured that after the IX on ID1 is acquired. So Session 3 will get IX lock on ID2 (which is the current tBase table) and then performs the data modification.
• Waiting -> ID1, Session2, truncating table tStage which originally called tBase.

Now you can see that Session 3 use IX on 2 objects but only modify rows in one object. SQL Server locking resources are id based. If a session waiting for a lock on a resource, eventually, it can get it even tought the meaning of the resource is changed. Although SQL Server can figure the change out and rectify the behaviors right away but remediation will not modify what has been wrongly done and hence cause lock leaking.

To overcome this issue, try to use partition swapping rather than using sp_rename.