Performing transactions

Create dummy tables and sample data to understand this recipe:

CREATE DATABASE bank;
USE bank;
CREATE TABLE account(account_number varchar(10) PRIMARY KEY, balance int);
INSERT INTO account VALUES('A',600),('B',400);

To start a transaction (set of SQLs), execute the START TRANSACTION or BEGIN statement:

START TRANSACTION;

Then execute all the statements that you wish to be inside a transaction, such as transferring 100 from A to B:

SELECT balance INTO @a.bal FROM account WHERE account_number='A';

Query OK, 1 row affected (0.00 sec)

Programmatically check if @a.bal is greater than or equal to 100

UPDATE account SET balance=@a.bal-100 WHERE account_number='A';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT balance INTO @b.bal FROM account WHERE account_number='B';

Query OK, 1 row affected (0.00 sec)

Programmatically check if @b.bal IS NOT NULL

UPDATE account SET balance=@b.bal+100 WHERE account_number='B';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After making sure that all the SQLs are executed successfully, execute the COMMIT statement, which will finish the transaction and commit the data:

COMMIT;

Query OK, 0 rows affected (0.09 sec)

If you encounter any error in between and wish to abort the transaction, you can issue a ROLLBACK statement instead of COMMIT.

For example, instead of sending to B, if A wants to transfer to an account that does not exist, you should abort the transaction and refund the amount to A:

BEGIN;

SELECT balance INTO @a.bal FROM account WHERE account_number='A';
UPDATE account SET balance=@a.bal-100 WHERE account_number='A';

SELECT balance INTO @c.bal FROM account WHERE account_number='C';

Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

SELECT @c.bal;

+--------+
| @c.bal |
+--------+
| NULL   |
+--------+
1 row in set (0.01 sec)

ROLLBACK;

Query OK, 0 rows affected (0.08 sec)

Autocommit

By default, autocommit is ON, which means that all individual statements are committed as soon as they are executed unless they are in a BEGIN...COMMIT block. If autocommit is OFF, you need to explicitly issue a COMMIT statement to commit a transaction. To disable it, execute:

SET autocommit=0;

DDL statements, such as CREATE or DROP for databases and CREATE, DROP, or ALTER for tables or stored routines cannot be rolled back.

There are certain statements such as DDLs, LOAD DATA INFILE, ANALYZE TABLE, replication-related statements and so on that cause implicit COMMIT. For more details on these statements, refer https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html.