Transactions (Part 1)

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.