Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Analysis of InnoDB Lock Mechanism in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A preface

This article introduces two other locks, Insert Intention Locks and AUTO-INC Locks.

Two common lock types

2.1 according to the time granularity of lock holding, it is divided into

1. Memory level: similar to mutex, released soon

two。 Statement level: statement ends, release

3. Transaction level: transaction commit or rollback before release

4. Session level: session level, disconnected before release

2.2 AUTO-INC lock

AUTO-INC lock is a special table-level lock. When a transaction inserts data into a table with self-incrementing fields, the transaction acquires an AUTO-INC lock, and other transactions must wait until the end of the insert statement that has acquired the lock. Therefore, multiple concurrent transactions cannot acquire the AUTO-INC lock on the same table at the same time, and holding AUTO-INC locks for too long may affect database performance (such as INSERT INTO T1. SELECT... Statements such as FROM T2) or deadlocks.

In view of the characteristics of AUTO-INC lock, MySQL 5.1.22 controls the algorithm of self-increasing sequence by adding the parameter innodb_autoinc_lock_mode. This parameter can be set to 0 _ 1 _ 1 _ 2.

Before learning innodb_autoinc_lock_mode, let's look at the types of insert statements.

1 Simple inserts

Insert statements that can determine the number of lines in advance, such as insert into tab values ()... (); replace, and so on. INSERT... With the exception of ON DUPLICATE KEY UPDATE and insert statements with subqueries.

2 Bulk inserts

As opposed to Simple inserts, it is impossible to determine the number of rows inserted in the insert/replace statement, insert. Select; replace... select; load data into table. In this case, Innodb assigns a separate auto_increment value to each row when executing a specific row.

3 Mixed-mode inserts

In this case, in Simple inserts mode, some insert specifies the specific value of the self-increment field, while others do not. For example:

INSERT INTO T1 (C1 and c2) VALUES (1 NULL,'b'), (NULL,'d'), (5)

INSERT... ON DUPLICATE KEY UPDATE

Next, let's take a look at MySQL's optimization mode for auto_increment.

Innodb_autoinc_lock_mode=0 is the traditional way. InnoDB adds an AUTO_INC lock to the table before allocation and releases it at the end of the SQL. This mode ensures that the standby library executes something like INSERT in STATEMENT replication mode. The consistency of statements such as SELECT, because it is impossible to determine how many records there are when such statements are executed, and only when other sessions are not allowed to allocate self-increment during execution can the consistency of master and standby be ensured.

Obviously, this locking mode greatly affects the performance of concurrent inserts, but it ensures the continuity of self-increment allocation within a SQL.

Innodb_autoinc_lock_mode=1, which is the default value for InnoDB. In this mode, an autoinc_mutex lock is added to the Simple inserts,InnoDB, and then to determine whether there are other threads on the table with the LOCK_AUTO_INC lock, and if so, release the autoinc_mutex, and use the traditional lock mode. Otherwise, after reserving the self-increment required for this insertion, the autoinc_mutex will be released quickly. Obviously, for normal concurrent INSERT operations, there is no need for LOCK_AUTO_INC locks. So this model improves the concurrency of the system.

Innodb_autoinc_lock_mode=2, which only adds a mutex to the allocation, will be released quickly, and will not degenerate to the traditional mode in some scenarios like a value of 1. Therefore, a setting of 2 does not guarantee the replication security of bulk inserts.

2.3 Insert Intention Locks

Inserting an intent lock is a type of gap lock, which is only for insert. When multiple insert of concurrent transactions are inserted into the same GAP, the sessions will not wait for each other if they are not inserting the same row of records. For example, if the index record deletes two records, and the two sessions insert the record 13 ~ 15 at the same time, they will add a GAP lock to (12 ~ 17), but they do not conflict with each other (because the inserted records do not conflict).

When a record is inserted into a data page, the function lock_rec_insert_check_and_lock is always called to check whether there is a lock object on the next record at the current insertion location (except for the data insertion when the index is built). The next record here is not the physical continuity, but the next record in logical order.

If there is no lock object on the next record: if the record is on the secondary index, update the maximum transaction ID on the secondary index page first and return the success directly to the ID; of the current transaction.

If there is a lock object on the next record, you need to determine whether the lock object locks the GAP. If the GAP is locked and it is determined that the intended GAP lock conflicts with the insertion, the current operation needs to wait. The added lock type is LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, and enters the waiting state. However, insertion intention locks are not mutually exclusive. This means that there may be multiple sessions in the same GAP that apply to insert an intention lock.

This is the end of the content of "InnoDB Lock Mechanism Analysis in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.