This is the fourth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Set transaction isolation level for MySQL/MariaDB

The default isolation level of InnoDB storage engine is REPEATABLE READ — RR. In order to avoid magic reading, the Next Key Lock Lock algorithm (Record Lock row Record Lock and Gap Lock) will be used. Thus achieving complete isolation of transactions.

MySQL introduces gap locking to solve phantom reads at the repeatable read isolation level, but this also causes problems such as deadlocks to occur more frequently, reducing the concurrency of transaction execution and so on.

A gap lock and a row lock are called a next-key lock. Each next-key lock is a forward-opening and back-closing interval (the value of the lock range is forward-opening and back-closing, and the record of the value in this range is not allowed to be inserted or deleted).

In practice, most people choose the read commit (RC) isolation level and set binlog_format= ROW (to resolve possible data and log inconsistencies).

The specific usage depends on the actual service requirements. If the service does not need the guarantee of repeatable read, the lock range of the operation data is smaller and the concurrency is increased at the read commit level.

In the InnoDB storage engine, the SERIALIZABLE transaction isolation level is mainly used for distributed transactions. In distributed transactions, the isolation level must be SERIALIZABLE.

View the current isolation level

The lowest granularity that the isolation level of a transaction applies is the session level, so you can view or set the isolation level globally or for the current session.

Note: Some versions of the isolation level variable are transaction_ISOLATION.

  • The isolation level of the current sessionshow variables like 'tx_isolation';
> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.001 sec)
Copy the code
  • The isolation level of the current sessionselect @@tx_isolation;
> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.000 sec)
Copy the code

View the global isolation level

  • Global isolation levelselect @@global.tx_isolation;
> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.000 sec)
Copy the code

Sets the isolation level of the transaction

set [global | session] transaction isolation level [read uncommitted| read committed | repeatable read | serializable]
Copy the code

The session cannot be omitted. Otherwise, it cannot be set successfully.

Sets the isolation level of the current session

set @@session.tx_isolation= 'read-committed';
Copy the code

or

set session transaction isolation level serializable;
Copy the code

Set the global isolation level

set @@global.tx_isolation= 'read-committed';
Copy the code

or

set global transaction isolation level serializable;
Copy the code

The MySQL configuration file permanently sets the transaction isolation level

You can also permanently set the isolation level of a transaction in the MySQL configuration file. Add the following line under [mysqld] :

[mysqld]
transaction-isolation = READ-COMMITTED
Copy the code

The actual isolation level Settings used by MySQL

There are two common lock-based isolation level Settings in MySQL:

  • Gap+RR (default)
  • Row+RC (more common)

Gap+RR: the default setting for MySQL is to use the repeatable read (RR) with Gap lock.

RR+Gap can achieve serialization, but Gap lock will lead to more frequent problems such as deadlock, reduce the concurrency of transaction execution and so on.

Row+RC: The read commit (RC) isolation level is used. The binary log format is Row

Set binlog_format=row to ensure that data and logs are consistent. That is, binary log binlog format is ROW.

Regardless of the level of isolation used, you should set the binary log format to ROW. Because the ROW format records ROW changes, rather than simple SQL statements, you can avoid some data and log inconsistencies, master/slave synchronization, and so on.

MySQL5.1 and later versions support the row format of binlog_format.

Set and view binlog_format

== The binlog function is enabled only when the binlog function is enabled

set binlog_format = 'row';
Copy the code

Or in the configuration file

[mysqld]
binlog-format = ROW
Copy the code
  • Set and view binlog_format
> set binlog_format = 'row';
Query OK, 0 rows affected (0.005 sec)

> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.001 sec)
Copy the code

The isolation level of Oracle

The default isolation level for Oracle, PG, SQL Server, and others is read commit (RC). If you are migrating to MySQL from another database system, you also need to set the isolation level of MySQL to “read Commit” in order to keep the isolation level consistent.

The Oracle database does not support READ UNCOMMITTED and REPEATABLE READ isolation levels.