MariaDB Transactions and Isolation Levels for SQL Server Users

This page explains how transactions work in MariaDB, and highlights the main differences between MariaDB and SQL Server transactions.

Note that XA transactions are handled in a completely different way and are not covered in this page. See XA Transactions.

Missing Features

These SQL Server features are not available in MariaDB:

• Autonomous transactions;

• Distributed transactions.

Transactions, Storage Engines and the Binary Log

In MariaDB, transactions are optionally implemented by . The default storage engine, , fully supports transactions. Other transactional storage engines include and . Most storage engines are not transactional, therefore they should not considered general purpose engines.

Most of the information in this page refers to generic MariaDB server behaviors or InnoDB. For and please check the proper KnowledgeBase sections.

Writing into a non-transactional table in a transaction can still be useful. The reason is that a is acquired on the table for the duration of the transaction, so that are queued.

It is possible to write into transactional and non-transactional tables within a single transaction. It is important to remember that non-transactional engines will have the following limitations:

• In case of rollback, changes to non-transactional engines won't be undone. We will receive a warning 1196 which reminds us of this.

• Data in transactional tables cannot be changed by other connections in the middle of a transaction, but data in non-transactional tables can.

• In case of a crash, committed data written into a transactional table can always be recovered, but this is not necessarily true for non-transactional tables.

If the is enabled, writing into different transactional storage engines in a single transaction, or writing into transactional and non-transactional engines inside the same transaction, implies some extra work for MariaDB. It needs to perform a two-phase commit to be sure that changes to different tables are logged in the correct order. This affects the performance.

Transaction Syntax

The first read or write to an InnoDB table starts a transaction. No data access is possible outside a transaction.

By default is on, which means that the transaction is committed automatically after each SQL statement. We can disable it, and manually commit transactions:

Whether autocommit is enabled or not, we can start transactions explicitly, and they will not be automatically committed:

BEGIN can also be used to start a transaction, but does not work in stored procedures.

Read-only transactions are also available using START TRANSACTION READ ONLY. This is a small performance optimization. MariaDB will issue an error when trying to write data in the middle of a read-only transaction.

Only DML statements are transactional and can be rolled back. This may change in a future version, see - Atomic DDL and - transactional DDL.

Changing autocommit and explicitly starting a transaction will implicitly commit the active transaction, if any. DDL statements, and several other statements, implicitly commit the active transaction. See for the complete list of these statements.

A rollback can also be triggered implicitly, when certain errors occur.

You can experiment with transactions to check in which cases they implicitly commit or rollback. The system variable can help: it is set to 1 when a transaction is in progress, or 0 when no transaction is in progress.

This section only covers the basic syntax for transactions. Much more options are available. For more information, see .

Constraint Checking

MariaDB supports the following :

In some databases, constraints can temporarily be violated during a transaction, and their enforcement can be deferred to the commit time. SQL Server does not support this, and always validates data against constraints at the end of each statement.

MariaDB does something different: it always checks constraints after each row change. There are cases this policy makes some statements fail with an error, even if those statements would work on SQL Server.

For example, suppose you have an id column that is the primary key, and you need to increase its value for some reason:

The reason why this happens is that, as the first thing, MariaDB tries to change 1 to 2, but a value of 2 is already present in the primary key.

A solution is to use this non-standard syntax:

Changing the ids in reversed order won't duplicate any value.

Similar problems can happen with CHECK constraints and foreign keys. To solve them, we can use a different approach:

The last solutions temporarily disable CHECK constraints and foreign keys. Note that, while this may solve practical problems, it is dangerous because:

• This doesn't disable a single CHECK or foreign key, but also others, that you don't expect to violate.

• This doesn't defer the constraint checks, but it simply disables them for a while. This means that, if you insert some invalid values, they will not be detected.

See and system variables.

Isolation Levels and Locks

For more information about MariaDB isolation levels see .

Locking Reads

In MariaDB, the locks acquired by a read do not depend on the isolation level (with one exception noted below).

As a general rule:

• Plain are not locking, they acquire snapshots instead.

• To force a read to acquire a shared lock, use .

• To force a read to acquire an exclusive lock, use .

Changing the Isolation Level

The default, the isolation level in MariaDB is REPEATABLE READ. This can be changed with the system variable.

Applications developed for SQL Server and later ported to MariaDB may run with READ COMMITTED without problems. Using a stricter level would reduce scalability. To use READ COMMITTED by default, add the following line to the MariaDB configuration file:

It is also possible to change the default isolation level for the current session:

Or just for one transaction, by issuing the following statement before starting a transaction:

How Isolation Levels are Implemented in MariaDB

MariaDB supports the following isolation levels:

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

MariaDB isolation levels differ from SQL Server in the following ways:

• REPEATABLE READ does not acquire share locks on all read rows, nor a range lock on the missing values that match a WHERE clause.

• It is not possible to change the isolation level in the middle of a transaction.

• SNAPSHOT isolation level is not supported. Instead, you can use START TRANSACTION WITH CONSISTENT SNAPSHOT to acquire a snapshot at the beginning of the transaction. This is compatible with all isolation levels.

Here is an example of WITH CONSISTENT SNAPSHOT usage:

As you can see, session 1 uses WITH CONSISTENT SNAPSHOT, thus it sees all tables as they were when the transaction begun.

Avoiding Lock Waits

When we try to read or modify a row that is exclusive-locked by another transaction, our transaction is queued until that lock is released. There could be more queued transactions waiting to acquire the same lock, in which case we will wait even more.

There is a timeout for such waits, defined by the variable. If it is set to 0, statements that encounter a row lock will fail immediately. When the timeout is exceeded, MariaDB produces the following error:

It is important to note that this variable has two limitations (by design):

• It only affects transactional statements, not statements like ALTER TABLE or TRUNCATE TABLE.

• It only concerns row locks. It does not put a timeout on metadata locks, or table locks acquired - for example - with the statement.

Note however that can be used for metadata locks.

There is a special syntax that can be used with SELECT and some non-transactional statements including ALTER TABLE: the clauses. This syntax puts a timeout in seconds for all lock types, including row locks, table locks, and metadata locks. For example:

InnoDB Transactions

InnoDB Lock Types

InnoDB locks are classified based on what exactly they lock, and which operations they lock.

The first classification is the following:

• Record Locks lock a row or, more precisely, an index entry.

• Gap Locks lock an interval between two index entries. Note that indexes have virtual values of -Infinum and Infinum, so a gap lock can cover the gap before the first or after the last index entry.

• Next-Key Locks lock an index entry and the gap between it and the next entry. They're a combination of record locks and gap locks.

• Insert Intention Locks are gap locks acquired before inserting a new row.

Lock modes are the following:

• Exclusive Locks (X) are generally acquired on writes, e.g. immediately before deleting a row. Only one exclusive lock can be acquired on a resource simultaneously.

• Shared Locks (S) can be acquired on reads. Multiple shared locks can be acquired at the same time (because the rows are not supposed to change when shared-locked) but are incompatible with exclusive locks.

• Intention locks (IS, XS) are acquired when it is not possible to acquire an exclusive lock or a shared lock. When a lock on a row or gap is released, the oldest intention lock on that resource (if any) is converted to an X or S lock.

For more information see .

Information Schema

Querying the is the best way to see which transactions have acquired some locks and which transactions are waiting for some locks to be released.

In particular, check the following tables:

• : requests for locks not yet fulfilled, or that are blocking another transaction.

• : queued requests to acquire a lock.

• : information about all currently executing InnoDB transactions, including SQL queries that are running.

Here is an example of their usage.

Deadlocks

InnoDB detects deadlocks automatically. Since this consumes CPU time, some users prefer to disable this feature by setting the variable to 0. If this is done, locked transactions will wait until the they exceed the . Therefore it is important to set innodb_lock_wait_timeout to a very low value, like 1.

When InnoDB detects a deadlock, it kills the transaction that modified the least amount of data. The client will receive the following error:

The latest detected deadlock, and the killed transaction, can be viewed in the output of . Here's an example:

The latest detected deadlock never disappears from the output of SHOW ENGINE InnoDB STATUS. If you cannot see any, MariaDB hasn't detected any InnoDB deadlocks since the last restart.

Another way to monitor deadlocks is to set to 1 (0 is the default). InnoDB will log all detected deadlocks into the .

_{This page is licensed: CC BY-SA / Gnu FDL}