In this chapter we will briefly go over basics in transactions, such as money transactions, and how to properly execute them with MySQL.

A MySQL transaction is a group of sequential database manipulation operations. All the operations should be successfully completed, else even if one statement fails, the operation is aborted. The successful statements should change the database as expected and the change should last even in case of a database failure, or a connection loss with the user.

MySQL supports local transactions i.e. within given client sessions through statements such as SET autocommit, START TRANSACTION, COMMIT and ROLLBACK.

Transaction sequence

Following statements are involved in a transaction sequence:

  • START TRANSACTION or BEGIN starts a new transaction. They are used as aliases to each other.
  • COMMIT commits the transaction and makes the changes permanent.
  • ROLLBACK cancels the current changes and moves back the current transaction. However some statements such as CREATE, DROP, ALTER, TRUNCATE TABLE, SET PASSWORD, GRANT RENAME USER, LOCK TABLE, UNLOCK TABLE, etc... cannot be rolled back.
  • SET autocommit enables or disables the default auto commit mode. When it is enabled, the transaction updates or modifies the table when they are being executed. Else, they have to be committed using COMMIT statement. By default the autocommit is enabled and the changes in database cannot be rolled back.

Enable or disable autocommit

To disable autocommit mode implicitly we use START TRANSACTION statement. On the other hand to disable it explicitly use already mentioned SET autocommit statement:

SET autocommit=0;

Transaction examples

Consider an example below, where a table is created with name of 'students', 'rollId', 'points' as its columns. To update 'points' of 'students' in database we execute the below statements:

START TRANSACTION;

UPDATE tblstudents SET points=98 WHERE rollID=12;

UPDATE tblstudents SET points=56 WHERE rollID=16;

UPDATE tblstudents SET points=79 WHERE rollID=20;

SELECT * FROM tblstudents;

COMMIT;

The START TRANSACTION disables the autocommit mode. The updates done to the database are reflected with SELECT statements. But they are permanent only after executing COMMIT statement. Instead of COMMIT user can choose to ROLLBACK and undo the update done to database. The example below shows a ROLLBACK function:

START TRANSACTION;

UPDATE tblstudents SET points=98 WHERE rollID=12;

SELECT * FROM tblstudents;

ROLLBACK;

The statement above does not save the changes in the database done by the previous UPDATE statement.