The Transaction must contain a series of properties known as ACID.
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.
Atomicity (each transaction is “all or nothing”):
If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.
For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.
Data is in a consistent state when a transaction starts and when it ends.
For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.
Every transaction has a well-defined boundary. One transaction will never affect another transaction running at the same time.
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.
Nested transactions usually occur when one stored procedure begins a transaction and then calls other procedures also using transactions before committing the initial transaction. However, a single stored procedure could also have multiple transactions open at the same time.
The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction.
If the outer transaction is committed, the inner nested transactions are also committed.
- If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
@@TRANCOUNT – Records the Current transaction nesting level
BEGIN TRAN – @@TRANCOUNT
COMMIT TRAN – @@TRANCOUNT – 1
ROLLBACK TRAN – @@TRANCOUNT
INSERT INTO People VALUES(‘Tom’)
INSERT INTO People VALUES(‘Dick’)
If you execute the above sample you will find that it generates the error:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
This error suggests that the final line of the script, which commits the outer transaction failed because the outer transaction no longer exists. Indeed. When you try to rollback a nested transaction, all active transactions are cancelled.
Savepoints offer a mechanism to roll back portions of transactions.
SAVE TRANSACTION Savepoint_name
ROLLBACK TRANSACTION savepoint_name – To roll back to the savepoint instead of rolling back to the start of the transaction
PRINT‘First Transaction: ‘+CONVERT(VARCHAR,@@TRANCOUNT)
NSERTINTO People VALUES(‘Tom’)
SAVE TRAN Savepoint1
PRINT‘Second Transaction: ‘+CONVERT(VARCHAR,@@TRANCOUNT)
INSERTINTO People VALUES(‘Dick’)
ROLLBACK TRAN Savepoint1
PRINT‘After Rollback: ‘+CONVERT(VARCHAR,@@TRANCOUNT)
PRINT‘After Commit: ‘+CONVERT(VARCHAR,@@TRANCOUNT)
First Transaction: 1
Second Transaction: 1
After Rollback: 1
After Commit: 0 */