Good afternoon everyone! This blog post will be the conclusion of my 3-parter series on Transactions, and will concentrate on Transaction Isolation Levels; what they are and how they can be set to improve your transactions.

What are Transaction Isolation Levels? 

Isolation levels are constraints that are applied to a transaction to determine how much access is allowed to resources for data modifications made by other transactions. Usually, transactions will be nested within one another and control will need to be passed between the 2 in order to commit the final data to the database. Isolation levels control: 

• The type of lock required on the transaction. 
• How long the locks are in use for
• When the locks will be freed to allow access from another transaction. 
• Whether other transactions are attempting to access the same data and applies the correct constraints accordingly. 

It's incredibly important to decide which isolation level to use as if the level is too high, blocking or locking can occur (and can be incredibly frustrating when you have multiple users accessing the same information at the same time!). However if the level is too low, potentially many of the users will notice missing updates, deadlock scenarios and what is referred to as "phantom" reads (see section below on Read Phenomena).

Read Phenomena

When a transaction reads data from another transaction that might have been changed, three different scenarios could potentially take place, which are often referred to as the Read Phenomena:

Dirty Reads

A Dirty Read allows a transaction to read data that hasn't been committed to the database yet. This means that the data may be different if the other transaction is rolled back and therefore causing an anomaly. 

Non-Repeatable Reads

A Non-Repeatable Read involves a row being retrieved from the database twice but it may contain different data on each occasion, even though it is retrieving from the same rows within the database. 

Phantom Reads

A Phantom Read occurs when 2 identical queries are run together and the rows returned are different in both result sets, even though the data may be the same. This occurs when the underlying data has been modified at some point in-between the 2 executes (and no read lock has been placed on the data).

Types of Isolation Level

There are various types of isolation level that can be put in place in order to control the amount of Read Phenomena that could occur within the database, which will be discussed in this section: 

Read Uncommitted

Reads data that has been updated by a transaction before it has been committed to the database.

Read Committed

This is the default setting in SQL Server, which allows statements to possibly experience Phantom Reads but not Dirty Reads. 

Repeatable Read

Transactions are only allowed to read data that has already been committed to the database. 

Snapshot

The data is read at the time it has been entered into a transaction (so before any action is performed on it) but no locks are placed on the data. This means that if updates occur, they will not be visible to the transaction that just took the snapshot. 

Serializable

Doesn't allow data to be read before it has been committed to the database and prevents other transactions from accessing data that is being read by the current transaction (until the current transaction has been completed). 

The table below shows the allowances of Read Phenomena depending on the Isolation Level selected: 

How To Declare An Isolation Level? 

Declaring an isolation level is pretty straight forward, and should be done before the transaction is run in order for it to take effect:  

Final Word...

A massive thank you to everyone who has read and continues to follow my blog and my progress as a Junior Developer out in the big wide world! I really appreciate all of your support. If anyone has any questions please don't hesitate to ask me in the comment section below. Plus I like to hear feedback on what I'm doing! Have a good weekend everyone! 

Good morning everyone! As promised, today we will be discussing Transaction Locking, which is one of the key elements to discovering exactly how transactions work with each other, particularly in an environment where multiple users will be using the same database in order to add and modify their data.  Although I haven't had the opportunity to look at this in the working environment, I am aware that it has been used on our database projects and it's a very important concept to have a handle on. When considering clients requirements for a database application, you need to consider the following 2 approaches; Pessimistic Control and Optimistic Control.

Pessimistic Control 

Pessimistic control assumes that the users will attempt to access and update the same data at the same time. In this situation, locks should be included to prevent this from happening.

Optimistic Control 

Optimistic control assumes that users will not be attempting to update the same data that regularly, therefore control can be relaxed. In this situation, less locks can be introduced and therefore speeding up the database.

In reality, you will need to look at the 2 different approaches and combine them to produce a number of locks and isolation levels (something which I will come onto in a later blog post). There are various different lock modes within SQL and each are used in their own situations. Some can be combined with other locks whilst others can only be used on a resource on their own. The list below (thanks to Microsoft SQL Server 2008 - Database Development (70-433) ISBN: 978-0-7356-2639-3) provides details on what the available locking modes are and under what conditions they are used:

Lock modes: 

Shared (S) 

• Used for read-only operations such as SELECT statements. 
• The Shared lock is compatible with other Shared locks.

Update (U)

• Used for both read and write operations, however only one transaction will be granted access at any one time. 
• The Update lock is usually upgraded to an Exclusive lock.

Exclusive (X)

• Used on all data modification operations (INSERT, UPDATE and DELETE).
• Ensures that multiple updates can't be made to the same data at the same time.
• Exclusive locks are not compatible with any other lock type. 

Intent (IS, IX, SIX)

• Improves performance by placing locks on tables and views before placing locks on page level controls. 

Schema (Sch-M, Sch-S)

• Sch-M stands for Schema Modification lock which is used when changes to the database schema occur, such as adding new tables or columns to an existing table. 
• A Sch-M lock will prevent any other executions until the current operation is complete.
• Sch-S stands for Schema Stability lock which is used when queries are being executed.
• A Sch-S lock can't be used with Sch-M locks.

Bulk Update (BU)

• Allows for a block insert into a table.
• Allows multi-threading however other processes won't be able to access the table until the bulk update is complete. 

Key-Range

• Protects against Phantom reads (where datasets brought back from 2 identical SELECT statements are different due to 2 DML transactions being executed at the same time).

Deadlock and Blocking 

Deadlocking occurs when two transactions updating the same data in the same data column on a database at the same time and, as a result, execution hangs as neither can be completed successfully. SQL Server determines which transaction should be rolled back by looking at the estimated cost for roll back (the lower cost will be rolled back). A 1205 error message will then be given, however ensure that these are captured effectively as they don't provide any information to users.

How to Reduce Deadlocks and Blocking 

In order to reduce the chances of deadlocking and transaction blocking from occurring:

• Keep transactions simple. 
• Verify all data input by users before running transactions.
• Access the least amount of data possible within your transactions. 
• Adjust query wait times.
• Assess possible deadlock areas by using SQL Server Profiler. 

Lock Status

You can determine lock status of a transaction by using SQL Server Profiler, which can also produce reports to show which locks have been placed on which database resources. Locks can be placed on the following resources: 

• KEY - Keys within an index. 
• PAGE - An 8KB page of data made up from tables. 
• EXTENT - A group of 8 pages.
• HoBT - A Heap or Balanced Tree Index.
• TABLE - A complete table and its contents, including Indexes. 
• FILE - A complete database file.
• APPLICATION - A resource related to the application that is running it. 
• METADATA - Specified metadata.
• ALLOCATION_UNIT - A single unit that has been allocated. 
• DATABASE - A complete database, including absolutely everything.

Next...

Thank you for reading this blog post! The next instalment will discuss Setting Transaction Isolation Levels. Watch this space everyone! 

Hi everyone! Today's blog post will be focusing on Transactions; explaining what they are in written terms, discussing ROLLBACK options and also isolation levels. Due to how large this topic is, I think I will break it down into smaller pieces and today I will focus mainly on what the definition of a Transaction is and relating it to my previous T-SQL blog posts. Whereas that focuses more on the syntax and how it is used, these blogs will focus more on how to manage them properly to ensure the best performance out of them.

What Are Transactions? 

Transactions are a set of actions that either succeed and commit to the database, or fail and ROLLBACK (depending on the error produced - see Stored Procedures (Part 2) for more details on error messages). In order to guarantee that database transactions are executed efficiently, a set of properties commonly referred to as the acronym ACID (Atomicity, Consistency, Isolation, Durability).

Atomicity

Atomicity means that either all pieces of the transaction are committed or none of it is committed (for example if an error is found). Atomicity is very important because without it, constraints would be violated.

Consistency

Consistency means that at the end of the transaction, either new, valid data has been produced that can be committed to the database or the transaction can perform a ROLLBACK to retrieve the data in its previous state.

Isolation

Isolation means that the data where the transaction takes place must be protected so that no other transactions can access it or modify it at the same time. The isolation level can be modified for each transaction, which is something I am going to come onto in a later blog post.

Durability

Durability means that once the transaction has been committed to the database, it will stay committed and the data won't revert back, even if the server is turned off, crashes, fails etc. If the durability of a transaction fails, if transactions are waiting to be committed to the database and there is a power cut, there is a chance that the user will (wrongly) assume that their changes have been committed when actually they hadn't and the data remains unchanged.

Defining a Transaction 

Transactions are usually declared within either a User-Defined Function, a Trigger or a Stored Procedure and are written in a basic format similar to the following:

You can also nest transactions within each other like in the example below. However, note that unless both parts of the Transaction are successful, due to its Atomicity, it will either commit the entire transaction (providing it passes all validation and constraints) or it will throw out an error and ROLLBACK to the previous dataset: 

It is also common to include transactions in a TRY ... CATCH statement in order to personalise your error messages, something which is covered by my Stored Procedures (Part 2) blog post. 

Gathering Information on Open Transactions

As mentioned in Stored Procedures (Part 1), @@TRANCOUNT is a global function that can be used to count the number of open transactions within a current session (from when a user has connected to SQL Server). However, there are other global objects which can provide even more information about the transactions running on your database:

• sys.dm_tran_active_snapshot_database_transactions
• sys.dm_tran_current_snapshot
• sys.dm_tran_database_transactions
• sys.dm_tran_session_transactions
• sys.dm_tran_transactions_snapshot
• sys.dm_tran_active_transactions
• sys.dm_tran_current_transaction
• sys.dm_tran_top_version_generators
• sys.dm_tran_version_store
• sys.dm_tran_locks

Keep Your Eyes Peeled...

Next blog post will discuss Transaction Locking and how Transactions interact with one another. Thanks for reading this blog post! If you have any questions or feedback, please feel free to leave a comment below and I will try to get back to you as soon as I can.