Transactional updates that are applied to DB regardless of no commit or rollback

Question

I was under the impression that all updates to a SQL server database are first added the T-Log before being applied to the underlying database. In the event of the server crashing, the restore process would rollback any uncommitted transactions. This I also assumed works with transactions, if a commit or rollback is not called the changes will not be made.

So I wanted to see the reaction of SQL server to transactions being cut short. i.e. transactional updates without a commit or rollback. What I found I don’t quite understand. Especially, how SQL server can allow this to happen.

I used the script below to insert rows into a table with a delay to give me enough time to stop the transaction before it reaches the commit or rollback. This I guess would simulate the client application timing out before the transaction completed.

    Create Table MyTest (Comment varchar(20))
Go
Create Procedure MyProc 

as

Begin Try
    Begin Transaction

        Insert Into MyTest Select 'My First Entry'

        WaitFor Delay '00:00:02'

        Insert Into MyTest Select 'My Second Entry'

        WaitFor Delay '00:00:02'

        Insert Into MyTest Select 'My Third Entry'

    Commit Transaction

    Return 0 -- success
End Try

Begin Catch
    If (@@trancount<>0) Rollback Transaction

    Declare @err int, @err_msg varchar(max)
    Select @err = error_number(), @err_msg = error_message()
    Raiserror(@err_msg, 16,1)

    Return @err
End Catch

If you run the script, depending on how quickly you stop the procedure, you will see that the first one or two inserts will remain in the table. Could someone explain why this would happen?

Select * From MyTest

I tested this on SQL 2008.

Answer 1

If you begin a transaction and do not commit it or roll it back, you will simply get a hanging transaction that is likely to block other users until something is done with the current transaction. SQL Server will not automatically commit or rollback a transaction on its own, simply because your code didn't do so. The transaction will stay in place and block other users until it's committed or rolled back.

Now, I can quite easily begin a transaction in my T-SQL code, not commit or roll it back, and do a Select statement and see that data that I just inserted or updated as long as the Select statement is using the same connection as my transaction. If I attempt to do a Select using a different transaction, I won't see the inserted or updated data. In fact, the Select statement might not finish at all until the transaction on the other connection is completed.

Answer 2

Correct, TXNs are written using "Write Ahead Logging". There are MSDB articles about it and how this interacts with commit/rollback/checkpoints etc

However, a command timout (or what you are doing simply stops code executing) and the TXN is never rolled back and locks released until the connection is closed (or done later separately). This is what SET XACT_ABORT is for