SQL- Transaction

The SQL language allows us to create SQL statements, to define database objects, to define user access to those objects, and to manipulate the data stored. In addition, SQL provides HELP and SHOW statements that provide help about database object definitions, sessions and statistics, SQL statement syntax, as well as displaying the SQL used to create tables.

What is a Transaction?

A transaction is a single operation or set of operations that succeed or fail together as a whole, thereby ensuring consistency of data should unforeseen circumstances arise. A classic scenario is the typical financial transaction. For example, let’s say you buy a car. The single transaction of buying a car consists of three distinct operations:

1. You select a car.

2. You pay for it.

3. You drive the car off the lot.

Skipping any of these steps could cause major angst to one or more of the parties involved. This is a simple example of a set of steps that must always occur together in a consistent manner. Transactions allow you to ensure consistency in your data through four basic principles. These principles provide a set of rules that must be followed for a transaction to succeed. The four principles help ensure that the state of your data is atomic, consistent, isolated, and durable, regardless of the success or failure of the transaction.

Understanding the ACID Properties - Quickly summarized the four ACID properties:

1. Atomicity Operations succeed or fail together. Unless all steps succeed, the transaction cannot be considered complete.
2. Consistency Operations leave the database in a consistent state. The transaction takes the underlying database from one stable state to another, with no rules violated before beginning or after the end of the transaction.
3. Isolation Every transaction is an independent entity. One transaction will not affect any other transaction that is running at the same time.
4. Durability Every transaction is persisted to a reliable medium that cannot be undone by system failures. Furthermore, if a system failure does occur in the middle of a transaction, either the completed steps must be undone or the uncompleted steps must be executed to finish the transaction. This typically happens by use of a log that can be played back to return the system to a consistent state.

A transaction can work with a single resource, such as a database, or multiple resources, such as multiple databases or message queues. Transactions limited to a single resource are referred to as local transactions, and transactions that span multiple resources are called distributed transactions.

Local Transaction Support in SQL Server

SQL Server, like any industrial-strength database engine, provides built-in support that enables you to wrap one or more queries inside a transaction. Local transactions (those that deal with only one physical database) operate in one of four transaction modes:

1. Auto-commit
2. Explicit
3. Implicit
4. Batch-scoped

Note: The auto-commit transaction mode is the default transaction mode.

SQL - Transaction Isolation Levels
The concept of isolation levels was first introduced in under the name Degrees of Consistency. We know that commercial databases support different isolation levels to allow programmers to trade off consistency for a potential gain in performance.