Sql-server – SQL Server – What access do I have during a transaction rollback

lockingrollbacksql server

As the title says, do I have read/write access to my entire database while it is rolling back a transaction?

Or read/write access to only the tables that the transaction did not write to? What is the locking behavior?

If I do have read access, what happens when I am reading a table where a transaction rolling back wrote to (does the query fail or just wait)?

Sorry for the questions, I have tried reading online (ROLLBACK TRANSACTION (Transact-SQL)), but it seems to not touch on this level of depth.

Best Answer

The answer really depends largely on your isolation level, what type of locks are held by the transaction being rolled back, and what your non-rollback session is trying to do.

Step 1) What is locked?

Before the ROLLBACK was issued, that transaction had done work and acquired locks. You can see details on those locks by looking in sys.dm_tran_locks:

SELECT tl.resource_type, tl.resource_associated_entity_id,
       tl.request_status, tl.request_mode, tl.request_session_id,
       tl.resource_description
FROM sys.dm_tran_locks tl
WHERE tl.request_session_id = <ROLLBACK session id>;

The resource_type and resource_associated_entity_id will tell you what is locked. If the resource_type is "Object" then resource_associated_entity_id is an object_id. If resource_type is "Key" or "Page" then resource_associated_entity_id is a hobt_id. There are more types to decode, but those two cover most scenarios. 

SELECT tl.resource_type, tl.resource_associated_entity_id,tl.resource_database_id,
       CASE WHEN resource_type = 'OBJECT' 
                THEN object_name(tl.resource_associated_entity_id,tl.resource_database_id)
            WHEN resource_type IN ('KEY','PAGE')
                THEN object_name(p.object_id)
        END AS resource_name,
       tl.request_status, tl.request_mode, tl.request_session_id,
       tl.resource_description
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id
                           AND tl.resource_database_id = db_id()
WHERE tl.request_session_id = <ROLLBACK session id>;

Lastly, the lock mode gives information on how that lock is being used. BOL gives a rundown of all the different lock modes.

Step 2: What will be blocked?

If the transaction being rolled back does not have a lock on an object, then the ROLLBACK won't block other sessions. That part is easy.

Whether these locks cause blocking is going to depend on your isolation level, and what you're trying to do. There's a very in-depth series on isolation levels by Paul White, which you could read for lots of details.

Different combinations of reading, writing, and isolation levels will have different blocking outcomes. Generally speaking, writes always block writes. However, writes may or may not block reads. Under SQL Server's default READ_COMMITTED isolation level, writes will block reads. If you are using READ_COMMITTED_SNAPSHOT (aka "RCSI" aka "Read Committed with Row Versioning"), then writes will not block reads.

In all cases, if another session is blocked by the session in ROLLBACK, the waiting session would just wait until the ROLLBACK released the lock.

Related Question