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

This article introduces the relevant knowledge of "MYSQL discussion on lock types and patterns". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

There are many kinds of locks in a database. Generally speaking, locks are mentioned, table locks, row locks, and deadlocks. But in fact, even MYSQL lock types are not so simple.

In fact, discussing a lock requires consideration from the following aspects:

1 Types of locks Table locks Row locks

2 locked mode

LOCK_IS LOCK_IX LOCK_S LOCK_X LOCK_AUTO_INC

3 Types of locks

4 lock granularity

5 Isolation level of lock

NEXT KEY LOCK LOCK_GAP LOCK_REC_NOT_GAP

LOCK_INSERT_INTENTION

After knowing these things, you can better understand locks and the various deadlocks or locktimeouts that can occur.

The following diagram shows the types of locks provided in MYSQL. From the diagram, it can be seen that IS intent locks can coexist with other lock types except X locks, X locks are mutually exclusive with any lock, and they are the same as themselves. AI locks only coexist with intent locks.

AUTO_INC locks, also known as auto_INCREMENT locks (commonly abbreviated as AI locks), are a special type of table lock that may be encountered when the inserted table has auto_INCREMENT columns. When there are self-increment columns in the insert table, the database needs to automatically generate self-increment values. Before generation, it will add AUTO_INC table locks to the table, and insert operations of other transactions will be blocked. This ensures that the generated self-increment values are definitely unique.

AUTO_INC locks are incompatible with each other, and only one self-increasing lock can exist at a time for the same table.

Self-locking does not follow the two-phase locking protocol. It is not released when the transaction is over. It is released when the INSERT statement is executed. It is used to improve the performance of concurrent insertion.

Self-increment is +1 once allocated and will not be reduced if the transaction rolls back, so self-increment may break.

And we're familiar with the line lock.

LOCK_REC_NOT_GAP, record lock itself is not so complex, it only locks the record where it is located, and the related lock is only locked on the index. If it is a primary key, it is directly locked at the primary key position. If it is a secondary index, it is locked on the secondary index. At the same time, it needs to be locked on the primary key specified by the secondary index.

NEXT KEY LOCK next key lock, as the name implies, is not ( ] [ ) ,(a collection of concepts), its main role is to prevent magic reading, that is, two inconsistent reads, so LOCK_GAP is mainly to see where

The isolation levels are R R and RC. The default isolation level for MYSQL is RR, but it is generally strongly recommended that the isolation level for MYSQL be RC. If our isolation level is RC, there is no next key lock. NEXT KEY LOCK locks the records surrounding the locked record.

Example: If the data records in our data table are 1 6 7 8 9 10

select * from t where number = 6 for update

Records locked at this time

(1 6 7), in this case, if you insert data between 1 and 6, it will not be inserted.

Gap lock, gap lock is more famous than NEXT KEY LOCK

(), identify the gap lock, gap lock can also be understood as a range lock, he will prevent other transactions from inserting or modifying records in this range, to ensure that two reads of records in this range will not change, so that there will be no magic reading phenomenon. Adding a gap lock does not conflict with a gap lock, but adding a gap lock severely affects database concurrency. In the example above, he wants to lock 1 (23456) 7, and different transactions can hold conflicting locks on the gap. For example, transaction A may hold a shared gap lock (gap S-lock) on a gap, while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, gap locks held in records by different transactions must be merged.

LOCK_INSERT_INTENTION Insert intent lock, mainly for insert service, when the database is inserted, it will diagnose whether there is a gap lock in the position of the inserted data, that is, it is mutually exclusive with the lock such as gap lock next key lock.

Record lock and record lock conflict, Next-key lock and Next-key lock conflict, Record lock and Next-key lock conflict;

For example, we now have the following table

We simulate two sessions.

1 select * from insert_lock where 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.