Mysql-innodb lock classification

Source: Internet
Author: User
Recordlock: Record lock, that is, only a single row of gap lock is locked. Only one interval is locked. (note that the interval here is an open interval, that is, the boundary value is not included. Next-keylock: recordlock + gaplock, so next-keylock is half-open and half-closed, and the lower bound is open and the upper bound is closed.

I. innodb row lock classification
Record lock: record lock, that is, only a single row is locked.
Gap lock: interval lock. Only one interval is locked. (note that the interval here is an open interval, that is, the boundary value is not included.
Next-key lock: record lock + gap lock. Therefore, next-key lock is half-open and semi-closed, and the lower bound and upper bound are closed.
Lock range of next-key: (negative infinity, minimum first record], (between records], (maximum record, positive infinity)

II. statement lock analysis

SELECT... FROM... for update sets an exclusive next-key lock FOR all index records encountered during read.
Insert into... VALUES (...) sets an exclusive lock for the inserted row. Note that this is not a next-key lock and does not prevent other users from inserting the gap before the inserted rows. If a duplicate key error occurs, set the share lock for duplicate index records.
· When you specify the AUTO_INCREMENT column before initialization for a table, InnoDB sets an exclusive lock at the end of the index associated with the AUTO_INCREMENT column. In the access AUTO-increment counter, InnoDB uses the dedicated table lock mode AUTO-INC, where the lock only continues until the end of the current SQL statement, rather than the end of the entire transaction. InnoDB retrieves the value of the previously initialized AUTO_INCREMENT column without setting any lock.
Insert into t select... from s where... sets an exclusive (non-next-key) lock for each row inserted to T. It regards search as a continuous read on S, but if MySQL binary log function is enabled, it sets a shared next-key lock on S.
Yes. InnoDB has to be locked in the latter case: in the rollback recovery from a backup, each SQL statement has to be executed in exactly the same way as it was initially executed.
· Create table... SELECT... execute the SELECT statement as a continuous reading statement, or execute the statement with a share lock, as described in the previous entry.
· If the unique key does not conflict, REPLACE is performed as an insert. In addition, set an exclusive nextkey lock for the rows that must be updated.
· UPDATE... WHERE... sets an exclusive next-key lock for each record encountered in the search.
· Delete from... WHERE... sets an exclusive next-key lock for each record encountered in the search.
· If you define a foreign key constraint for a table, any insert, update, or delete record that needs to check the constraints sets a shared row-level lock for the record that looks at the check constraints. InnoDB also sets these locks when the constraints fail.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.