TRANSACTION definition and ACID Properties in SQL !!

05 Jan

What is the TRANSACTION in the SQL database?

In brief, a transaction is a logical unit of work in which, all the steps must be performed or none (ATM example).

Another definition:
A transaction is a unit of work in a database that typically contains several commands that read from and write to the database. The most well-known feature of a transaction is that it must complete all of the commands in their entirety or none of them. This feature, called atomicity, is just one of four properties defi ned in the early days of database theory as requirements for a database transaction, collectively known as ACID properties.

In databases, it sometimes happens that multiple tables have to be updated at the same time. For example a payroll payment system may need to update these tables when an employee is paid:

  • Employee record Table – holds a record for each employee.
  • Accounts Table – holds payment details by department.
  • Auditing Table – holds all payment details for auditing purposes.

The idea is when the employee is paid that his record is updated with the paid amounts, a record is added to the accounts table and one to the auditing table. That way the company can account for the salary payment. It all adds up and is consistent. The problem arises if something goes wrong while writing to these three tables. Maybe a hardware failure or a program bug, say a duplicate data row added or a crash due to hardware. If you work with databases, you will experience a failure at some time or other. You are most likely to get problems during development if a SQL query goes wrong. Say it takes too long to run and you have to kill the process.

Why not use TRANSACTION for everything?

Because transactions are expensive in processing time to do. The server has to set aside resources to deal with both success and failure and then deal with one or the other.

What is ACID properties?

ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

ACID is an acronym and mnemonic device for learning and remembering the four primary attributes ensured to any transaction by a transaction manager (which is also called a transaction monitor).

Atomicity means that all the effects of the transaction must complete successfully or the changes are rolled back. A classic example of an atomic transaction is a withdrawal from an ATM machine; the machine must both dispense the cash and debit your bank account. Either of those actions completing independently would cause a problem for either you or the bank.

The consistency requirement ensures that the transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, your system might require that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the data in a sex fi eld must be male or female. In order to be consistent, a transaction must not break any of the constraints or rules defi ned for the data.

Isolation refers to keeping the changes of incomplete transactions running at the same time separate from one another. Each transaction must be entirely self-contained, and changes it makes must not be readable by any other transaction, although SQL Server does allow you to control the degree of isolation in order to fi nd a balance between business and performance requirements.

Once a transaction is committed, it must persist even if there is a system failure — that is, it must be durable. In SQL Server, the information needed to replay changes made in a transaction is written to the transaction log before the transaction is considered to be committed.

SQL Server Transactions Types:

There are two types of transactions in SQL Server that are differentiated only by the way they are created: implicit and explicit.

Implicit transactions are used automatically by SQL Server to guarantee the ACID properties of single commands. For example, if you wrote an update statement that modifi ed 10 rows, SQL Server would run it as an implicit transaction so that the ACID properties would apply, and all 10 rows would be updated or none of them would.

Explicit transactions are started by using the BEGIN TRANSACTION T-SQL command and are stopped by using the COMMIT TRANSACTION or ROLLBACKTRANSACTION commands.

Committing a transaction effectively means making the changes within the transaction permanent, whereas rolling back a transaction means undoing all the changes that were made within the transaction. Explicit transactions are used to group together changes to which you want to apply the ACID properties as a whole, which also enables you to roll back the changes at any point if your business logic determines that you should cancel the change.

What are the Isolation levels?

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

1 Comment

Posted by on January 5, 2012 in MS SQL Server


Tags: , , , , , , , , , , , , , , , ,

One response to “TRANSACTION definition and ACID Properties in SQL !!

  1. Asmaa

    November 8, 2014 at 9:53 AM


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: