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

The following brings you about MySQL database transaction isolation and MVCC content, I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I'm sure you'll get something after reading the transaction isolation and MVCC of the MySQL database.

1. What is a transaction?

Transactions must follow the ACID principles established by ISO/IEC. ACID is an abbreviation for atomicity, consistency, isolation, and durability. These four states mean:

1. Atomicity (Atomicity)

Atomicity means that all operations contained in a transaction either succeed or fail to roll back, which is the same concept as the function of the transaction described in the previous two blogs, so if the operation of the transaction is successful, it must be fully applied to the database. If the operation fails, it cannot have any impact on the database.

two。 Consistency (Consistency)

Consistency means that the transaction must change the database from one consistency state to another.

3. Isolation (Isolation)

Changes to the data made by a transaction are not allowed to be provided to any other transaction until the transaction is committed correctly, that is, its possible results should not be displayed to other transactions until the transaction is committed correctly.

4. Persistence (Durability)

Persistence means that once a transaction is committed, the change to the data in the database is permanent, and the operation of committing the transaction will not be lost even if the database system encounters a failure.

two。 The role of the transaction

When multiple threads open the data in the transaction operation database, the database system should be able to isolate the operation to ensure the accuracy of each thread to obtain the data.

3. Concurrency problems encountered

1. The first type of missing update: when transaction An is undone, the update data of committed transaction B is overwritten.

two。 The second kind of missing update: a transaction overwrites the committed data of B transaction, resulting in the loss of B transaction operation.

3. Dirty reading: transaction A reads uncommitted data in transaction B.

4. Unrepeatable: the value read by A transaction is different because it is modified and committed by B transaction.

5. Illusion: between two reads of transaction A, transaction B inserts data.

4. How to solve the above problems?

To solve the above problems, developers have designed the following four transaction isolation levels for the MySQL database:

1.Read Uncommitted (uncommitted reads): allow dirty reads, that is, data modified by uncommitted transactions in other sessions may be read.

2.Read Committed (commit read): only data that has been submitted can be read. Most databases, such as Oracle, default to this level (do not repeat reads).

3.Repeated Read (repeatable): repeatable. All queries within the same transaction are consistent at the beginning of the transaction, the default level of InnoDB. In the SQL standard, this isolation level eliminates unrepeatable reads, but there are phantom reads, but innoDB solves phantom reads.

4.Serializable (Serial read): fully serialized reads, each read requires a table-level shared lock, read and write will block each other.

Isolation level dirty read unrepeatable Read Uncommitted (uncommitted read) possible Read Committed (committed read) impossible Repeated Read (repeatable read) possible Serializable (serial read) impossible 5. A small attempt

1. View the transaction isolation level for a global or session

SELECT @ @ global.tx_isolation, @ @ tx_isolation

two。 Modify the transaction isolation level of a global or session

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE] 6. The default Repeated Read isolation level of MySQL does not solve the problem of phantom reading.

The following will first introduce the locks involved in the database.

7. The basic description of the lock

1. Brief introduction of lock

A lock in a database is a software mechanism used to control and prevent a user (process session) from taking up a certain data resource. Other users make means that affect their own data operations or lead to data incompleteness and inconsistency.

two。 Level of lock

According to the lock level, locks can be divided into shared locks and exclusive locks.

Shared lock (read lock)

For the same piece of data, multiple reads can be performed at the same time without affecting each other. Shared locks are only locked for UPDATE, and other transactions can only get the latest records but not UPDATE operations until the UPDATE operation is committed.

Exclusive lock (write lock)

Block other write and read locks before the current write operation is completed.

3. Lock granularity

According to the granularity of locks, locks can be divided into table-level locks, row-level locks, and page-level locks.

Row level lock

High cost, slow locking, deadlock will occur, locking strength is the least, the probability of lock conflict is the lowest, and the degree of concurrency is high.

Table level lock

The utility model has the advantages of low overhead, fast locking, no deadlock, strong locking, high probability of conflict and low concurrency.

Page lock

The cost and locking time are between table lock and row lock, deadlock occurs, locking strength is between table and row level lock, and the concurrency is average.

8. Pessimistic lock and optimistic lock 8.1 pessimistic lock

1. The basic idea: always assume the worst-case scenario, thinking that someone else will modify it every time you go to get the data, so it will be locked every time you get the data. so that other people want to take the data will block until it gets the lock (shared resources are only used by one thread at a time, other threads block, and then transfer resources to other threads after use). Many of these locking mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks and so on. So whether the conflict actually occurs or not, the locking mechanism is used.

two。 Pessimistic lock function:

Lock the read records to prevent other transactions from reading and updating these records. Other transactions will block until the transaction ends. Pessimistic lock is the exclusive use of resources on the basis of the transaction isolation function of the database, so as to ensure the consistency of reading data and avoid the loss of modifications. Pessimistic locks can use Repeatable Read transactions, which fully meet the requirements of pessimistic locks. 8.2 optimistic lock

1. Basic idea: always assume the best situation, every time you go to get the data, you think that others will not modify it, so you will not lock it, but when you update it, you will judge whether others have updated the data during this period. You can use the version number mechanism and CAS algorithm to achieve this. Optimistic locks are suitable for multi-read applications, which can improve throughput.

two。 Explanation: optimistic locking is an idea, optimistic locking will not lock anything, that is, it does not rely on the transaction mechanism of the database, optimistic locking is entirely at the application system level. So it is not a locking mechanism. If an optimistic lock is used, then the database must add a version field, otherwise all fields can only be compared, but because floating-point types cannot be compared, it is actually not feasible to have a version field

8.3 version number mechanism

Generally, a data version number version field is added to the data table, indicating the number of times the data has been modified. When the data is modified, the version value will be increased by one. When thread A wants to update the data value, it will also read the version value while reading the data. When submitting the update, if the version value just read is equal to the version value in the current database, otherwise, the update operation will be retried until the update is successful.

8.4 CAS algorithm

1. Core idea: Compare and Swap, that is, compare and exchange.

two。 Process: suppose there is a thread A ready to modify the value of the variable named name in memory, so thread A will compare the value of the name variable read by itself with the value of name now. If the same, it means that the value of the variable has not been modified, so it can be updated and modified, otherwise the update fails.

9. Go back to the Repeated Read transaction isolation level of MySQL

As mentioned earlier, MySQL implements a repeatable transaction isolation level by default, but it does not solve the problem of phantom reading. however, under the condition of MySQL database using repeatable transaction isolation, phantom reading does not occur. MySQL is controlled by MVCC (Multi-version concurrency Control).

9.1 brief analysis of nouns:

1.MVCC: short for multiversion concurrency control, that is, multi-version concurrency control, is a very basic concept. The role of MVCC is to make transactions occur in parallel, under the premise of a certain isolation level, can ensure that consistent reading can be achieved in a transaction, that is, the data read according to a certain condition when the transaction starts, until the end of the transaction, the same condition is executed again, or the same data is read, without change (data modified by other parallel transactions will not be seen).

The meaning of the internal snapshot used by 2.read view:InnoDB MVCC. At different isolation levels, the version of the data snapshot you see when the transaction starts (and in some cases, at the beginning of the SQL statement) may also be different. Read view is used under several isolation levels described above.

3. Snapshot read: the so-called access to information and data according to read view without adding any locks.

4. Current read: the previous read will get all the committed data. Logically, there is a new transaction for DML operation between the first current read and the second current read in a transaction. At this time, the results of the two current reads should be inconsistent, but the reality is that before the transaction of the current read is committed, all data modifications and inserts for the current read will be blocked. The main reason is that next-key lock solves the situation where phantom reading may occur in the current reading.

Next-key lock is downgraded to record lock (row lock) when using the primary key index for the current read

9.2 Read view detailed analysis

InnoDB supports MVCC multi-versioning, where READ COMMITTED and REPEATABLE READ isolation levels are supported by consistent read view (consistent read View). The so-called consistent read view is to snapshot the transaction system trx_sys at a certain time, write down the trx_sys state (including the active read and write transaction array), and then compare all the read operations according to its transaction ID (i.e. trx_id) with the state of trx_sys in snapshot, so as to judge the visibility of read view to the transaction.

The difference between the REPEATABLE READ isolation level (except for GAP locks) and the READ COMMITTED isolation level is the timing when the snapshot is created. The REPEATABLE READ isolation level creates the read view at the beginning of the transaction, exactly when the first read operation creates the read view, and the READ COMMITTED isolation level creates the read view at the beginning of the statement. This means that the SELECT operation of a transaction below the REPEATABLE READ isolation level will only get one read view, but a transaction under the READ COMMITTED isolation level can get multiple read view.

Creating / closing read view requires holding trx_sys- > mutex, which will degrade system performance. Version 5.7 optimizes this, and session will cache the read view of read-only transactions when the transaction commits.

9.3 read view determines whether the current version data item is visible or not

In InnoDB, when you create a new transaction, InnoDB creates a read view of the list of active transactions in the current system (trx_sys- > trx_list), which holds an id list of other transactions that the system should not currently see by this transaction. When the user wants to read the record of the row in this transaction, InnoDB compares the current version number of the row with the read view.

The specific algorithm is as follows:

Let the current transaction id of the row be trx_id,read view. The earliest transaction id in the row is trx_id_min and the latest transaction id is trx_id_max.

If trx_id

Trx_id_max, it indicates that the transaction in which the row record is located does not open until after the new transaction is created, so the current value of the row record is not visible.

If trx_id_min = view- > low_limit_id) {return (FALSE);} 3. When the transaction ID of the row record is in the active scope, it is determined whether it is in the active linked list, if it is, it is not visible, if not, it is visible. For (I = 0; I < n_ids IDs; iTunes +) {trx_id_t view_trx_id = read_view_get_nth_trx_id (view, n_ids-I-1); if (trx_id)

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.