The user can use a set of SQL statements to control transactions. These statements allow us to explicitly start, commit, or rollback a transaction, but in some cases such operations are implicit. We can also set an isolation level, which determines which locks are acquired and how consistent the reads are. We can also declare in advance that a transaction is read only; this allows InnoDB to execute more internal optimizations.
Usually, MariaDB transactions start with the START TRANSACTION statement and end with COMMIT or ROLLBACK. BEGIN WORK is a synonym for START TRANSACTION, but it does not work within stored programs, because the BEGIN and END keywords are used to enclose code blocks. The general syntax is:
START TRANSACTION; <one or more statements> COMMIT;
The START TRANSACTION AND CHAIN command means that another transaction will immediately start after COMMIT or ROLLBACK, so it is useless to repeat START TRANSACTION.
Some statements are not transactional and implicitly commit the current transaction. The list of such statements is long and varies slightly from version to version. As a general rule, all DML or DCL statements, as well as administrative statements, are not transactional. The DML statements that only involve temporary tables are an exception.
By default, the autocommit server variable is ON. It can be changed at global or session level. If it is enabled, each statement is considered a transaction, unless START TRANSACTION is issued. If it is disabled, an implicit START TRANSACTION is added before the first statement and after each COMMIT or ROLLBACK.
MariaDB supports four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level is REPEATABLE READ. To change it, the transaction_isolation server variable or the --transaction-isolation startup option can be set, or the SET TRANSACTION statement can be issued. For example:
MariaDB [(none)]> SET @@tx_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec)
Of course, it is not possible to change the isolation level after the transaction has started.
The isolation level determines how the data that is accessed by the current transaction is isolated from other connections. That is, it determines how the accessed rows are locked and whether snapshots are generated. Strong isolation levels block other connections for a longer period of time, so they should only be used if necessary. For example, a strong isolation level may not be necessary to query big quantities of data for statistical purposes where a small error is tolerated.
With the
READ UNCOMMITTED isolation level, a separate snapshot is created for each read command that is executed in the transaction. Reads use a data snapshot. This snapshot may consist of data from a transaction that has not yet been committed, so it is possible that such data will never exist in the table.
Like READ UNCOMMITTED, the READ COMMITTED isolation level sets a different snapshot for each consistent read in the transaction, but it never uses uncommitted data to create a snapshot. The UPDATE and DELETE statements, as well as locking reads, never use gap locks. This means that the insertion of phantom rows is always possible.
With the
REPEATABLE READ isolation level, all reads within a transaction use the same snapshot. This guarantees much more consistency than the READ COMMITTED isolation level. The UPDATE and DELETE statements, as well as locking reads, and record locks will be used on the UNIQUE indexes; and other indexes gap locks or next-key locks will be used to block insertions on the scanned range of values.
We can think of the SERIALIZABLE isolation level as a REPEATABLE READ mode where all non-locking SELECTs are automatically converted to LOCK IN SHARE MODE. If we only use locking SELECTs, there is no difference between SERIALIZABLE and REPEATABLE READ. Another situation where these levels are identical is when the autocommit mode is enabled. This is because locks are not acquired if the current transaction will finish with the current query.
In practice, SERIALIZABLE can be used when all the queries in a transaction must acquire at least read locks. For example, let's consider the following transaction:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT @a := AVG(price) FROM product WHERE category = 10 LOCK IN SHARE MODE; UPDATE avg_price SET avg = @a WHERE category = 10; COMMIT;
The same effect can be obtained in this way:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; SELECT @a := AVG(price) FROM product WHERE category = 10; UPDATE avg_price SET avg = @a WHERE category = 10; COMMIT;
MariaDB 10.0 introduces the transactions access mode. Two access modes exist: READ WRITE for transactions that may modify the existing data and READ ONLY for transactions that only read data. An exception is that READ ONLY transactions can modify data in temporary tables, but if they try to modify any other data they produce an error. The access mode can be specified with SET TRANSACTION. It is also possible to specify the access mode and isolation level with the same statement. For example:
MariaDB [(none)]> SET TRANSACTION READ ONLY; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY; Query OK, 0 rows affected (0.00 sec)
If the autocommit mode is enabled, MariaDB always knows the exact access mode of a transaction. If it is disabled, or the transaction starts with an explicit START TRANSACTION statement, READ WRITE is the default access mode.
If the storage engine knows that the access mode is READ ONLY, it can perform some optimizations that improve concurrency.