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

This article mainly explains "the transaction characteristics and isolation level of Mysql InnoDB". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "Mysql InnoDB transaction characteristics and isolation level details" it!

Mysql-innodb- transaction feature ACID

1. Atomicity: each transaction is an indivisible execution unit that contains a set of database operations that either succeed or fail to roll back.

two。 Consistency: consistency is reflected in the completion of the transaction, and all data states are consistent.

3. Isolation: the definition of the visibility isolation level of data during transaction and transaction execution, which controls the data visibility of reading and writing according to different degrees of isolation. Generally speaking, things and things are isolated from each other and independent of each other. The different isolation levels are described in detail below.

4. Durability: after the transaction is executed, the state of the data will be saved permanently.

Preface

Before we speak, let's take a look at the concept of dirty reading, non-repeatable, repeatable, and phantom reading:

1. Dirty reading: that is, transactions can read transactions that are not committed by other transactions, but this generally cannot happen in practical application development. Reading unreliable data leads to abnormal data, but it is believed to be valuable. The advantage of dirty reading is that it will not lock the table or record rows when reading, that is, it will not block waiting when reading and writing, so it is suitable for some data operations that update frequently. And there will be no cross-queries between transactions in the scenario.

two。 Non-repeatable reading: that is, the result of reading a record multiple times in a transaction is inconsistent with the original read result. If there is no exclusive lock on the read operation in a transaction, when the same select statement is executed multiple times, the hit data row may have been modified by other transactions, and other results are read, which is non-repeatable.

3. Repeatable: the result of reading a record multiple times in a transaction is consistent with the original read result. So this is the default isolation level for mysql-innodb, also because the relevant row records are locked at the beginning of the transaction.

4. Phantom reading: it means that different results will occur when the same select statement is executed twice, and the second read will add a row of data, such as transaction A updates the data, transaction B updates the result and then the SELECT in the same transaction can see records that do not exist before the completion of the B transaction operation. At this time, unlike repeatable reads, there are new or deleted lines.

Isolation level of the transaction

Here we discuss the transaction isolation level of the mysql database Innodb, from low to high.

1. Read uncommitted (Read UnCommitted) referred to as RU: other transactions can read uncommitted transactions, this level is the lowest level of isolation, there will be dirty reading.

two。 Read committed (Read Committed) referred to as RC: only committed transactions can be read to prevent dirty reading, but there will be non-repeatable reading, that is, different results may occur for the same data query in the same transaction. It can also cause hallucinations.

3. Repeatable Read abbreviated as RR: is the default isolation level for the Mysql-innodb engine. The result of the repeated query for the same piece of data in the same transaction is consistent with the result of the initial query, that is, it can be read repeatedly. Although this isolation level eliminates non-repeatable reads, phantom reads still exist in normal database engines, but innoDB solves phantom reads.

InnoDB uses Next-key Lock to solve "phantom reading". The default transaction isolation level of InnoDB is REPEATABLE READ, under which InnoDB uses Next-key Lock to lock the relevant index records and the "gaps" before the records to ensure that transactions in other session not only cannot update records but also cannot insert data into them, thus avoiding the problem of "phantom reading". InnoDB implements access control through Record Lock, Gap Lock and Next-Key Lock locking mechanisms, which will not be discussed too much today. The next article will discuss it in detail.

4. Serializable: this is the highest isolation level, where every transaction is serialized, table-level shared locks are acquired for each read, and reads and writes block each other.

Isolation level summary isolation level dirty read non-repeatable phantom read uncommitted read (Read uncommitted) possible committed read (Read committed) impossible uncommitted read (Read uncommitted) is not possible (mysql-innodb will not) uncommitted read (Read uncommitted) impossible mysql lock knowledge extension

The above talked about a lot of lock-related concepts, the following is a simple quick understanding of the concept, do not do a specific detailed analysis. Mysql locking mechanism is divided into table-level lock, row-level lock and page-level lock. Row locks can be divided into shared locks and exclusive locks.

1. Table-level lock: it locks the whole table with low overhead, fast locking speed, large lock granularity, many lock resources, high conflict probability, prone to deadlock and very low concurrency.

two。 Row-level lock: it records rows with high positioning overhead, slow locking speed, small granularity, small locking resources, low collision probability and relatively high concurrency.

3. Page lock: between the table and the row, lock the adjacent set of records, and the concurrency is general.

Row-level lock subdivision-shared lock & exclusive lock

Row-level lock subdivision-shared lock & exclusive lock

1. Shared lock is also known as read lock, referred to as S lock, as the name implies, shared lock means that multiple transactions can share a lock for the same data, all can access the data, but can only be read and cannot be modified.

two。 Exclusive lock is also known as write lock, referred to as X lock, as the name implies, exclusive lock can not coexist with others, it is exclusive. If a transaction acquires an exclusive lock on a data row, other transactions can no longer acquire other locks on the row, including shared and exclusive locks, but the transaction that acquires the exclusive lock can read and modify the data.

Note: exclusive locks are mentioned here: the default isolation level of the mysql InnoDB engine is to modify data statements, and update,delete,insert will automatically add exclusive locks to the data involved. The select statement does not add any lock type by default. If you want to add an exclusive lock, you can use select. For update statement, shared lock can use select. Lock in share mode . Therefore, data rows with exclusive locks cannot be modified in other transactions, nor can they be queried through for update and lock in share mode locks, but directly through select. From... Query data because a normal query does not have any locking mechanism.

Optimistic lock and pessimistic lock

Some people ask what are optimistic locks and pessimistic locks. Here, I also say that optimistic locks and pessimistic locks are not specific to the database. Any locking mechanism will be related to concepts. Do not be confused with row lock table locks and shared exclusive locks. Pessimistic and optimistic locks are actually a kind of concurrency control. Let's talk about the implementation of optimistic and pessimistic locks in the database.

1. Optimistic lock: use database version number or timestamp to implement, concurrent access, when the two operations are the same version number, it can be executed. The optimistic lock assumes that the data will not cause conflicts in general, so when the data is submitted for update, the conflict of the data will be formally detected. If a conflict is found, the wrong information of the user will be returned and the user will decide what to do.

two。 Pessimistic lock: it is the way to acquire the lock first and then operate. If the lock is not acquired, an exception is thrown or the lock is released. Access control is also carried out in the database through row locks, table locks and other mechanisms.

At this point, I believe you have a deeper understanding of "Mysql InnoDB's transaction characteristics and isolation level details". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.