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

This article mainly shows you the "InnoDB select query is a row lock or table lock", the content is easy to understand, clear, hope to help you solve the doubt, the following let the editor lead you to study and learn "InnoDB select query is a row lock or table lock" this article.

It was said before that InnoDB's select query locks tables, but some people don't believe it. However, some people have a strong learning ability and immediately found it on the official website. Select query will lock the table, depending on how you use it. This does not mean that all select queries will lock the table. It depends on your transaction isolation level and the form of the query statement you write.

The most common pit in MySQL is that InnoDB is a row lock, which is well known, but sometimes it locks the table, isn't it strange?

In fact, as long as you understand it, you won't feel strange at all. Only if you don't understand, will you think it's strange. The row lock of InnoDB is implemented on the index, not on the physical row record. The subtext is that if the access does not hit the index and cannot use a row lock, it will degenerate into a table lock. This is slightly different from Oracle's row lock implementation mechanism.

For example, the following table:

one

Xttblog (id, title, url, text) innodb

Id contention (primary key), no other indexes, that is, no indexes for other columns.

one

Update xttblog set text=' amateur grass' where id=1

Hit index, row lock.

one

Update xttblog set text=' amateur grass' where id! = 1

Missed index, table lock.

one

Update xttblog set text=' amateur grass' where title=' amateur grass'

No index, table lock.

Revelation: InnoDB must build a good index, otherwise the lock granularity is large, which will affect concurrency.

Speaking of select, if the query does not hit the index, it will also degenerate into a table lock. Let's combine the three locks of InnoDB (record lock (Record Locks), gap lock (Gap Locks), and key lock (Next-Key Locks) to illustrate it. The prerequisite for these three locks is that the default transaction isolation level is repeatable (Repeated Read, RR).

Record lock (Record Locks)

A record lock, which blocks index records, such as the following query statement:

one

Select * from xttblog where id=1 for update

It locks the index record of id=1 to prevent other transactions from inserting, updating, and deleting this row of id=1.

It is important to note that if it is the following query statement:

one

Select * from xttblog where id=1

Is a SnapShot Read, which is not locked.

Gap lock (Gap Locks)

A gap lock that blocks the interval in the index record, or the range before the first index record, or after the last index record. For example, the following SQL statement:

one

Select * from xttblog where id between 8 and 15 for update

Records with id between 8 and 15 are blocked. To prevent other transactions, such as id=10, from inserting records.

If the record insertion of id=10 is not blocked, phantom reading will occur. If the insert is successful and the same transaction executes the same SQL statement, you will find that there is one more record in the result set, that is, the phantom read data.

The main purpose of the gap lock is to prevent other transactions from inserting data into the interval, resulting in "unrepeatable readings".

If you downgrade the isolation level of a transaction to read commit (Read Committed, RC), the gap lock will automatically fail.

Key lock (Next-Key Locks)

Key lock is a combination of record lock and gap lock. Its blocking range includes both index record and index interval. The key lock blocks the index record itself and the interval before the index record.

If one session has a shared / exclusive lock on the index record Record, other sessions cannot immediately insert a new index record in the interval before Record. The main purpose of the key lock is also to avoid Phantom Read. If the isolation level of the transaction is downgraded to RC, the critical lock will also fail.

Finally, how do you test whether the current query is a row lock or a table lock?

In the case of our previous accident, the first is that the select query cannot have an index. Then the dev environment and the sit environment connect to the same database, dev takes the query breakpoint in a transaction and stops it on the query operation, while the sit environment inserts, updates, and deletes the same table. If you look at the log, you will find that there is a time out log. Specifically, the transaction cannot be committed and the timeout ends, because the table is locked and the timeout occurs if the lock is not acquired.

Summary: the lock of InnoDB is related to index type and isolation level of transaction. Whether InnoDB is a row lock or a table lock depends on your SQL statement. If the query does not hit the index, it will also degenerate into a table lock. The row lock of InnoDB is implemented on the index, not on the physical row record. So if the access does not hit the index and cannot use the row lock, it will degenerate into a table lock.

The above is all the content of the article "whether the select query in InnoDB is a row lock or a table lock". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.