MySQL Transaction

Introduction on Transaction

A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL (Data Definition Language (DDL) is used to manage table and index structure and CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements) statement is issued.

Understand the concept of a transaction

To understand the concept of a transaction, consider a banking database. Suppose a bank customer transfers money from his savings account (SB a/c) to his current account (CA a/c), the statement will be divided into four blocks :

• Debit SB a/c.
• Credit CA a/c.
• Record in Transaction Journal
• End Transaction

The SQL statement to debit SB a/c is as follows:

UPDATE sb_accounts

SET balance = balance - 1000

WHERE account_no = 932656 ; 

The SQL statement to credit OD a/c is as follows:

UPDATE ca_accounts

SET balance = balance + 1000

WHERE account_no = 933456 ; 

The SQL statement for record in transaction journal is as follows:

INSERT INTO journal VALUES 

(100896, 'Tansaction on Benjamin Hampshair a/c', '26-AUG-08' 932656, 933456, 1000); 

The SQL statement for End Transaction is as follows :

 COMMIT WORK;

MySQL and the ACID Model

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In MySQL, InnoDB storage engine supports ACID-compliant features. The following sections discuss how MySQL features, in particular, the InnoDB storage engine, interact with the categories of the ACID model:

Atomicity: The atomicity aspect of the ACID model mainly involves InnoDB transactions. Related MySQL features include :

• Autocommit setting.
• COMMIT statement.
• ROLLBACK statement.
• Operational data from the INFORMATION_SCHEMA tables.

Consistency: The consistency aspect of the ACID model mainly involves internal InnoDB processing to protect data from crashes. Related MySQL features include :

• InnoDB doublewrite buffer.
• InnoDB crash recovery.

Isolation: The isolation aspect of the ACID model mainly involves InnoDB transactions, in particular, the isolation level that applies to each transaction. Related MySQL features include :

• Autocommit setting.
• SET ISOLATION LEVEL statement.
• The low-level details of InnoDB locking. During performance tuning, you see these details through INFORMATION_SCHEMA tables.

Durability: The durability aspect of the ACID model involves MySQL software features interacting with your particular hardware configuration. Because of the many possibilities depending on the capabilities of your CPU, network, and storage devices, this aspect is the most complicated to provide concrete guidelines for. Related MySQL features include:

• InnoDB doublewrite buffer turned on and off by the innodb_doublewrite configuration option.
• Configuration option innodb_flush_log_at_trx_commit.
• Configuration option sync_binlog.
• Configuration option innodb_file_per_table.
• Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
• Battery-backed cache in a storage device.
• The operating system used to run MySQL, in particular, its support for the fsync() system call.
• Uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
• Your backup strategy, such as frequency and types of backups, and backup retention periods.
• For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.

MySQL Transaction

MySQL (here we maintain version 5.6) supports local transactions (within a given client session) through statements such as SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK. Here is the syntax of START TRANSACTION, COMMIT, and ROLLBACK:

START TRANSACTION
   transaction_characteristic [, transaction_characteristic] ...] 

transaction_characteristic:     
    WITH CONSISTENT SNAPSHOT    
    | READ WRITE    
    | READ ONLY    

BEGIN [WORK]  
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]  
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]  
SET autocommit = {0 | 1}

These statements provide control over use of transactions :

• The START TRANSACTION or BEGIN statement begins a new transaction.


• COMMIT commits the current transaction, making its changes permanent. 


• ROLLBACK rolls back the current transaction, canceling its changes.


• The SETautocommit statement disables or enables the default autocommit mode for the current session.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.

Currently (by default), MySQL runs with autocommit mode enabled.

mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        9 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.09 sec)

Let execute an update command:

mysql>mysql> UPDATE STUDENT_MAST SET ST_CLASS=8 WHERE STUDENT_ID=2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        8 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

Now execute the ROLLBACK command to return in the previous stage :

mysql>mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)

mysql>mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        8 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)
 

There is no roll back as MySQL runs with autocommit mode enabled.

To disable autocommit mode, use the START TRANSACTION statement. See the following example :

mysql>mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE STUDENT_MAST SET ST_CLASS=10 WHERE STUDENT_ID=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |       10 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        8 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

MySQL statements that cannot be Rolled Back and statements that cause an implicit Commit

In MySQL, some statements cannot be rolled back. DDL statements such as CREATE or DROP databases, CREATE, ALTER or DROP tables or stored routines. You should design a transaction without these statements.

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.

• Data definition language (DDL) statements that define or modify database objects. 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.


• ALTER FUNCTION, CREATE FUNCTION, and DROP FUNCTION also cause an implicit commit when used with stored functions, but not with UDFs. (ALTER FUNCTION can only be used with stored functions.)


• ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used.


• Statements that implicitly use or modify tables in the MySQL database. CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD.


• Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.


• Data loading statements. LOAD DATA INFILE. LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine.


• Administrative statements. ANALYZE TABLE, CACHE INDEX, CHECK TABLE, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE.


• Replication control statements. Beginning with MySQL 5.6.7: START SLAVE, STOP SLAVE, RESET SLAVE, CHANGE MASTER TO.
  

SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT

InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK.

The SAVEPOINT statement sets a named transaction savepoint with a name of the identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.

The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint.

Here is the syntax:

SAVEPOINT identifier  
ROLLBACK [WORK] TO [SAVEPOINT] identifier  
RELEASE SAVEPOINT identifier

LOCK and UNLOCK Tables

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked.

UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

Here is the syntax:

LOCK TABLES      
  tbl_name [[AS] alias] lock_type      
  [, tbl_name [[AS] alias] lock_type] ...    

lock_type:      
  READ [LOCAL]    
 | [LOW_PRIORITY] WRITE    

UNLOCK TABLES

SET TRANSACTION Syntax

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...
	
transaction_characteristic:      
    ISOLATION LEVEL level    
  | READ WRITE    
  | READ ONLY    

level:       
  REPEATABLE READ     
  | READ COMMITTED     
  | READ UNCOMMITTED     
  | SERIALIZABLE

• With the GLOBAL keyword, the statement applies globally for all subsequent sessions. Existing sessions are unaffected.
• With the SESSION keyword, the statement applies to all subsequent transactions performed within the current session.
• Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session.

Reference: MySQL 5.6 Manual

Frequently Asked Questions (FAQ) - MySQL Transactions

1. What is a transaction in MySQL?

• A transaction is a logical unit of work containing one or more SQL statements that can be committed or rolled back. Transactions ensure that either all changes are applied or none are.

2. When does a transaction start and end?

• A transaction starts with the first executable SQL statement and ends with a COMMIT or ROLLBACK. It can also end implicitly with a Data Definition Language (DDL) statement.

3. What is the ACID model in MySQL?

• ACID stands for Atomicity, Consistency, Isolation, and Durability. It ensures reliable transaction processing. MySQL, particularly with the InnoDB storage engine, supports ACID-compliant features.

4. What are the key aspects of the ACID model in MySQL?

• Atomicity: Ensures all operations within a transaction are completed successfully.


• Consistency: Ensures the database remains in a consistent state before and after the transaction.


• Isolation: Ensures that the operations within a transaction are isolated from other transactions.


• Durability: Ensures that once a transaction is committed, it remains so, even in the event of a system crash.

5. What statements control transactions in MySQL?

• START TRANSACTION or BEGIN starts a new transaction.


• COMMIT commits the current transaction, making changes permanent.


• ROLLBACK rolls back the current transaction, undoing changes.


• SET autocommit enables or disables the default autocommit mode.

6. What is autocommit mode in MySQL?

• By default, MySQL runs with autocommit mode enabled, meaning each statement is committed immediately. This can be disabled with the SET autocommit statement to allow for manual control over transactions.

7. Which MySQL statements cannot be rolled back?

• DDL statements like CREATE, ALTER, and DROP cannot be rolled back. Additionally, some administrative and replication control statements implicitly commit the transaction.

8. What is a savepoint in MySQL?

• A savepoint allows a transaction to be rolled back to a specific point without rolling back the entire transaction. Savepoints can be set, rolled back to, and released using SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT statements.

9. How do LOCK and UNLOCK TABLES work in MySQL?

• LOCK TABLES explicitly acquires table locks for the current session to prevent other sessions from modifying them. UNLOCK TABLES releases any table locks held by the session. Locks can be acquired for base tables or views.

10. What is the syntax for setting transaction characteristics in MySQL?

• The SET TRANSACTION statement sets transaction characteristics such as isolation levels and read/write modes for the global scope, session scope, or the next transaction within the session.

Previous: MySQL Triggers
Next: MySQL Views