Transaction is a single unit of work. Usually a transaction contains, more than one T-SQL statements; the basic concept of transaction is “Either all the statements are successful or none”. This is very very important concept as far as database is considered.
Consider a situation where you are going to the bank, and taking money from the ATM. Important two actions you can see here are, deducting the amount from your savings account and taking the money. These operations either run together or won’t run at all. It is not good, if your account is deducted the amount and you were not received the money; neither the reverse.
The SQL server operates in four transaction modes.
- Auto commit
- Implicit Transactions
- Explicit transactions
- Distributed Transactions
In auto commit mode, every individual transaction is considered as a Transaction. This is exactly what happens when we issue a single insert / update/ delete statements from the Query analyzer. Here is how sql performs this
Transaction begins à Statement Executed à Commits the Transaction
When a connection is operated in an ‘implicit transaction’ mode, SQL Server will start automatically when a prior transaction is committed or rolled back; and thus it generates a continuous chain of transactions.
In order to enable ‘Implicit transaction’, you need to set the value for that option. You can do it either using Enterprise manager / SQL Server management studio (in SQL Server 2005) or use a t-SQL Script
SQL server 2005
In Explicit mode, we need to explicitly mention the “BEGIN TRANSACTION’ in order to start the transaction and “COMMIT TRANSACTION” in order to commit it or “ROLLBACK TRANSACTION” in order to rollback the changes done by the transaction.
Distributed transactions usually span over two or more different servers. The transactions involving two or more databases can also be defined as Distributed transaction. The Statements to be executed as part of the distributed transaction must be enclosed within a
‘BEGIN DISTRIBUTED TRANSACTION’ and a ‘COMMIT TRANSACTION’. If this involves more than one server, the server which initiates the transaction is going to control the entire transaction.
So the next question is where should we use ‘BEGIN/COMMIT’ Transactions? As a rule of thump, if the procedure involves more than one insert &/ delete &/update statements, those statements must be included inside a transaction.
Eg:
BEGIN TRAN
INSERT INTO SomeTble
SELECT <SomeValues>
UPDATE someOtherTable
SET someColumn = 'aNewValue'
COMMIT TRAN
While dealing with explicit / distributed Transactions, make sure to keep the transactions, as small as possible as the transactions are the main cause of locks. Do all the parameter validations outside the transactions. In order to prevent the deadlock, do the data modifications in the same order within different transactions in the same database. For example, if there are several procedures, which will do some update operations on tables A and B, you need to make sure that, each transaction should update the tables A and B in the same order.
