SQL Transaction

Posted in /   /  

SQL Transaction
vinaykhatri

Vinay Khatri
Last updated on March 28, 2024

    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 words, we can say that a transaction is a group of SQL queries, and the transaction handles all those queries as a single unit.

    SQL Transaction

    We can refer to a standard query as a transaction if it maintains the data integrity in the database. 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.

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

    Transaction Properties (ACID)

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

    • A tomicity
    • C onsistency
    • I solation
    • D urability

    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 to 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 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 bank account.

    3. Isolation

    When a transaction is performed on a database, then the current transaction process must not be affected by another transaction. If there are two concurrent transaction requests sent on a database, then the SQL must isolate both 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

    The 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 your 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 the 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 starts rebooting.
    • COMMIT command is used to save the changes.
    • ROLLBACK command rolls back the past COMMIT and ROLLBACK commands.
    • SAVEPOINT is used to save transaction COMMITS states.
    • SET TRANSACTION is used to group queries into a single unit.

    People are also reading:

    Leave a Comment on this Post

    0 Comments