The main idea of transactions is that when each of the statements returns an error, the entire modifications rollback to provide data integrity.
Property of Transaction:
- Atomicity: The entire of the operations that are included by the transaction performed successfully. Otherwise, all operations are canceled at the point of the failure and all the previous operations are rolled back
- Consistency: This property ensures that all the data will be consistent after a transaction is completed according to the defined rules, constraints, cascades, and triggers
- Isolation: All transactions are isolated from other transactions
- Durable: The modification of the commited transactions becomes persist in the database
Mode of Transaction:
- Auto commit Transaction mode is the default transaction for the SQL Server. In this mode, each T-SQL statement is evaluated as a transaction and they are committed or rolled back according to their results. The successful statements are committed and the failed statements are rolled back immediately
- Implicit transaction mode enables to SQL Server to start an implicit transaction for every DML statement but we need to use the commit or rolled back commands explicitly at the end of the statements
- Explicit transaction mode provides to define a transaction exactly with the starting and ending points of the transaction
Implicit Transaction Statement:
SET IMPLICIT_TRANSACTIONS ON
UPDATE
employee
SET
salary=25000
WHERE
ID = 2;
select * from employee
rollback tran
select * from employee
Explicit Transaction Statement:
begin transaction
UPDATE
employee
SET
salary=45000
WHERE
ID = 2;
-- below select statement is executing after update but before commit so it will not show updated value
select * from employee
rollback TRAN
end
-- below select statement is executing after update and commit so it will show updated value
select * from employee
Difference Between Explicit and Implicit Transaction:
- In **implicit transaction** mode, a transaction is automatically started after each commit. So you only have to commit or rollback. You will not see an explicit `BEGIN TRANSACTION` in the logs. This mode is set by using `SET IMPLICIT_TRANSACTIONS ON`.
- In **explicit transaction** mode, you will need to start a transaction explicitly by using `BEGIN TRANSACTION`. You will also need to commit or rollback explicitly. This mode is set by using `SET IMPLICIT_TRANSACTIONS OFF`.
Some advantages of implicit transaction mode are:
- It reduces the risk of forgetting to commit or rollback a transaction.
- It simplifies the code by avoiding explicit transaction statements.
Some disadvantages of implicit transaction mode are:
- It may cause performance issues by holding locks longer than necessary.
- It may cause unexpected results if multiple statements are executed in a single batch without a commit or rollback.
Some advantages of explicit transaction mode are:
- It gives you more control over the transaction boundaries and isolation levels.
- It allows you to handle errors and rollbacks more easily.
Some disadvantages of explicit transaction mode are:
- It requires more code and attention to ensure proper transaction management.
- It may cause data inconsistency if a commit or rollback is missed or misplaced.