SQL Transaction

By | September 13, 2020
SQL Transaction

If there is a group of SQL commands performed on a database to manipulate the stored data, then all those commands can be treated as a single unit called a transaction. In simple word, we can say that a transaction is a group of SQL queries, and transaction handles all those queries as a single unit.

SQL Transaction

We can refer a standard query as a transaction if it maintains the data integrity in the database. By data integrity means, if any of the transaction commands fails, all the commands fail automatically and the data that was modified in the database rollback. The transaction makes sure that either all the command succeeds or none of them.

Vamware

The transaction concept comes very useful in bank transaction because here money deducts from one account and transfer to another. If during the process of transferring money any command get collapse, we do not want to leave the process in between instead we roll back all the transaction process.

Transaction Properties (ACID)

To be a successful transaction, the group of commands must follow the Transaction property ACID.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

1. Atomicity

All the statements of the transaction should be atomic by nature, which means either all the statements execute successfully, or all fail. This property of transaction ensures that the process does not stop at amid.

For example, if an amount is deducted from a bank account, then it should be credited in another, if in between the credited process collapse then the deduction of the amount should be rolled back.

2. Consistency

Consistency property of transaction state that if a data has been modified, then it should be modified logically.

For example, if you want to deduct the amount from a bank account, then the amount of deduction should be less than or equal to the total amount present in the back account.

3. Isolation

When a transaction is performing on a database, then the current transaction process must not be affected by another transaction. If there are two concurrent transactions request send on a database, then the SQL must isolate both the transactions and execute them one by one.

For example, if transactions A and B try to withdraw money from the same bank account at the same time, then their requests must be executed one after another not simultaneously.

4. Durability

Durability process state that, if a system error or power failure occurs before a set of commands is complete, those commands are undone, and the data is restored to its original state once the system begins running again.

Transaction Control

To make the transition process occur in SQL, we use some commands:

  • COMMIT
  • ROLLBACK
  • SAVEPOINT
  • SET TRANSACTION

1. COMMIT Command

All the SQL DML commands such as UPDATE, INSERT, DELETE, etc., come under COMMIT. These commands make changes in the database and automatically commit and save all the changes. However, SQL also provides us with a COMMIT command which can save all the previous COMMIT and ROLLBACK.

Example

Consider this table for all the SQL queries:

Accounts

+----------+---------+
| UserName | Balance |
+----------+---------+
| Sam      | 1000.00 |
| John     | 1000.00 |
| Sofi     | 1000.00 |
| Andrew   | 1000.00 |
+----------+---------+

Query: Delete the Account of Sam and COMMIT the changes;

DELETE FROM Accounts
WHERE UserName = ‘Sam’;

COMMIT;

Verify the database

SELECT * FROM Accounts;

+----------+---------+
| UserName | Balance |
+----------+---------+
| John     | 1000.00 |
| Sofi     | 1000.00 |
| Andrew   | 1000.00 |
+----------+---------+

2. ROLLBACK COMMAND

The ROLLBACK is used to undo the last transactions that have been saved in the database. ROLLBACK command only undoes those transactions which were issued by previous COMMIT or ROLLBACK commands.

Query: ROLLBACK the DELETE command and bring back the data of Sam.

ROLLBACK;

Verify the database

SELECT * FROM Accounts;

#Output
+----------+---------+
| UserName | Balance |
+----------+---------+
| Sam      | 1000.00 |
| John     | 1000.00 |
| Sofi     | 1000.00 |
| Andrew   | 1000.00 |
+----------+---------+

3. Savepoint Command

The SavePoint command is used to save a point of Transaction state which can be ROLLED back easily using the ROLLBACK command rather than undoing the entire transaction.

Syntax

SAVEPOINT SavePoint_Name;

Example

DELETE FROM Accounts WHERE UserName='Sam';
1 row deleted.

SAVEPOINT SP1;
Savepoint created.

DELETE FROM Accounts WHERE UserName='John';
1 row deleted.

SAVEPOINT SP2;
Savepoint created.

Now if we want to RollBack to that transaction where we delete the Sam Account.

ROLLBACK TO SP1;

To delete the SavePonts, we can use the RELEASE SavePoint Command.

Syntax

RELEASE SAVEPOINT SAVEPOINT_NAME;

SET TRANSACTION

Using the SET TRANSACTION command, we can group SQL queries as a Transaction. This command is generally used to initiate a database transaction. Using SET Transaction command, we can also specify the transaction mode to READ ONLY or READ WRITE.

Syntax

SET TRANSACTION [ READ WRITE | READ ONLY ];

Summary

  • A transaction is a Group of SQL queries which treated as a single unit.
  • The transaction must follow the ACID property to be a successful transaction.
  • Atomicity ensure that either all the operations work successfully or all fail.
  • Consistency ensures the logical modification of data.
  • Isolation ensures that no two transactions interfere with each other.
  • Durability helps in rolling back the data if the SQL start rebooting.
  • COMMIT command is used to save the changes.
  • ROLLBACK command rolls back the past COMMIT and ROLLBACK command.
  • SAVEPOINT is used to save transaction COMMITS states.
  • SET TRANSACTION is used to group queries into a single unit.

Leave a Reply

Your email address will not be published. Required fields are marked *