MariaDB Transactions

Posted on In

MariaDB Transaction and Isolation Level explained.

Transaction

“An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data.”

— The SQL Standard

Autocommit

By default, MariaDB runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MariaDB stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:

1
SET autocommit=0;

Start Transaction

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.

see example from START TRANSACTION

1
2
3
4
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Isolation Level

MasriaDB supports the 4 different transaction isolation levels.

READ UNCOMMITTED

READ UNCOMMITTED isolation level allows dirty reads. One transaction may see not-yet-committed changes made by some other transaction.

You may use this isolation level when making rough estimates(like COUNT(*) or SUM(*)) etc.

READ COMMITTED

READ COMMITTED isolation level guarantees that any data read was committed. READ COMMITTED prevents dirty reads.

Under READ COMMITTED, a second SELECT in a transaction may return different data because a concurrent transaction may update, delete or insert new record.

REPEATABLE READ

REPEATABLE READ is a higher isolation level. It guarantees to read committed data and guarantees any dataread cannot change. So if you read the data again, the data is unchanged.

Under REPEATABLE READ,a second SELECT is guaranteed to display at least the rows that were returned from the first SELECT unchanged. New rows may be added by a concurrent transaction, but the existing rows cannot be deleted nor changed.

REPEATABLE READ is the default isolation level for MySQL and MariaDB.

SERIALIZABLE

SERIALIZABLE is higher isolation level than REPEATABLE READ.

under SERIALIZABLE reads, the second select is guaranteed to see exactly the same rows as the first.

Examples

Check Global Isolation Level

1
2
3
4
5
6
7
> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

Check session level isolation level

1
2
3
4
5
6
7
> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

Set global isolation level

1
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Set session isolation level within an existing transaction

1
2
3
4
START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation;;
COMMIT;

Reference