A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.
Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

Controlling Transactions

Applications control transactions mainly by specifying when a transaction starts and ends. This can be specified using either Transact-SQL statements or database API functions. The system must also be able to correctly handle errors that terminate a transaction before it completes.
Transactions are managed at the connection level. When a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction until the transaction ends.
Starting Transactions
You can start transactions in Microsoft® SQL Server™ as explicit, autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. These transactions will contain only one statement. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. When that transaction is completed either by using COMMIT / ROLLBACK, the next Transact-SQL statement starts a new transaction.
Ending Transactions
You can end transactions with either a COMMIT or ROLLBACK statement.
If a transaction is successful, commit it. A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. A COMMIT also frees resources, such as locks, used by the transaction.

COMMIT TRAN[SACTION]  transactionName

If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction.

ROLLBACK TRAN[SACTION] transactionName [savepointname]


Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement, and then later execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of a transaction.
Example: Let us suppose emp table contains the following data.

2. SELECT * FROM emp
3. DELETE FROM emp WHERE empid=102
4. SELECT * FROM emp
5. SAVE TRAN sp1
6. UPDATE emp SET sal=4000 WHERE empid=101
7. SELECT * FROM emp
9. SELECT * FROM emp
11.SELECT * FROM emp

Output:  The result for the above program is as given below. each block is the result of select statement.
100     Anil               1500  
101     Balu              2000  
102     Vivek            3500  
100     Anil               1500
101     Balu              2000
100     Anil               1500
101     Balu              4000
100     Anil               1500
101     Balu              2000
100     Anil               1500
101     Balu              2000
102     Vivek            3500
Errors During Transaction Processing
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.


Post a Comment