Shulou(Shulou.com)06/01 Report--

What is the lock and transaction isolation level in MySQL? Many people may not know much about it. In order to let everyone know more, Xiaobian summarized the following contents for everyone. I hope everyone can gain something according to this article.

lock

The locking mechanism of a database is a key feature that distinguishes it from file systems. Used to manage concurrent access to shared resources. InnoDB uses locking mechanisms in many places, such as data tables in the operation buffer pool, LRU page lists, and data rows. In order to ensure consistency and integrity, locking mechanisms are required.

The locking mechanism is designed and implemented differently for different databases:

● MyISAM engine: table lock design, concurrent read without problems, concurrent write performance is poor.

● Microsoft SQL Server: Supports optimistic concurrency and pessimistic concurrency. Row-level locks are supported under optimistic concurrency. The cost of maintaining locks is high. When the number of row locks exceeds the threshold, they will be upgraded to table locks.

● InnoDB engine: supports row locking and provides consistent unlocked reads. There is no overhead for row locking and no performance degradation.

Oracle: Very similar to the InnoDB engine.

Two types of locks: lock and latch

Both lock and latch can be called locks in databases, but there is a big difference.

Latches are generally called latches, which are used to ensure that concurrent threads operate on critical resources correctly, and the object of action is a memory data structure. The locking time is required to be very short, and deadlocks will not be detected. In the InnoDB engine, it is divided into mutex (mutex) and rwlock (read-write lock).

lock is used to lock objects in the database, such as tables, pages, rows, the object is a transaction, released after commit/rollback, will detect deadlock. Divided into row lock, table lock, intention lock.

The lock below refers to the lock class lock.

Four types of locks

InnoDB supports four types of locks:

Shared lock (S Lock): allows transactions to read one line of data

Exclusive lock (X Lock): Allows transactions to delete or update a row of data

Intention S Lock: A transaction wants to acquire a shared lock on certain rows of a table.

Intention X Lock: A transaction wants an exclusive lock on a few rows in a table.

When transaction T1 acquires the shared lock on row r, since reading does not change the row data, transaction T2 can also acquire the shared lock on row r directly, which is called lock compatible.

When transaction T3 wants to acquire the exclusive lock of row r to modify the data, it needs to wait for T1/T2 to release the row-sharing lock, which is called lock incompatibility.

S locks and X locks are row locks, while IS locks and IX locks are intention locks, belonging to table locks. Intentional locks are designed to reveal the type of lock that the next row will be requested in a transaction, i.e., lock at a finer granularity of table locks. Because InnoDB supports table locks, intent locks do not block any requests except full table scans.

Compatibility of locks:

ISIXSXIS compatible compatible incompatible incompatible IX compatible incompatible S incompatible incompatible X incompatible incompatible

Three tables for storing transaction and lock information

We can view information about the current lock request in the transaction section with the show engine innodb status command.

Since InnoDB 1.0, INNODB_TRX (transaction table), INNODB_LOCKS (lock table), INNODB_LOCK_WAITS (lock waiting table) have been added under the INFORMATION_SCHEMA schema. Through these three tables, we can monitor the current transaction in real time and analyze possible table problems.

The three tables are defined as:

INNODB_TRXtrx_idThe unique transaction ID inside the InnoDB storage engine trx_state The state of the current transaction trx_started The start time of the transaction trx_requested_lock_id The lock waiting for the transaction IDC, NULLtrx_wait_started When the state is not LOCK WAIT The time to wait for the start of the transaction trx_weight The weight of the transaction, reflecting the number of rows modified and locked by a transaction. When rollback is required, select the transaction with the smallest value to rollback trx_mysql_thread_idMySQL thread ID, show processlist displayed results trx_query SQL statement run by transaction INNODB_LOCKLock_id lock IDlock_trx_id transaction IDlock_mode lock_type lock type, table lock or row lock_table to lock_index lock_space lock object space idlock_page transaction lock page number, table lock is NULLlock_rectransaction lock row number, NULLlock_data transaction lock record primary key value, NULLINNODB_LOCK_WAITSrequesting_trx_id transaction ID requesting_lock_id ID blocking_trx_id transaction ID blocking_lock_id blocking lock ID

Through INNODB_TRX we can see all transactions, whether transactions are blocked, and what the lock ID of the block is.

After that, view all lock information via INNODB_LOCKS.

Later, INNODB_LOCK_WAITS allows you to view wait information for locks and blocking relationships.

Through these three tables, you can clearly view transactions and locks, and you can also query them jointly. In the following scenarios, we will show the contents of these three tables.

isolation level

First, let's talk about the four transaction isolation levels of databases:

● READ UNCOMMITTED (0): Browse access level, dirty read, non-repeatable read, phantom read

READ COMMITTED (1): cursor stability level, existence of non-repeatability, phantom reading

● REPEATABLE READ (2): There is an illusion read

SERIALIZABLE (3): isolation level, transaction security guaranteed, but fully serial, low performance

These four transaction isolation levels are specified SQL standards. The default isolation level of InnoDB is REAPEATABLE READ. However, when it is different from other databases, it uses the Next-Key-Lock algorithm at the same time, which can avoid the generation of phantom reads. Therefore, it can fully meet the isolation requirements of transactions, i.e., achieve SERIALIZABLE isolation level.

The lower the isolation level, the fewer locks a transaction requests or holds for a shorter time, so the default isolation level for most databases is READ COMMITTED. However, related analysis also indicates that the isolation level has almost the same performance overhead, so users do not have to adjust the isolation level to improve performance.

Commands to view and modify transaction isolation levels:

mysql> select @@session.tx_isolation;+------------------------+| @@session.tx_isolation |+------------------------+| REPEATABLE-READ |+------------------------+1 row in set (0.00 sec)mysql> set session transaction isolation level SERIALIZABLE;Query OK, 0 rows affected (0.00 sec)

In the example, the transaction isolation level of this session is modified. If you need to modify the global parameter, you can replace session with global. If you want to permanently modify, you need to modify the configuration file:

[mysqld]transaction-isolation = READ-COMMITED

At the transaction isolation level of SERIALIZABLE, InnoDB automatically adds LOCK IN SHARE MODE to each SELECT statement to impose a shared lock on reads, so consistent unlocked reads are no longer supported.

Since InnoDB can reach SERIALIZABLE at REPEATABLE READ isolation level, it is generally not necessary to use the highest isolation level.

Consistent unlocked reads and multiversion concurrency control

Consistent unlocking reads refer to InnoDB reading rows in the current execution time database using Multi Version Concurrency Control (MVCC).

That is, if the row being read is undergoing a change operation, the read does not wait for the row lock to be released, but instead reads a snapshot of the row. A snapshot refers to a historical piece of data for that row, done through an undo operation. This greatly improves database concurrency, which is also the default setting for InnoDB.

A snapshot is a historical version of the current row, but there may be multiple versions, and there may be multiple snapshots of row data. This technique is called row multiversion technology, and the resulting concurrency control is called multiversion concurrency control (MVCC). InnoDB uses non-locking consistent reads at the READ COMMITTED and REPEATABLE READ isolation levels, but uses different definitions of quickfind data at these two isolation levels:

READ COMMITTED: Always read the latest snapshot

REPEATABLE READ: Always read the row data version at the beginning of the transaction

Let's implement an example:

Consistency unlocked read time Session A Session B1BEGIN

2select * from z where a = 3;

3

BEGIN4

update z set b=2 where a=3;5select * from z where a = 3;

6

COMMIT;7select * from z where a = 3;

8COMMIT;

In this example, we can clearly see the difference between 0, 1 and 2 isolation levels:

#Before the transaction starts, we can adjust the isolation levels to 0, 1, and 2 respectively to view different outputs mysql> set session transaction isolation level READ UNCOMMITTED;Query OK, 0 rows affected (0.00 sec)mysql> select @@tx_isolation;+------------------| @@tx_isolation |+------------------+| READ-UNCOMMITTED |+-------------------+1 row in set (0.00 sec)# A session: T1 business mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from z where a = 3;+--+----+| a | b |+---+------+| 3 | 1 |+---+------+1 row in set (0.00 sec)# B Session: T2 Transaction mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update z set b=2 where a=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0# A Session: T1 transaction, if isolation level is READ-UNCOMMITTED at this time, because transaction 2 may roll back at this time, so dirty read mysql> select * from z where a=3;+--+---+| a | b |+---+------+| 3 | 2 |+---+----+1 row in set (0.00 sec)# A Session: T1 transaction if isolation level is higher than READ-UNCOMMITTED mysql> select * from z where a=3;+--+----+| a | b |+---+------+| 3 | 1 |+---+------+1 row in set (0.00 sec)# B Session: T2 Transaction mysql> commit;Query OK, 0 rows affected (0.00 sec)# A session: T1 transaction, if the isolation level is READ-COMMITTED at this time, because the data is inconsistent with what is read at the beginning of the transaction, it is called non-repeatable read, and the results of other transactions can be read, violating the isolation of transactions mysql> select * from z where a=3;+--+----+| a | b |+---+------+| 3 | 2 |+---+----+1 row in set (0.00 sec)# A Session: T1 transaction if isolation level is higher than READ-COMMITTED mysql> select * from z where a=3;+--+----+| a | b |+---+------+| 3 | 1 |+---+----+1 row in set (0.00 sec)# A Session: T1 Transaction mysql> commit;Query OK, 0 rows affected (0.00 sec)

Consistency lock read and SERIALIZABLE isolation

At the default REPEATABLE READ isolation level, InnoDB uses consistent unlocked reads. But sometimes we also need to explicitly specify the use of consistent locking reads to ensure that the data is locked consistently during read operations. This requires the database to support locking read-lock statements:

● select ... for update: X-lock row records while reading

● select ... lock in share mode: add an S lock to the row record when reading

Both locks must be in a transaction, and the lock is released when the transaction commits, so be sure to add BEGIN, START TRANSACTION or SET AUTOCOMMIT=0.

As we said earlier with isolation levels, SERIALIZABLE automatically adds a LOCK IN SHARE MODE instruction to reads to add a shared lock, so consistent unlocked reads are no longer supported. This is also a feature of isolation level 3.

summary

Because the concept of lock is very important, here first talked about the concept of lock, lock type, lock information view, transaction isolation level and difference, later we will continue to talk about lock algorithm, lock three problems and magic read, deadlock and lock escalation.

After reading the above, do you have any further understanding of locks and transaction isolation levels in MySQL? If you want to know more about it, please pay attention to the industry information channel. Thank you for reading it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.