This article explains the concept of ACID properties and how they are implemented in SQL Server database. Brief explanations on the concept and an example of basic transaction are also covered here.
ACID is a common acronym in SQL worlds which stands for “atomicity, consistency, isolation, durability.” These four words form a set of properties that a database transaction should implement to guarantee the reliability of data storage, processing, and manipulation. Let’s discuss this ACID property in SQL Server in more detail.
First, a transaction should be atomic. Atomicity ensures that either the entire transaction is performed or none of it is. If for some reason any of the individual operations can’t be completed, no changes are made to the database. Anything that’s partially done will be undone.
When we have multiple operations in a transaction, we need to write commands which specify the start and the end of the transaction explicitly. At the end of the transaction, we can commit all the changes or rollback the transaction if there are any errors. In SQL Server, the explicit transaction commands are listed in the following table.
Table 1. Explicit transaction commands in SQL Server.
We can write either TRANSACTION or TRAN, for example, BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN. Both TRANSACTION and TRAN are the same.
Let’s take a look at a basic transaction example in SQL Server. Suppose we have a stored procedure named sp_updateTwoTables that modifies two tables, Table1 and Table2. We will modify Table1 and Table2. If there are no errors, the changes will be committed, otherwise will be rolled back.
CREATE PROCEDURE sp_updateTwoTables AS BEGIN TRY BEGIN TRANSACTION UPDATE Table1 SET ColumnName = 'newvalue1'; UPDATE Table2 SET ColumnName = 'newvalue2' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR ('An error has occurred', 16, 1) END CATCH
If there are any errors, we won’t get into the case where the Table1 is updated but Table2 is not.
The second property is consistency. It means that the data in the database must be consistent with some rules set up in the database. For example, we cannot insert the name ‘John Smith’ to a column with an integer data type.
SQL Server and other DBMS such as MySQL and Oracle use data types and constraints to enforce consistency in the database.
Consistency also applies when dealing with data updates. If there are concurrent users read or update the same rows simultaneously, an issue such as dirty reads or lost updates can occur. The concept of isolation, which is also listed in ACID, is designed to deal with this situation.
The isolation property ensures that any modification in one transaction must be isolated from any modifications in any other transaction. Also, any transaction should not read data which is in an intermediate state or uncommitted.
SQL Server provides several isolation levels for different stages of isolation in a transaction. These isolation levels are based on locking mechanisms. See How to Set Isolation Level in SQL Server.
The concept of durability ensures that once the transaction is completed, all the changes that have been made to the database will be permanent. Even if there is a system failure, committed data cannot be lost.
SQL Server provides transaction log, backup, and restore features that help to ensure data durability.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.