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 can help enforce data integrity, sometimes you need something more.
Transactions are here to help.
Per SQL Server's Documentation
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:
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.
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.
Top comments (0)