This is the 15th day of my participation in the August More Text Challenge
About the author: Wu Kong, 8 years of experience in Internet development and architecture, explains distributed, architecture design, Java core technology with stories. Author of “JVM Performance Optimization Practice” column, open source “Spring Cloud Practice PassJava” project, public account: Wukong chat architecture. This article is available at www.passjava.cn
Hello everyone, I’m Brother Wukong. Today, I will bring you the interview questions of Dfactory:
What about locks in InnoDB storage engine?
Row-level locks
A shared Lock (S Lock) allows a transaction to read a row of data.
An X Lock that allows a transaction to delete or update a row of data.
Table level lock
A transaction wants to acquire a shared Lock for rows in a table.
A transaction wants to acquire an exclusive Lock for rows in a table.
supplement
1. If the locked object is regarded as a tree, locking the lowest level object is the same as locking the most fine-grained object. The coarse-grained object needs to be locked first.
For example, if you want to lock X on A record, you need to lock IX on database A, table, and page, and finally lock X on the record. 2. The default read operation is unlocked, using the MVCC multi-version control mechanism.
Why intentional locks?
If there are other transactions that want to lock the table, first check whether there are table level intent locks. This way, you don’t need to check whether there are S or X locks on the record. The advantage is that it is fast and saves a lot of steps.
Here’s an example from life:
There are many floors in the library, and there are many rooms on each floor. Room 201 on the second floor is under renovation and cannot be entered, which is equivalent to adding an exclusive lock to room 201. Then, a warning board was set up at the door of the second floor: Someone on the second floor is under renovation (equivalent to adding an intentional exclusive lock). When the library was closed, the administrator began to check whether there were people in each building and found a warning sign on the second floor. Ah, there were still people in decoration, so the library could not be closed temporarily. Here, room 201 is used as a row of records, and the second floor is used as a watch. The renovation is compared to transaction 1, and the librarian has to close the library compared to transaction 2.
Literary explanation:
IS and IX locks are table-level locks. They are proposed only to quickly determine whether the records in the table are locked when the S-level locks and X-level locks are added later, so as to avoid traversing to check whether there are locked records in the table. When a row is locked, if you want to add a table lock to the table where the row resides, you must first check whether the row is locked, otherwise there will be conflicts. IS and IX locks eliminate the need to traverse each row in a table to determine whether the row IS locked or not. You can determine whether a table has a row lock by checking whether the table has an intent lock.