Understanding SQL Server Recovery Process [Discussion on Transactions - II]

This is the continuation of the post I made: Understanding transaction properties with Microsoft SQL Server.

During the same discussion, we talked about how SQL Server handles incomplete transactions in the event of a power failure or system crash. In order to understand this, we need to understand a process called Recovery Process. This process takes place every time SQL Server is started or when a restore operation is performed.

Recovery Process makes sure that incomplete transactions are either rolled back or rolled forward. This is possible because necessary information is available in the log file even though data files are not updated with modifications performed by our transactions. When a modification is performed, log is updated with information such as values related to the modification, type of the change, page numbers, date and time of the beginning and end. SQL Server makes sure that the message that says “transaction is completed” is sent to the client only after relevant information is written to the log. Remember, physical log is immediately updated with our transactions but it does not update data pages in the disk (data files) immediately. It updates data pages loaded to the buffer and pages in the disk are updated by a process called Checkpoint.

Checkpoint writes updated data pages in the memory to data files. In addition to that, it updates the log file with information related to transactions that are in progress.

When Recovery Process runs, it checks for transactions that have updated the log but not updated the data file. If found, it redoes the transactions applying modifications recorded in the log to data files. This is called as redo phase of recovery.

If Recovery Process finds incomplete transactions in the log file, it checks for updated data pages in the data file related to the transaction and using information available in the log file, it undoes all modifications from the data file. This is called as undo phase of recovery.

This video shows how Recovery Process works with complete and incomplete transactions. It shows how transactions are started with the time line, how log is updated (with ) and how data files are updated (with ). And finally, it shows how Recovery Process works on all transactions and recover them either performing Redo or Undo.

Understanding transaction properties with Microsoft SQL Server [Discussion on Transactions - I]

I had a chance to discuss an old topic with few of my colleagues, it was on transactions and isolation levels. Although it is old, well known to experienced engineers, it is something new and something important to know for many. Therefore, thought to make some notes on the discussion, here is the first one.

What is a transaction? Simplest way to understand it is, consider multiple operations to be done as a single operation. In database management system world, it is a unit of work that will contain one or more operations on querying, modifying data and modifying definition of the data. With SQL Server, we implement explicit transactions using BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN. In addition to that, SQL Server supports implicit transaction and auto-commit transactions.

Transactions have four properties. Here are details we discussed on them aligning with SQL Server.

Atomicity: Transaction is considered as an atomic unit of work. As explained above, it includes one or more operations. However transaction completion is not just completing one operation, it should either complete all operations involved or none of them should be completed. SQL Server automatically rolls back transactions that have errors if it runs with auto-commit mode and we need to roll back if explicit or implicit transactions are used. If the system fails before the completion of the transaction, upon restart the service, SQL Server undoes all incomplete transactions during Recovery Process.

Consistency: This refers the state of the data (or database). When a transaction needs to be started on data, data needs to be in a consistence state in order to access. In SQL Server, if data is being modified, with default behavior, the state of the data is not consistence and transaction has to wait, unless different isolation level is used. SQL Server allows to start the transaction once data is consistence and transaction goes through all integrity rules set and completes bringing data into another consistence state.

Isolation: This refers controlling data access as transaction expects. Basically, if transaction expects to get exclusive access even for data reading, this property makes sure that data is in required consistency level for accessing. Once allowed, if other concurrent transactions require same data, access is allowed based on the way data is being accessed by the first transaction. SQL Server offers two methods for handling isolation: locking and versioning. Both locking and versioning are available in on-premises SQL Server installation and default is locking. Locking makes sure that transaction cannot read data if it is inconsistence. Versioning is bit different, if data is inconsistence, it allows to read the previous consistence state of data. This is the default for Azure SQL Databases. This can be controlled by implementing different isolation levels with transactions.

Durability: Once the transaction is committed, it is considered as durable even with a disaster. SQL Server uses write-ahead mechanism for handling this, it makes sure committed transactions are written to the transaction log before writing it to data files. If something happens before writing committed data to the data file, still data can be recovered using info recorded in the log file. Generally, at the restart of SQL Server service, it checks the transaction logs for rolling forward transactions that are written to log but data files and rolling back transaction that are incomplete.

What are ROLLBACK WORK AND COMMIT WORK?

I am sure that you are familiar with ROLLBACK TRANSACTION and COMMIT TRANSACTION but have you heard about or used ROLLBACK WORK and COMMIT WORK?

ROLLBACK WORK and COMMIT WORK work exactly same way as ROLLBACK TRANSACTION and COMMIT TRANSACTION. WORK keyword is optional and this is ISO-compatible.

Can we use this instead what we have been used? Yea it is possible and no harm at all. Only missing part is, this does not accept user-defined transaction name.

Here is a sample code using ROLLBACK WORK and COMMIT WORK.

USE AdventureWorks2014;
GO

BEGIN TRAN

UPDATE Production.Product
 SET Color = 'b'
WHERE Color = 'Black';

-- simple logic to test
IF @@ROWCOUNT > 10
BEGIN

 ROLLBACK WORK;
 PRINT 'transaction rolled back';
END
ELSE
BEGIN
 
 COMMIT WORK;
 PRINT 'transaction committed.';
END

Can we commit inner (or nested) transactions?– SS SLUG Dec 2013 – Brain Bashers - Demo IV

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about committing nested transactions.

Here is the question related to this demo;

Can we commit the inner transaction (or nested transaction) without committing the most outer transaction?

Answer is simple. The concept of nested transaction does not exist with Microsoft SQL Server though we can have multiple BEGIN TRAN and COMMIT TRAN statements respectively.

What exactly happen with inner BEGIN TRAN and inner COMMIT TRAN?
They do nothing but increases and decreases @@TRANCOUNT. Note that @@TRANCOUNT giving a value greater than one does not mean that there are more than one transactions. It means there are more than one BEGIN TRAN statements. Committing all statements is actually done by most outer COMMIT TRAN statement.

Then what is the purpose of nested transaction in SQL Server?
It is meaningless to have multiple BEGIN TRAN statements in a same scope (example, in one stored procedure). However this allows to track the count of BEGIN TRAN in nested operations; Stored procedure executes BEGIN TRAN and then calls another stored procedure that has BEGIN TRAN. The count helps SQL Server to determine at which COMMIT TRAN actions should be committed.

Are you sure?
You may ask that question. Let’s test this and see.

Let’s execute the following code. It creates a database and a table, then inserts two records after starting a transaction with BEGIN TRAN.

CREATE DATABASE TestDB

GO

USE TestDB

GO

-- create a table 

CREATE TABLE dbo.TestTable

(

    Id int PRIMARY KEY

    , Value char(500) NOT NULL

)

GO

-- start a transaction

BEGIN TRAN

-- do some actions

INSERT INTO dbo.TestTable

    (Id, Value)

VALUES

    (1, 'Test Value 1')

INSERT INTO dbo.TestTable

    (Id, Value)

VALUES

    (2, 'Test Value 2')

Let’s check the @@TRANCOUNT and bytes used for this transaction.

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

 

As you see, the count is 1 and 1408 bytes used for the transaction. Let’s have another BEGIN TRAN and some INSERTs, and check the same.

-- add another BEGIN TRAN
BEGIN TRAN

-- do some actions
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (3, 'Test Value 3')
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (4, 'Test Value 4')


SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

It increases the count and bytes used. Now, let’s have one COMMIT TRAN statement and check again.

COMMIT TRAN

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

Now @@TRANCOUNT is again 1 but number of bytes used is same. This clearly shows that inner COMMIT TRAN statements do not do anything other than decreasing @@TRANCOUNT. Let’s execute the COMMIT TRAN again and see.

COMMIT TRAN

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

Everything is clear now. This proves that inner BEGIN TRAN and COMMIT TRAN do not do anything other than changing the @@TRANCOUNT.

Can I rollback part of the actions performed in my transaction?
Yes, it is possible with SAVE TRAN statement. It allows to set savepoints in the transaction and rollback the transaction to the savepoint.