Transaction in SQL Server

 

Definition:

The Transaction must contain a series of properties known as ACID.

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

 

Atomicity (each transaction is “all or nothing”):

If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency

Data is in a consistent state when a transaction starts and when it ends.

For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation

Every transaction has a well-defined boundary. One transaction will never affect another transaction running at the same time.

Durability

Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

Example:

BEGIN TRANSACTION

 –Transaction statements

 IF @@ERROR!=0

BEGIN

ROLLBACK TRANSACTION

END

ELSE

BEGIN

COMMIT TRANSACTION

END

Nested Transaction:

Nested transactions usually occur when one stored procedure begins a transaction and then calls other procedures also using transactions before committing the initial transaction. However, a single stored procedure could also have multiple transactions open at the same time.

The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction.

  •  If the outer transaction is committed, the inner nested transactions are also committed. 
  • If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

 @@TRANCOUNT – Records the Current transaction nesting level

BEGIN TRAN –  @@TRANCOUNT 

COMMIT TRAN – @@TRANCOUNT – 1

 ROLLBACK TRAN @@TRANCOUNT

BEGIN TRAN

    INSERT INTO People VALUES(‘Tom’)

BEGIN TRAN

   INSERT INTO People VALUES(‘Dick’)

ROLLBACK TRAN

COMMIT TRAN

 If you execute the above sample you will find that it generates the error:

 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This error suggests that the final line of the script, which commits the outer transaction failed because the outer transaction no longer exists. Indeed. When you try to rollback a nested transaction, all active transactions are cancelled.

Transaction Savepoints:

Savepoints offer a mechanism to roll back portions of transactions.

SAVE TRANSACTION Savepoint_name

 ROLLBACK TRANSACTION savepoint_name – To roll back to the savepoint instead of rolling back to the start of the transaction

 Example:

BEGIN TRAN

PRINT‘First Transaction: ‘+CONVERT(VARCHAR,@@TRANCOUNT)

NSERTINTO People VALUES(‘Tom’)

SAVE TRAN Savepoint1  

PRINT‘Second Transaction: ‘+CONVERT(VARCHAR,@@TRANCOUNT)

INSERTINTO People VALUES(‘Dick’)

ROLLBACK TRAN Savepoint1

 PRINT‘After Rollback: ‘+CONVERT(VARCHAR,@@TRANCOUNT)

COMMIT TRAN

PRINT‘After Commit: ‘+CONVERT(VARCHAR,@@TRANCOUNT)

/* MESSAGES

First Transaction: 1

Second Transaction: 1

After Rollback: 1

After Commit: 0 */

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: