Monday, July 16, 2012

Transactions - Bits & Pieces

SET XACT_ABORT

SQL Server uses transactions to group a set of SQL Statements. However if a Rolling back mechanism is not set up, eventhough some statments failed to execute, others would be commited. 

If SET XACT_ABORT is set to ON, all the statements are Rolledback. By default this is SET to OFF.

MARK
This is used to mark a transaction in the transaction log. This is usfull to restored a database to that mark. This similar to recover a database to a point in time.
When several databases are used in a single transaction, MARK option can be used to make all the databases to a consistant state.

SAVE POINT

Save points are used to usfull when transactions are rolled back. Transaction can be rolled back to defined save point

When there is no explisit transaction is defined, the statement
SAVE TRANSACTION will fail.
However it will work, if the transaction mode is set to IMPLICIT TRANSACTION

SET ANSI_DEFAULTS ON
When this is ON, IMPLICIT TRANSACTION mode is automatically ON