MySQL Transaction

Foreword: In this tutorial, I explain how to code a transaction in MySQL.

By: Chrysanthus Date Published: 13 Apr 2016

Introduction

In this tutorial, I explain how to code a transaction in MySQL. More than one client can connect to the MySQL server at one time (duration). It is possible for two or more users to attempt to edit the same tables at a particular time. To prevent conflict and inconsistent result, the tables of interest have to be locked from the rest of the clients, so that the very first client uses the tables. When he finishes, the tables are unlocked. That is transaction. The tables will be locked again for the next user, and released after his transaction.

START TRANSACTION and COMMIT
In the following transaction code, an ID is read from one table. The ID is used to update a different table.

START TRANSACTION;
SELECT @ID := SaleID FROM sales WHERE EmployeeID=1 AND CustomerID=3;
UPDATE SaleDetails SET Quantity=8 WHERE SaleID=@ID;
COMMIT;

The transaction begins with the statement “START TRANSACTION;” and ends with the statement “COMMIT;”. In between, the SQL statement results are saved temporarily and not permanently. They are saved to disk permanently with the “COMMIT;” statement.

Statements That Cause an Implicit Commit
There are some statements that are always committed whether or not they are in a transaction. Typical of these are the Data Definition Language (DDL) statements that define or modify database objects. These are: ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE, TRUNCATE TABLE.

SAVEPOINT and ROLLBACK Statements
In a transaction, the SQL statements can be many. Rollback means reverse. Within a transaction, it is possible to rollback a group of SQL statements. The coding is something like:

START TRANSACTION;

#statements

SAVEPOINT identifier

#statements

IF Condition ROLLBACK TO identifier

#statements

COMMIT;

The SQL statements between “SAVEPOINT identifier” and “ROLLBACK TO identifier” will rollback if the condition is true. The starting point for rollback is “SAVEPOINT identifier” and the ending point is the statement with “ROLLBACK TO identifier”. identifier is a name of your choice to identify the SAVEPOINT.

In the following code the statements with (6, 6) are rollback (not saved), while the rest are saved (committed).

SET @var = 2;

START TRANSACTION;
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (3, 3);
SAVEPOINT sp1;
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (6, 6);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (6, 6);
ROLLBACK TO sp1;
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (2, 2);
COMMIT;

With MySQL, to have a condition for rollback, you will need to call a stored procedure in the place of “ROLLBACK TO sp1;” in this code.

Note: if there are more than one SAVEPOINT with the same identifier, only the last one takes effect.

That is it for this tutorial.

Chrys

Broad Network

Related Articles

MySQL Transaction

Introduction

Related Links

Comments