ACID Transaction

ACID transaction is an indivisible unit of work. It can consist of one or more SQL statements, but to be considered successful, either all of those SQL statements must complete successfully, leaving the database in a new stable state, or none must complete, leaving the database as it was before the transaction began.

For example, if you make a purchase using your bank card, many things must happen.

The product must be added to your cart.
Your payment must be processed.
Your account must be debited the correct amount and the
store’s account credited
The inventory for that product must be reduced by the number purchased.

Let’s look at the example in more detail.

If Rose buys boots for $200, then you can use an UPDATE statement to decrease her account balance
And another UDATE statement to add $200 to the Shoe Shop balance
And a final update statement to decrease the stock level of boots at the Shoe Shop by 1
If any of these UPDATE statements fail, the whole transaction should fail, to keep the data in a consistent state.

The types of transactions in the example are called ACID transactions. ACID stands for

Atomic - All changes must be performed successfully or not at all
Consistent - Data must be in a consistent state before and after the transaction
Isolated - No other process can change the data while the transaction is running
Durable - The changes made by the transaction must persist

To start an ACID transaction, use the command BEGIN. In db2 on Cloud, this command is implicit.

Any commands you issue after that are part of the transaction, until you issue either
COMMIT, or ROLLBACK.
If all the commands complete successfully, issue a commit command to save everything in the database to a consistent, stable state
If any of the commands fail; perhaps Rose’s account doesn’t have enough money to make the payment, you can
issue a rollback command to undo all the changes and leave the database in its previously consistent stable state.

SQL statements can be called from languages like Java, C, R, and Python. This requires the use of database-specific access APIs such as Java Database Connectivity (JDBC) for Java or a specific database connector like ibm_db for Python.

Most languages use the EXEC SQL commands to initiate a SQL command, including COMMIT and ROLLBACK, as you can see in this example.
Remember that BEGIN is implicit, you do not need to call it out explicitly.

Incorporating SQL commands into your application code gives you the opportunity to create error-checking routines that in turn control whether the transaction is committed or rolled back.

Example 1

We will import the BankAccounts-CREATE.sql and ShoeShop-CREATE.sql scripts from local drive, and load them to the phpMyAdmin console.
The scripts will create new tables called BankAccounts and ShoeShop and populate them with the sample data as well

Commit & Rollback

Scenario: Rose is buying a pair of boots from ShoeShop. So we have to update Rose’s balance as well as the ShoeShop balance in the BankAccounts table. Then we also have to update Boots stock in the ShoeShop table.
After Boots, let’s also attempt to buy Rose a pair of Trainers.

Once the tables are ready, create a stored procedure routine named TRANSACTION_ROSE that includes TCL commands like COMMIT and ROLLBACK.
Now develop the routine based on the given scenario to execute a transaction.
To create the stored procedure routine on MySQL, use the code below in the textarea of the SQL page. Click Go.

DELIMITER //

CREATE PROCEDURE TRANSACTION_ROSE()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    START TRANSACTION;
    UPDATE BankAccounts
    SET Balance = Balance-200
    WHERE AccountName = 'Rose';

    UPDATE BankAccounts
    SET Balance = Balance+200
    WHERE AccountName = 'Shoe Shop';

    UPDATE ShoeShop
    SET Stock = Stock-1
    WHERE Product = 'Boots';

    UPDATE BankAccounts
    SET Balance = Balance-300
    WHERE AccountName = 'Rose';

    COMMIT;
END //

DELIMITER ;

Call Transaction

CALL TRANSACTION_ROSE;

SELECT * FROM BankAccounts;

SELECT * FROM ShoeShop;

Results

Observe that the transaction has been executed. But when we observe the tables, no changes have permanently been saved through COMMIT. All the possible changes happened might have been undone through ROLLBACK since the whole transaction fails due to the failure of a SQL statement or more. Let’s go through the possible reason behind the failure of the transaction and how COMMIT - ROLLBACK works on a stored procedure:
- The first three UPDATEs should run successfully. Both the balance of Rose and ShoeShop should have been updated in the BankAccounts table. The current balance of Rose should stand at 300 - 200 (price of a pair of Boots) = 100. The current balance of ShoeShop should stand at 124,200 + 200 = 124,400. The stock of Boots should also be updated in the ShoeShop table after the successful purchase for Rose, 11 - 1 = 10.
- The last UPDATE statement tries to buy Rose a pair of Trainers, but her balance becomes insufficient (Current balance of Rose: 100 < Price of Trainers: 300) after buying a pair of Boots. So, the last UPDATE statement fails. Since the whole transaction fails if any of the SQL statements fail, the transaction won’t be committed.

Example 2

We’ll use the base of example 1 but we will create a stored procedure TRANSACTION_JAMES to execute a transaction based on the following scenario:
- First buy James 4 pairs of Trainers from ShoeShop.
- Update his balance as well as the balance of ShoeShop.
- Also, update the stock of Trainers at ShoeShop.
- Then attempt to buy James a pair of Brogues from ShoeShop
- If any of the UPDATE statements fail, the whole transaction fails.
- You will roll back the transaction.
Commit the transaction only if the whole transaction is successful

Breakdown:

Take 1200 (4 x 300) from James’s balance and add 1200 to the ShoeShop balance
Take 4 trainers out of the stock in ShoeShop
Then take 150 from James’s balance.

DELIMITER //

CREATE PROCEDURE TRANSACTION_JAMES()

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE BankAccounts
    SET Balance = Balance-1200
    WHERE AccountName = 'James';

    UPDATE BankAccounts
    SET Balance = Balance+1200
    WHERE AccountName = 'Shoe Shop';

    UPDATE ShoeShop
    SET Stock = Stock-4
    WHERE Product = 'Trainers';

    UPDATE BankAccounts
    SET Balance = Balance-150
    WHERE AccountName = 'James';

    COMMIT;

END //

DELIMITER ;

Call Transaction

CALL TRANSACTION_JAMES;

SELECT * FROM BankAccounts;

SELECT * FROM ShoeShop;