Basics Transactions in SQL Server 2008

A transaction in a database is a logical unit of work. A transaction can be one or more SQL statements run by a single user.

For example, If I want pay my $200 credit card bill from my bank account, it is transaction. Basically what has to happen is, the $200 has to be deducted from my bank account and it has to added to my credit card account. There are 2 SQL statements here, one to update the bank account with current balance by reducing $200 and another to add the credit card with $200 to pay it off. Both the statements has to be treated as one logical unit or transaction, because if any error happens even at one statement or if the database crashes during the statements, both of them should not happen. All actions should either fail or succeed together.

The process on executing both the statements into database and registering it is called commiting the transaction. If either one fails, not registering or moving the other one to previous state is called Rolling Back.

Syntax:

To explicitly start the transaction: Begin transaction transactionname

To explicitly commit the transaction: Commit transaction transactionname

To explicitly rollback the transaction: Rollback transaction transactionname

According to MSDN online:

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.