Introduction to Transactions in SQL
Transactions in SQL are a single or consecutive set of processes completed in a rational definite flow that can be performed manually or by creating an automatic database programming. Every transaction in SQL starts as a flow from a particular job and should complete with another working job. If any or all of the jobs are incomplete, the transaction status is said to be ‘fail’. A Transaction is said to be ‘complete’ or ‘success’ if and only if all the jobs are completed successfully.
The transaction, once committed, cannot be a rollback; it can be rollback only if the transaction is not committed. MYSQL automatically commits the changes to the database if all the queries are executed successfully. To explicitly commit the changes into the database, first, need to disable the auto-commit through the command –
Syntax: SET autocommit = 0;
Properties of the Transaction
Following are the important properties of the transactions; every transaction must follow these properties.
1. Atomicity
A transaction must be atomic; mean data manipulation should be completed for a certain logical unit. This property ensures that data changes taken place completely; otherwise, rollback the transaction.
2. Consistency
Once the Transaction finishes, All the available records will be consistent throughout the transaction. This property ensures that database property switched state after a successful commit or not.
3. Isolation
Isolation refers to data changes at a certain logical unit that should not affect on another unit. It allows a transaction to execute independently.
4.5 (6,425 ratings)
View Course
4. Durability
Changes made during transactions should be permanent in the system. In case of a system error, this property also ensures that data changes take place or not.
The above-given property of the transaction is also known as ACID property.
Steps of Transaction
Below are the steps:
1. Begin
A transaction may occur in multiple SQL executions, but all SQL should run at once. If any of the transactions fails, then the whole transaction would be reverted. The statement for starting the transaction is “START TRANSACTION”,, .begins the acronym for the START TRANSACTION.
Syntax: START TRANSACTION;
2. Commit
Commits permanently reflect the changes to the database. The statement for starting the transaction is “COMMIT”.
Syntax: COMMIT;
3. Rollback
Rollback is used to revert the changes, i.e. record will not be changed, It would be in the previous state. The statement for starting the transaction is “ROLLBACK”.
Syntax: ROLLBACK;
4. Savepoint
SAVEPOINT is also a transaction statement. This statement used to create a store point in the system so that the ROLLBACK operation can achieve the state of the savepoint.
5. Release Savepoint
RELEASE SAVEPOINT is a statement to release the savepoint & memory consumed by the system in creating a save point.
Syntax: RELEASE SAVEPOINT SP
Notes – SP was the name of the savepoint when this savepoint was created before the transaction start.
6. Set Transaction
The SET TRANSACTION command is used to specify the transaction attribute, such as the given transaction is a read-only or read-write session.
Syntax: SET TRANSACTION [ READ-WRITE | READ ONLY ];
The transaction is used to perform complex changes in the database. Its mainly used in banking related information changes into a relational database.
The transaction is supported by the MYSQL engine InnoDB. By default, auto-commit remains enabled; that’s why whenever any SQL executes after execution, commits automatically take place.
Transactions using SQL
Example #1
Banking Transaction: An account debited for 50000 amount from person A saving account & submitted this amount to the loan account of A.
Start Transaction: This start transaction will convert all the SQL queries to a single unit of transaction.
UPDATE `account` SET `balance` = `balance` - 50000 WHERE user_id = 7387438;
This SQL query deducts the amount from the existing account balance.
UPDATE `loan_account` SET `paid_amount` = `paid_amount` + 50000 WHERE user_id = 7387438;
This SQL query adds the amount to the user loan account.
Insert into `transaction_details`(`user_id`, ‘amount’) values (7387438, ‘50000’);
This SQL query inserts a new record into the transaction details table; this table holds the details of all the transactions of the users. If all the query executed successfully, then the COMMIT command needs to be executed as changes need to be permanently stored in the database.
Commit: This commit statement saves changes invoked by a transaction to the database. If any of the transaction fails during the execution, then the ROLLBACK command should be executed to revert the complete transaction.
Rollback: Rollback takes place once any query fails during execution.
Example #2
Inventory Transaction: In the given Items table, 6 items are available.
Executing the following START TRANSACTION statement to start the transaction.
Now run command SET AUTOCOMMIT = 0;to disable the auto-commit
Now executing the following statement to remove the record from the items table
Now Available record in the table is 4, i.e. records temporary removed from the table items
Now executing command ROLLBACK to revert the changes, the deleted record would be available in the table items like as previously before starting the transaction
Again if applying the same delete operation, then COMMIT operation after it would be saved permanently in the database
Now we can see that after the ROLLBACK command still, the record was in a new state; it means once the COMMIT operation performed, changes can not be reverted because it permanently makes changes into the database;
Benefits of using Transaction in SQL
a) Using Transaction improves the performance; when inserting 1000 records using transactions, in that case, time taken would be lesser than normal insertion. As in a normal transaction, every time COMMIT would take place after each query execution & it would increase the time of execution every time while in a transaction, no need to execute COMMIT statement after each SQL query. COMMIT at the end would reflect all the changes to the database permanently at once. Also, If using a transaction, then reverting of the changes would be much easier than the normal transaction. ROLLBACK will revert all the changes at once & keep the system in the previous state.
b) The transaction ensures data integrity in the relational database. Most of the database uses multiple tables to maintain the data, and while making updates, there may have changes in the multiple tables at that time; if any of the SQL queries fail, then the transaction would keep the data unchanged.
Conclusion
Using Transactions is a best practice in information update for a logical unit in a relational database. For transaction implementation database engine should support the transaction like the InnoDB engine. The transaction, as a unit of SQL statements, can be reverted at once using a single ROLLBACK statement. The transaction ensures data integrity & improve database performance.
Recommended Articles
This is a guide to Transactions in SQL. Here we discuss the introduction, properties, steps, examples of transactions in SQL, along with the benefits of using transaction in SQL. You may also have a look at the following articles to learn more –
