Transactions in MySQL

What is a transaction?

A database transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit.
All individual operations must succeed or we must revert changes done by each operation in order to call this unit of work as a valid transaction.
All transactions have four standard properties - Atomicity, Consistency, Isolation, Durability (ACID).

MySQL transaction features:

In MySQL, by default, each statement is a transaction i.e. each modification is instantly committed into the database and you can not rollback the changes made. The behavior of transaction is controlled by autocommit.
MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.
A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit start transaction or begin statement and ending it with a commit or rollback statement.
If autocommit mode is disabled within a session with set autocommit = 0, the session always has a transaction open. A commit or rollback statement ends the current transaction and a new one starts.
If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
Some statements implicitly end a transaction, as if you had done a commit before executing the statement. For example - create, drop, alter and many others.
autocommit -> 1 (ON) (enabled, default value) -> Each SQL statement (until and unless started in an explicit transaction using start transaction) is considered a complete transaction and committed by default when it finishes.
autocommit -> 0 (OFF) (disabled, using command set autocommit = 0) -> Subsequent statements acts like a transaction and no activities are committed until an explicit commit statement is issued.
If autocommit is 0 and you change it to 1, MySQL performs an automatic commit of any open transaction.
To turn off autocommit globally for all so that clients always begin with a default of 0, we have below options: Add a autocommit=0 variable in my.cnf configuration file in MySQL and restart the MySQL server. Start the server with the --autocommit=0 option
You can see current autocommit mode using below commands: show variables like 'autocommit'; select @@autocommit;

To illustrate, we need to transfer amount x from account A to account B.

Sample pseudo syntax can be as follows:

-- begin a new transaction
begin work; -- or start transaction; -- or set autocommit = 0;
A.balance = A.balance - x;
If above fails -> rollback;
B.balance = B.balance + x;
If above fails -> rollback;
Update ledger table;
If above fails -> rollback;
Send email and SMS notification to both account holders.
If above fails -> rollback;
commit; --or begin work; to start next new transaction.
-- transaction has ended.

Previous Index Next

SQL Tutorial

Transactions in MySQL

XP

Loading comments...