SQL Server Transactions and Isolation Levels
Tags: sql, sqlserver, database
Published: 2019-09-07 19:44:01

As developers, we don't always think too deeply about the database internals and that can result in poor performance and unexpected concurrency issues.

This article represents my learning progress in diving into SQL Server's concurrency features.

In this article we'll discuss:

- Transactions
- Read Modes

# Transactions

By default, when you write SQL it runs in an *autocommit transaction*, which means each statement will be committed to the database after it is executed.

This is fine for many cases, but what if you have a complex operation where you want to modify multiple tables in one batch and these changes must all go together or not at all in order to prevent data integrity issues. While [constraints](https://dev.to/integerman/name-your-constraints-4n10) can help enforce data integrity, sometimes you need something more.

Transactions are here to help.

Per [SQL Server's Documentation](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-2017)
> A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

A transaction looks like the following:

```sql
BEGIN TRANSACTION

   INSERT INTO 
       dbo.Resumes (FirstName, LastName, Url) 
   VALUES 
       ('Matt', 'Eland', 'SomeActualUrl');
   GO

   UPDATE
       dbo.ResumeCounts
   SET
       ResumeCount = ResumeCount + 1
   WHERE
       ResumeType in ('Awesome', 'Code Monkey', 'Manager', 'Mentor')
   GO

COMMIT TRANSACTION
```

Here we want to insert into the `Resumes` table and also update the `ResumeCounts` table in one transaction. If the second statement errored, we'd want to rollback the transaction and remove Matt Eland from the Resumes table. This transaction takes care of that for us.

Additionally, you can use a `ROLLBACK TRANSACTION` statement if you just want to see if a statement executes and get a sense of how many rows are updated. I often recommend running statements in a `ROLLBACK TRANSACTION` to check for errors and as a sanity check before changing the transaction to a `COMMIT TRANSACTION`.

# Read Modes

Transactions have something called a *read mode* that governs what types of locking strategies they use.

![Locking Strategies](https://thepracticaldev.s3.amazonaws.com/i/88wrjng7hwe1o8omlf6k.png)

## Read Committed

By default, SQL Server will run in this mode and only read data that is committed (not *dirty* - currently being modified by another query). This prevents what is called *dirty reads*.

To activate this mode, execute the following statement:

`SET TRANSACTION ISOLATION LEVEL READ COMMITTED`

## Read Uncommitted

Read Uncommitted removes the restriction that data must be in a fully committed state. This means that read uncommitted bypasses some locks in order to read rows which may currently have intermediate values. 

To activate this mode, execute the following statement:

`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED`

## Repeatable Read

`Repeatable Read` adds an additional layer of safety to `Read Uncommitted`. In addition to reading things that have not yet been committed, `Repeatable Read` acquires a shared lock on the rows in question to prevent them from being further updated until the transaction ends. This means that any subsequent reads of those rows during the transaction will produce the same result. This is not necessarily true with `Read Uncommitted`.

Generally the extra overhead of the shared locks makes `Repeatable Read` less desirable, but there may be cases where it is good to use them.

To activate this mode, execute the following statement:

`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ`

## Serializable

Serializable is an extreme measure to demand total control of the range of rows being modified. It will not read uncommitted and it will not allow other transactions to read data that it is modifying. As such, it requires exclusive locks, causing other transactions to wait for it to complete. This also prevents other rows from being added to the set of records, preventing what is known as a *phantom read*.

This has high data integrity and safety implications at the expense of performance.

To activate this mode, execute the following statement:

`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE`

## Snapshot

Snapshot is a SQL Server specific option that effectively takes a snapshot of the rows being evaluated at the start of the transaction.

This bypasses other locking operations on the database, but any updates that occur during the transaction will not be available to the transaction. Additionally, the database must have snapshots enabled via the `ALLOW_SNAPSHOT_ISOLATION` option.

To activate this mode, execute the following statement:

`SET TRANSACTION ISOLATION LEVEL SNAPSHOT`

---

Transactions are a good tool for managing data integrity during update and insert scripts which much touch multiple tables.

Next in this series we'll discuss [locking](https://dev.to/integerman/sql-server-locking-4beo).

{% link https://dev.to/integerman/sql-server-locking-4beo %}