In this short tutorial, we will explore how transcations work in MySQL.
Now, we would create a simple bank table;
CREATE TABLE Bank(
name VARCHAR(10),
amnt DECIMAL(10,2),
CONSTRAINT AmntC CHECK (amnt>=0) );
Add few users to the bank
INSERT INTO bank VALUES("Bob", 40);
INSERT INTO bank VALUES ("Joe", 50);
By default, in mysql shell (or Workbench) each statement is a sperate transcation and it is auto commited (auto commit)
SHOW VARIABLES LIKE 'AUTOCOMMIT';
To set autocommit to false
SET AUTOCOMMIT = 0;
OR
SET AUTOCOMMIT = OFF;
To transfer 10$ from Bob to Joe
START TRANSACTION;
UPDATE bank SET amnt=amnt-10 WHERE name='Bob';
UPDATE bank SET amnt=amnt+10 WHERE name='Joe';
COMMIT;
After executing the statements You should get the following when executing 'select * from bank;'
| name | age |
|---|---|
| Bob | 30 |
| Joe | 60 |
Execute the following statemnts
START TRANSACTION;
SELECT * FROM bank;
UPDATE bank SET amnt=amnt+10 WHERE name='Bob';
SELECT * FROM bank;
UPDATE bank SET amnt=amnt-10 WHERE name='Joe';
SELECT * FROM bank;
ROLLBACK;
When we are using rollback, the table is rolled back to the previous state.
Other instances (or connections would not see these updates until commit is executed.)
The previous transcation can results in a several pitfalls.
For eaxample, running the following statements
START TRANSACTION;
UPDATE bank SET amnt=amnt-100 WHERE name='bob';
UPDATE bank SET amnt=amnt+100 WHERE name='joe';
COMMIT;
| name | age |
|---|---|
| Bob | 30 |
| Joe | 160 |
Basically 100$ has been magically appeared in Joe account.
To handle that in mysql, we need to create a procedure (so we can use IF and or execption)
DELIMITER //
CREATE PROCEDURE `TRANSFER`(
IN FromACCNT VARCHAR(10),
IN ToACCNT VARCHAR(10),
IN amount DECIMAL(10,2))
BEGIN
IF EXISTS(SELECT * FROM bank WHERE name=FromAccnt AND amnt > amount) AND EXISTS(SELECT * FROM bank WHERE name=ToAccnt) THEN
START TRANSACTION;
UPDATE bank SET amnt=amnt-amount WHERE name=FromAccnt;
UPDATE bank SET amnt=amnt+amount WHERE name=ToAccnt;
COMMIT;
END IF;
END//
TO use it
DELIMITER ;
CALL TRANSFER('Bob','Joe', 100);
This would not be executed.
Another Approach is by using Exception, as follows:
DELIMITER //
CREATE PROCEDURE Transfer2(
IN FromACCNT VARCHAR(10),
IN ToACCNT VARCHAR(10),
IN amount DECIMAL(10,2))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK; -- ROLLBACK ANY error IN the TRANSACTION
END;
START TRANSACTION;
UPDATE bank SET amnt=amnt-amount WHERE name=FromAccnt;
UPDATE bank SET amnt=amnt+amount WHERE name=ToAccnt;
COMMIT;
END//
DELIMITER ;
CALL TRANSFER2('Bob','Joe', 10);
AND
CALL TRANSFER2('Bob','Joe', 100);