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

This article mainly introduces "the introduction of the relevant knowledge points of the mysql lock". In the daily operation, I believe that many people have doubts about the introduction of the relevant knowledge points of the mysql lock. The editor consulted all kinds of materials and sorted out a simple and easy-to-use operation method. I hope it will be helpful to answer the doubts of "introduction of the relevant knowledge points of the mysql lock". Next, please follow the editor to study!

1. Lock type of mysql

(1) shared / exclusive lock (Shared and Exclusive Locks)

Shared and exclusive locks are standard row-level locks implemented by the InnoDB engine.

The shared lock is taken to allow the current transaction to read a row of data.

The exclusive lock is taken to allow the current transaction to modify or delete a row of data.

Set shared lock: select * from user where id = 1 LOCK IN SHARE MODE

Set exclusive lock: select * from user where id = 1 FOR UPDATE

(2) intention lock (Intention Locks)

The purpose of the existence of intention locks is to enable row locks and table locks to coexist.

An intention lock is a table-level lock that indicates what type of lock (shared or exclusive lock) the transaction will later add to the rows of data in the table.

When a transaction adds an intention exclusive lock to a table, another transaction waits through the table's intention exclusive lock to know that there is already an exclusive operation on the table.

(3) record lock (Record Locks)

A record lock is a lock on an index record. For example, SELECT C1 FROM t WHERE C1 records 10 FOR UPDATE; will prevent other transactions from inserting, updating, deleting, and so on.

Record locks always lock indexed records. If a table does not have an index defined, the implicit "clustered index" is locked.

(4) clearance lock (Gap Locks)

A gap lock is a lock on the gap between index records.

A gap may span a single index value, multiple index values, or even empty.

For statements that use unique indexes to search for unique rows, only record locks are added without gap locks (this does not include combining unique indexes).

(5) key lock (Next-key Locks)

Next-Key Locks is a combination of row locks and gap locks. When InnoDB scans an index record, it first adds a record lock (Record Lock) to the selected index record, and then a gap lock (Gap Lock) to the gap between the two sides of the index record.

(6) insert intention lock (Insert Intention Locks)

The insert intention lock is the type of gap lock that is set by the insert operation before the data row is inserted.

If multiple transactions are inserted into the same index gap, and if they are not inserted at the same location in the gap, there is no need to wait for other transactions. For example, if two transactions are inserted between the index gaps of 4 and 7, 5 and 6, respectively, the two transactions will not cause collision blocking.

(7) self-increasing lock (Auto-inc Locks)

A self-incrementing lock is a special table-level lock acquired by inserting a transaction into a table with self-incrementing columns. If one transaction is inserting values into the table, then any other transaction must wait to ensure that the row inserted by the first transaction is continuous self-increment.

Second, the implementation of the lock

InnoDB row locking is achieved by locking the index, and if there is no index, InnoDB locks the record through a hidden clustering index (full table scan, that is, table lock).

However, for the sake of efficiency, MySQL has optimized that for records that do not meet the conditions, locks will be placed, and eventually the locks on records that meet the conditions will be held. However, locking / unlocking actions on records that do not meet the conditions will not be omitted. Therefore, when there is no index, the data rows that do not meet the conditions will have a time-consuming process of locking and locking.

Indexes are divided into primary key index and non-primary key index. If a sql statement operates on a primary key index, MySQL locks the corresponding primary key index; if a statement operates on a non-primary key index, MySQL locks the non-primary key index first, and then locks the corresponding primary key index.

Third, the application of mysql lock in four transaction isolation levels.

The four isolation levels of a transaction are:

Read unsubmitted (Read Uncommitted)

The select statement does not add any locks at this time. Concurrency is highest at this time, but dirty reads occur.

Read submit (Read Committed, RC)

Ordinary select statements are snapshot reads

Update statement, delete statement, display locked select statement (select... In share mode or select... For update), except for blocking intervals during foreign key constraint checks and duplicate key checks, only record locks are used in other cases

Readable (Repeated Read, RR)

Ordinary select statements are also snapshot reads.

Update statement, delete statement, display locked select statement (select... In share mode or select... For update) according to the situation:

If a unique query condition is used on a unique index, record locks are used. For example, select * from user where id = 1, where id establishes a unique index.

If the range query condition is used on the unique index, the gap lock and key lock are used. For example, select * from user where id > 20

Serialization (Serializable)

At this point all select statements are implicitly locked: select... In share mode.

IV. Snapshot reading, current reading

To understand the locking methods of the previous four isolation levels, it is necessary to understand MVCC, snapshot reading, and current reading.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read (current read).

Snapshot reading, read the visible version of the record (it may be the historical version), without locking.

The current read is the latest version of the record, and the record returned by the current read will be locked to ensure that other transactions will not modify the record concurrently.

What is multi-version concurrency control (MVCC:multi-version concurrency control)

1.MVCC definition: multi-version concurrency control system. It can be thought of as a variant of row-level locks, which can avoid locking operations in more cases.

two。 Function: avoid some locking operations and improve concurrency performance.

3. Implementation: by saving the creation time and expiration time or deletion time of each row of records (which are hidden), these two times are actually the version number of the system. Each time you start a new transaction, the version number is automatically incremented.

4. Concrete principle

Select:innoBD query checks the following two conditions: one is that the version number of the data row is earlier than the version number of the current transaction, and the other is the delete version number of the row, either none or greater than the version number of the current transaction.

4.2) insert/delete:innoDB uses the current system version number as the version number of the newly inserted (deleted) data row.

Update: insert a new row of data first and use the current system version number as the row version number and the current system version number as the deleted version number of the original row. When updating the primary key, both the clustered index and the normal index produce two versions, while when updating the non-primary key, as long as the normal index produces two versions.

Note: MVCC only works under the two isolation levels of read committed and repeatable read.

[reference: "High performance mysql"]

What are the snapshot readings?

A normal select... A sentence is a snapshot read.

Snapshot reading, which makes a normal select... at the RR (repeatable read) level Statements can also be read repeatedly. That is, the use of visible versions to ensure data consistency as mentioned in the previous MVCC.

What are the previous readings?

Insert statement, update statement, delete statement, display locked select statement (select... LOCK IN SHARE MODE, select... FOR UPDATE) is the current read.

Why do insert, update, and delete statements belong to the current read?

This is because when these statements are executed, they perform a process of reading the latest version of the current data.

Of the SQL statements currently read, InnoDB interacts with MySQL Server one by one. That is, after locking a record that meets the conditions, and then returning it to MySQL Server, when MySQL Server completes the DML operation, the next data is locked and processed.

5. Check row-level lock contention

Execute SQL:mysql > show status like 'InnoDB_row_lock%'

Mysql > show status like 'InnoDB_row_lock%' +-- +-+ | Variable_name | Value | +-+-+ | InnoDB_row_lock_current_waits | 0 | InnoDB_row_lock_time | | 0 | | InnoDB_row_lock_time_avg | 0 | | InnoDB_row_lock_time_max | 0 | | InnoDB_row_lock_waits | 0 | +-+-+ |

If you find that lock contention is serious, you can further observe the tables, data rows, etc., where lock conflicts occur by setting InnoDB Monitors, and analyze the causes of lock contention. Such as:

Set up the monitor: mysql > create table InnoDB_monitor (an INT) engine=InnoDB

View: mysql > show engine InnoDB status

Stop viewing: mysql > drop table InnoDB_monitor

Specific reference: InnoDB Monitor

VI. Deadlock

What is a deadlock: you wait for me to release the lock, I wait for you to release the lock will form a deadlock.

How to find deadlocks: in the transaction management and locking mechanism of InnoDB, there is a special mechanism to detect deadlocks, which will be detected shortly after the deadlocks are generated in the system.

Solution: roll back the smaller transaction

Under the REPEATABLE-READ isolation level, if two threads use SELECT for the same condition record at the same time. FOR UPDATE adds an exclusive lock, and in the absence of a record that meets the condition, both threads will lock successfully. The program finds that the record does not yet exist and attempts to insert a new record, and if both threads do so, a deadlock occurs. In this case, changing the isolation level to READ COMMITTED avoids the problem.

How to determine the size of a transaction: the amount of data that each transaction inserts, updates, or deletes

Note:

When more than InnoDB storage engine is involved in a deadlock scenario, InnoDB cannot detect the deadlock, so it can only be solved by locking the timeout parameter InnoDB_lock_wait_timeout.

VII. Optimize row-level locking

(1) if we want to make rational use of InnoDB row-level locking to enhance our strengths and circumvent our weaknesses, we must do the following:

A) allow all data retrieval to be done through the index as much as possible, so as to prevent InnoDB from upgrading to table-level locking because it cannot be locked by the index key

B) reasonably design the index to make InnoDB lock on the index key as accurately as possible and narrow the locking range as much as possible, so as not to cause unnecessary locking and affect the execution of other Query

C) minimize the filtering conditions for range-based data retrieval to avoid locking records that should not be locked because of the negative impact of gap locks

D) try to control the size of transactions and reduce the amount of locked resources and the length of locking time

E) use a lower level of transaction isolation as much as the business environment allows to reduce the additional costs incurred by MySQL in implementing the transaction isolation level.

(2) due to the row-level locking and transactionality of InnoDB, deadlocks are bound to occur. Here are some common suggestions to reduce the probability of deadlocks:

A) in similar business modules, access according to the same access order as far as possible to prevent deadlock

B) in the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock

C) for business parts that are very prone to deadlocks, you can try to use upgrade locking granularity to reduce the probability of deadlocks through table-level locking.

View lock information for SQL statements

Before you look at the lock information of a sql sentence, you need to do the following:

View the isolation level of the transaction:

View through the show global variables like "tx_isolation"; command.

You can change the isolation level to the desired isolation level by executing set session transaction isolation level repeatable read;. The isolation level values are as follows:

Read uncommitted 、 read committed 、 repeatable read 、 serializable

Ensure that the transaction is manually committed:

Through show global variables like "autocommit"; view.

If ON, submit manually by executing set session autocommit=0; instead.

Make sure the gap lock is open:

Through show global variables like "innodb_locks%"; view

OFF indicates that it is on. Default is OFF

At this point, the study of "introduction to the relevant knowledge points of mysql locks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.