How InnoDB locks pages in Cache?

InnoDB Locking Mechanism

  • Row Locking: InnoDB uses row-level locking to maintain data integrity during transactions. When a row of data is being updated, it acquires a lock on that specific row to prevent other transactions from modifying it simultaneously.
  • Buffer Pool Latches (Mutexes): InnoDB uses data structures called latches or mutexes to manage access to pages in the buffer pool and to ensure the consistency of those pages. When a thread needs to access a page in the buffer pool, it must first acquire the appropriate latch. If another thread is already holding that latch, the second thread will wait until the first thread has released the latch.

It’s important to note that these latches are internal to InnoDB and are generally invisible to users. They’re automatically managed by InnoDB as it reads and writes pages in the buffer pool, and users don’t have to (and can’t) manually acquire or release these latches.

In conclusion, while InnoDB doesn’t lock entire pages in the buffer pool, it does use a system of latches to manage concurrent access to those pages and ensure their consistency.

About Shiv Iyer 444 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.