Introduction to SQL Server Transactions (Transaction Isolation Part 4)

This is forth part of "Introduction to SQL Server Transaction" series. You can see previous sections below:

Part 1

Part 2

Part 3

In this part we discuss two SQL Server isolation levels in deep.

Read Uncommitted – The “Wild West” of Isolation

Read Uncommitted is the lowest isolation level. It allows dirty reads, non-repeatable reads, and phantoms – basically all issues relate to concurrency can be seen in this isolation level. No locks are taken on reads, so you can read uncommitted changes. 

We can set the isolation level to Read Uncommitted on SQL Server using statement like below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Also in query level we can use query hint (NOLOCK) to force Read Uncommitted isolation level.

Example: Dirty-read scenario. A pending payment transaction withdraws $45 from Betty’s $78 balance, then rolls back. Meanwhile an ATM transaction (right) reads the balance as $33. The ATM saw a dirty value that never became final.

Demonstration:

-- Session A: Begin a transaction and update, but do not commit immediately.

BEGIN TRAN;

SELECT Balance FROM BankAccount WHERE AccountNumber = 'Betty'

-- This return $78

UPDATE BankAccount

SET Balance = Balance - 45

WHERE AccountNumber = 'Betty';

-- Wait or do other work before committing/rolling back...

WAITFOR DELAY '00:00:10';  

ROLLBACK;  

-- Session B (simulated concurrently): Read without locking

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT Balance FROM BankAccount WHERE AccountNumber = 'Betty';

SELECT in Session B will return 33 (assuming you have run the Session B within 10 second delay in Session A). But real balance is 78.

Under Read Uncommitted, SQL Server does not issue any shared locks on reads, so a select can return “dirty” changes from another transaction. The only advantage is maximum concurrency (no waiting), but at the cost of consistency: all anomalies can happen.

Read Committed – Default Safe Reads

Read Committed is the SQL Server default isolation level (when no explicit level is set). It prevents dirty reads: you will never see another transaction’s uncommitted changes. However, it still allows non-repeatable reads and phantoms (unless you enable row-versioning, more about this in later blog posts). 

In locking mode (SQL Server’s default), a read places shared locks that last only for the duration of each statement. If another transaction has an exclusive lock on a row, your SELECT will wait until that transaction commits or rolls back. Thus, Read Committed “protects” your reads from dirty data.

-- Session A: Start and update a row, but hold it.

BEGIN TRAN;

UPDATE BankAccount

SET Balance = Balance - 45

WHERE AccountNumber = 'Betty';

-- (Session A, not yet committed), carrying out other statement execution

-- Session B: Default Read Committed SELECT

SELECT Balance FROM BankAccount WHERE AccountNumber = 'Betty';

-- Session B will *wait* here until Session A commits or rolls back.

As you can see above, session B waits, in executing query mode (no results).

-- If Session A commits, B then sees the new balance (i.e. 33) ; if A rolls back, B sees the old balance (i.e. 78).

COMMIT;  -- Session A eventually commits or rollbacks

This will be the result in Session B:

In above demonstration, SQL server used row level exclusive lock in Session A. Then because of the default isolation level (i.e. Read committed), Session B has used shared lock. However, since "Betty's" row is exclusively locked by Session A, Session B couldn't get the shared lock on the row. Therefore, Session B (select) had to wait till Session A committed or rollback the transaction.

Since Session B had to wait, till Session A finishes, there were no dirty reads (didn't see the temporary balance of 33). But as you can see, we now have locking (i.e. Session B waiting) scenario. This is why you should handle isolation levels very carefully.

Furthermore, data can be changed by other transactions between individual statements… resulting in nonrepeatable reads or phantom data”. In other words, each SELECT in your transaction sees only committed data at that moment, but different SELECTs in the same transaction might see different results if other transactions commit in between.

Introduction to SQL Server Transactions (Transaction Isolation Part 3)

This is third part of "Introduction to SQL Server Transaction" series. You can see previous sections below:

Part 1

Part 2

In this part we discuss how SQL server has implemented concurrency control.

Locking and Versioning

SQL server uses following two techniques to implement concurrency control:

1. Locking
2. Versioning

Locking

Locking is the traditional mechanism SQL Server uses to isolate transactions.

When a transaction accesses data, SQL Server places locks on the data to prevent other transactions from making conflicting changes. Locking type and granularity decide the effect of the lock and the scale.

Locking Types: 

There are different types of locks SQL server can placed. Each lock type has some level of restrictions for other transactions. Here is summary of locking types and what it blocks:

We will have a talk about type of locks and locking in detail in future blogs.

Locking Granularity: Locks can be applied at row level, page level, table level, or even database level. Granularity allows SQL server to not to lock more objects than it required.

When lock is placed on row level, only that row is restricted from accessed by other transactions. Other rows are free to read and write operations, from other transactions. This reduce the blocking

Page level locks on the other hand locks all rows in that page from read or modification (depend on lock type). Same with table and database level locks, they lock more rows, hence more data and prone for more blocking issues.

Locking is primarily used in Read Committed, Repeatable Read, and Serializable isolation levels.

Versioning

Versioning uses a multi-version concurrency control (MVCC) approach. Instead of locking data for readers, SQL Server maintains row versions (multiple version of a row) in tempdb, allowing readers to see a consistent snapshot of the data.

When a row is modified, SQL Server keeps an older version in tempdb so that readers can still access the version valid at the start of their transaction or query.

This is a much recent technique and Isolation levels using versioning are Read Committed Snapshot (RCSI) and Snapshot Isolation (SI).

Versioning helps reduce blocking and deadlocks, improving concurrency.

Introduction to SQL Server Transactions (Transaction Isolation Part 2)

This is second part of "Introduction to SQL Server Transaction" series. You can see previous section here.

In previous module we learned about basics of SQL Server Transaction and properties of the Transactions.

In there, we discussed that among the four ACID properties of a transaction, Isolation is the one that can be modified in SQL Server. In this module, we will delve deeper into the Isolation property to understand its significance.

Why Do We Need Different Isolation Levels? The Concurrency Conundrum

When multiple transactions run simultaneously, they can interfere with each other in undesirable ways. Here are few of those scenarios you might encounter:

1. Dirty Reads: 

Transaction B reads data that Transaction A has changed, but Transaction A hasn't committed (saved) yet. If Transaction A then rolls back (undoes its changes), Transaction B has read data that technically never existed (i.e. dirty data).

Analogy: Let us take a look at Bank Money transfer example again. Transaction A is doing a money transfer between 2 accounts. But before it commit (save) its changes, Transaction B is reading account balances for a report that manager wants. 

• If there are no isolation between transactions, and if Transaction A fails before it commit its changes, Transaction B has read wrong data for the report. Therefore, this is called Dirty Reads, which will leads to wrong report output.
• If there are isolation between transaction, transaction B (report) will have to wait till transaction A completes and then read data. But that means, manager will need to wait bit longer to get his report prepared.

2. Non-Repeatable Read: 

Transaction A reads some data. Transaction B then updates or deletes that specific data and commits its changes. If Transaction A reads the same data again, it gets a different value (because values are updated) or finds the data missing (because particular row is deleted).

Analogy: In our banking example, Transaction A reads balance of a person and do some calculation to make some decision (for example to see eligibility for bonus interest). Transaction A will make its decisions based on the values it read, if it decide this person is eligible to bonus interest then it will re-read the balance to add the interest. But before Transaction A re-reads, Transaction B deduct the balance of the same person and commit (save) values to database. New balance could be not eligible for bonus. This is called non-repeatable read, because Transaction A couldn't re-read the value it read earlier.

3. Phantom Read: 

Transaction A reads a set of rows based on some condition (where clause). Transaction B then inserts a new row that meets that same condition (where clause) and commits to the database. If Transaction A runs the same query again, it sees a new "phantom" row that wasn't there before.

Analogy: In our banking example, Transaction A reads accounts with high values (e.g. higher than 100000) for a report. Then Transaction B update an account which was not in Transaction A's list and increase that account balance to over 100000. Now this account also matches the condition. If Transaction A re-reads accounts again with the same condition (for example let us say for sub section of a report it was doing), it finds a new account which was not there before (which might leads to confusing results in report).

Isolation levels are SQL Server's way of letting you decide which of these phenomena you are willing to tolerate in exchange for better performance and concurrency. Stricter levels prevent more phenomena but can cause more blocking (transactions waiting for each other). So it is a tread-off between concurrency and data integrity.

In next part of this series, let us take a look at how (what techniques are used) isolation is implemented on SQL Server.

Introduction to SQL Server Transactions (Transaction Isolation Part 1)

What is a Transaction

First, let's take a look at what is a Transaction in SQL Server?

Imagine you need to perform several related database operations that must either all succeed or all fail together. For example, transferring money from one bank account to another. This involves two main steps:

1. Deduct the amount from the source account.

2. Add the amount to the destination account.

If step 1 succeeds but step 2 fails (maybe due to a network error or server crash), you'd end up with money disappearing from the source account but not appearing in the destination account! That's a disaster.

A SQL Server transaction groups these multiple steps into a single, logical unit of work. You tell SQL Server "Start a transaction here (BEGIN TRAN)", perform all the steps, and then say "Okay, everything worked, make it permanent (COMMIT TRAN)". If something goes wrong during the steps, you say "Cancel everything since I started (ROLLBACK TRAN)", and SQL Server undoes any changes made within that transaction.

You might already hear about ACID properties of a transaction. ACID properties are the 4 pillars of a transaction, which allows SQL Server (and other relational databases) to implement the feature correctly.

So what are these ACID properties. ACID stands for:

• A - Atomicity
• C - Consistency
• I - Isolation
• D - Durability

Atomicity

• Think of it: "All or Nothing."

• What it means: An atomic transaction is treated as a single, indivisible unit. Either all the operations within the transaction complete successfully and are permanently recorded in the database, or none of them are. If any part of the transaction fails, the entire transaction is cancelled, and the database is returned to the state it was in before the transaction started. This is called rolling back the transaction.

• Why it's important: Prevents partial updates that could leave your data in an inconsistent or incorrect state.

• SQL Server: SQL Server uses the transaction log to keep track of all changes made within a transaction. If a transaction needs to be rolled back, SQL Server uses the log to undo those changes. If an error occurs during a transaction, SQL Server will often automatically initiate a rollback, or you can explicitly issue a ROLLBACK TRAN command.

• Example (Bank Transfer):

○ You start the transfer transaction (BEGIN TRAN).

○ You successfully deduct $100 from Account A.

○ BUT before you can add $100 to Account B, the database server crashes.

Because of Atomicity, when the server restarts and recovers, it sees that the transaction wasn't completed (it wasn't COMMITted). It will use the transaction log to undo the deduction from Account A. Account A's balance will be back to what it was before the transaction. It's as if the transaction never happened.

Consistency

• Think of it: "Valid State to Valid State."

• What it means: A transaction brings the database from one valid state to another valid state. This doesn't mean the transaction logic itself is perfect (you could accidentally transfer the wrong amount!), but it ensures that the transaction adheres to all defined database rules and constraints. These rules include things like:

○ Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK constraints (e.g., Balance must be >= 0).

○ Data Types: Ensuring you don't try to put text into a number column.

○ Triggers: Database logic that automatically runs on inserts, updates, or deletes.

If a transaction would violate any of these rules, it cannot be committed, and it will be rolled back.

• Why it's important: Maintains the integrity of your data and enforces the structure and business rules defined within the database schema.

• SQL Server: SQL Server automatically checks for constraint violations and executes triggers as part of the transaction. If a violation occurs, the transaction fails.

• Example (Bank Transfer):

○ Let's say you have a CHECK constraint on the Balance column of your Accounts table that says Balance >= 0.

○ You try to transfer $200 from Account A, which only has $150.

○ You start the transaction (BEGIN TRAN).

○ You deduct 200 from Account - A. Account A′s balance temporarily becomes −50 within the transaction's scope.

○ When you try to COMMIT TRAN, SQL Server checks all constraints. It sees that Account A's balance is negative, which violates the CHECK constraint.

○ Because of Consistency (and the constraint), the COMMIT fails, and the transaction is automatically rolled back. Account A's balance returns to $150. The database remains in a valid state where no account has a negative balance.

Isolation

• Think of it: "Transactions Don't Step on Each Other's Toes."

• What it means: Multiple transactions running at the same time should not interfere with each other. From the perspective of one transaction, it should appear as if it is the only transaction running on the database. This prevents various concurrency problems (like one transaction reading data that another transaction is changing but hasn't committed yet, or two transactions trying to update the same data simultaneously in a conflicting way).

• Why it's important: Allows multiple users or applications to access and modify the database concurrently without causing errors or returning incorrect results.

• SQL Server: SQL Server provides different Isolation Levels (like READ COMMITTED, SNAPSHOT, etc.) which control how much isolation you get. Higher isolation levels provide stronger guarantees but can sometimes impact performance because SQL Server might need to use more locks or versioning to keep transactions separate. The default level in SQL Server is READ COMMITTED, which prevents reading data that another transaction has modified but not yet committed (known as "Dirty Reads").

• Example (Bank Transfer):

○ Transaction 1 starts to transfer $100 from Account A to Account B. (BEGIN TRAN, deducts from A, prepares to add to B).

○ At the exact same time, Transaction 2 starts to read the balances of Account A and Account B to generate a report.

○ Because of Isolation, Transaction 2 will typically not see the temporary state where Account A has been debited but Account B hasn't been credited yet (especially with the default READ COMMITTED isolation level). Transaction 2 will likely see the balances as they were before Transaction 1 started, or it might wait until Transaction 1 is fully committed before reading. This ensures Transaction 2 gets a consistent view of the data, even though it's running concurrently with a transaction that's modifying that data.

Durability

• Think of it: "Changes are Permanent, Even After a Crash."

• What it means: Once a transaction has been successfully committed, its changes are permanent and will survive even if the database server crashes, loses power, or restarts immediately after the commit. The committed data is stored in a way that guarantees it won't be lost.

• Why it's important: Ensures that once a user or application gets confirmation that a transaction is complete (e.g., "Your transfer is successful"), they can trust that the changes have truly been saved and won't disappear.

• SQL Server: When you COMMIT TRAN, SQL Server ensures that the record of the transaction's changes is written to the transaction log on disk. Writing to the log is typically faster than writing the actual data pages to disk. Even if the server crashes after the log record is safely written but before the changes are written to the main data files, SQL Server can use the transaction log during startup recovery to redo the committed transaction and ensure the data files reflect the committed state.

• Example (Bank Transfer):

○ You successfully complete the transfer: $100 is deducted from Account A, and $100 is added to Account B.

○ You issue COMMIT TRAN. SQL Server confirms the commit back to your application.

○ Immediately after receiving the confirmation, the power goes out, and the server shuts down.

○ Because of Durability, when the server is restarted, SQL Server performs a recovery process. It reads the transaction log, sees that your transfer transaction was committed, and ensures that the changes (deducting from A and adding to B) are fully applied to the actual data files on disk. When you next check the balances, they will correctly reflect the transfer.

Next

ACID properties are the backbone of reliable database systems like SQL Server

Out of all 4 of these properties, Isolation is the property we can configure mostly on SQL Server.

Atomicity and Durability is automatically implemented in the SQL server core code to make sure all transactions are adhere to them (otherwise no proper transaction). Although we define rules (business rules via constraints and triggers) for Consistency, enforcing them (once defined and enabled) is automatic (no intervention required from us).

However, Isolation property is much more configurable. This is because there is a trade-off between high Isolation and performances. Therefore, SQL server allows user to choose several pre-defined Isolation levels based on performance requirements.

So let's explore more on Isolation levels in our next module.