SQL Server - What access do I have during a transaction rollback?

Question

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.

Answer 1

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.

Answer 2

You should look at this from two different points of view - that of the connection that issued the ROLLBACK and that of all other connections.

The issuing connection is easy: no, you do not have access. The ROLLBACK is performed synchronously. This connection cannot do anything else until the ROLLBACK succeeds. (And if it fails for any reason you're in a world of pain and likely talking to MS support.)

For all other connections there is no change. Whatever they could do the instant before the ROLLBACK was initiated they can do while it is in progress. Whether transaction B can read a value locked by transaction A will depend on the type of lock A holds and the isolation level B is running under. For those combinations which offer the highest guarantees of data integrity B will usually wait for A to finish and release locks. Should B manage to read a value that A has locked, and A changes the value, the DBMS will not magically reach into the application and change the formerly-read value. Note this is not the same as saying that if B read the same row a second time it would receive the same outcome.

As a transaction performs work (i.e. changes values in various rows) locks are taken¹. These locks prevent the various concurrent activities within the DBMS from corrupting one-anothers' work. They allow multiple users to connect to a single database and perform work in parallel while keeping the data in tact. They help implement concurrency and isolation - two big topics which are well worth getting familiar with. Locks can be taken on a row, on a page or on a table. If a single row is locked by one connection all other rows will be available to other connections for work. If a whole table is locked then no other connection can work on that table until the lock is released. (I'm gliding over a whole world of subtilties here to keep to the point.) The locks are taken incrementally, one at a time, as work proceeds. Additionally the "before" and "after" values of any changed data are stored in the log file.

During a ROLLBACK the DBMS works backwords through the log file reversing out each change made, taking the "before" values and replacing them into the table's data. By the time it works back to the BEGIN TRANSACTION statement all tables' values will be back as they started and we are guaranteed that no other user will have performed conflicing changes because of the locks.

Concievably the ROLLBACK could release locks as it undoes changes. I rather think it doesn't, however. As work was being done initially there may have been several reasons to take a lock on a row, say it was updated twice. For incremental lock release on ROLLBACK to work both of these updates would have to be recorded, plus the lock itself, which is a lot of internal book keeping for what is, hopefully, a rare event. So I rather think that all locks are held until all data is back as it started then all locks are released.

At this point all other transactions can proceed as if the rolled-back transaction never existed. Those that were waiting on the previously-locked items are roused from their slumber and allowed access to the now unlocked values.

¹ There are many variations on this. The isolation levels are important. Multivalue concurrency control is important. I'm limiting the dicussing to try and answer the actual question as succinctly as possible without getting into all the configuration options, caveats and special cases.