Introduction
In this blog, I will explain the SQL Transaction. A transaction is a logical unit of work. Each transaction begins with a specific task and ends with all tasks in a group successfully complete. If any tasks fail, it means that the transaction fails. All steps must be committed (transaction Success) or rolled back (transaction failure). A transaction begins to initiate the execution of the SQL statement. A transaction must be committed or rolled back. It is separate operations succeed is transaction succeed and committed to the database. If any separate operation fails means transaction failure and must be undone rolled back. The following are the properties of a transaction.
1. Atomicity
2. Consistency
3. Isolation
4. Durability
Transaction Process
BEGIN TRANSACTION - Starts the transaction
ROLLBACK - If an error occurred, reverts the existing transaction changes
COMMIT - No error occurred, then it saves all transaction states
SAVEPOINT - Rollback particular named transaction
Example
- BEGIN TRANSACTION T1
- UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1
- COMMIT;
-
- BEGIN TRANSACTION T1
- UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1
- ROLLBACK;
-
- SAVEPOINT T1
- UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1
- ROLLBACK T1;