ISOLATION

Sintassi:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
  {
       READ UNCOMMITTED
     | READ COMMITTED
     | REPEATABLE READ
     | SERIALIZABLE
   }

Spiegazione:

Questa istruzione imposta il livello di isolamento delle transazioni a livello globale, per la sessione corrente o per la prossima transazione:

• Con la parola chiave GLOBAL, l'istruzione imposta il livello di isolamento predefinito per tutte le prossime sessioni. Le sessioni esistenti non sono influenzate.
• Con la parola chiave SESSION, l'istruzione imposta il livello di isolamento predefinito per tutte le transazioni che verranno eseguite all'interno della sessione corrente.
• Se non è specificata né SESSION né GLOBAL, l'istruzione imposta il livello di isolamento per la prossima transazione non ancora iniziata, eseguita all'interno della sessione corrente.

Per modificare il livello di isolamento globale predefinito occorre il permesso SUPER privilege. Tutte le sessioni sono libere di modificare il proprio livello di isolamento (anche durante una transazione), o quello della transazione successiva.

Per impostare il livello di isolamento predefinito all'avvio del server, si usa l'opzione --transaction-isolation=liv di mysqld nella riga di comando o in un file di configurazione. I valori di liv per questa opzione utilizzano i trattini anziché gli spazi, quindi i valori ammessi sono: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ e SERIALIZABLE. Per esempio, per impostare il livello di isolamento predefinito a REPEATABLE READ, si usano queste righe nella sezione [mysqld] di un file di configurazione:

[mysqld]
transaction-isolation = REPEATABLE-READ

Per determinare i livelli di isolamento globale e di sessione a runtime, si controlla il valore della variabile di sistema tx_isolation:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

InnoDB supporta tutti i livelli di isolamento descritti qui utilizzando diverse strategie di locking. Il livello predefinito è REPEATABLE READ. Per ulteriori informazioni sui lock a livello di record di InnoDB e su come li utilizza per eseguire i diversi tipi di istruzioni, si vedano http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html e http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html.

I prossimi paragrafi spiegano in che modo MariaDB supporta i diversi livelli di isolamento.

READ UNCOMMITTED

SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty read." Otherwise, this isolation level works like READ COMMITTED.

READ COMMITTED

A somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus allows the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because "phantom rows" must be blocked for MySQL replication and recovery to work.

Note: In MariaDB 5.1, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for non-matching rows are released after MySQL has evaluated the WHERE condition. As of MySQL 5.1, if you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (non-locking) read and need not block for other transactions. (This means that to force a plain SELECT to block if other transactions have modified the selected rows, you should disable autocommit.)