Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What is a transaction?



By:

A transaction can be defined in many different ways and Ive always had this question come up in interviews. Basically, a transaction is a unit of work that is performed against a database. This work can be performed manually, such as an UPDATE statement you issue in SQL Server Management Studio or an application that INSERTS data into the database. These are all transactions.

SQL Server supports the following transaction modes:

Autocommit transactions - Each individual statement is a transaction.

Explicit transactions - Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Batch-scoped transactions - Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.

You may have heard of the ACID properties. These apply to transactions as well:

Atomicity - ensures that all operations within the work unit are completed successfully, otherwise the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

Consistency - ensures that the database properly changes states upon a successfully committed transaction.

Isolation enables transactions to operate independently of and transparent to each other.

Durability - ensures that the result or effect of a committed transaction persists in case of a system failure.

Last, but not least, SQL Server supports transaction control. Below is a short description of each, but well go over them in more detail in the next section. Note that transaction controls are only used with DML commands.

BEGIN TRANSACTION - the starting point of a transaction

ROLLBACK TRANSACTION - roll back a transaction either because of a mistake or a failure

COMMIT TRANSACTION - save changes to the database


Last Update: 3/25/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools