Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

ACID Properties in SQL Server – Querychat

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.

Atomicity

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.

Note:

We can write either TRANSACTION or TRAN, for example, BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN. Both TRANSACTION and TRAN are the same.

Example:

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.

Consistency

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.

Isolation

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.

Durability

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc