Anand Sukumaran Nair

Startup Founder, Software Engineer, Abstract thinker
Co-founder & CTO @ Engagespot (Techstars NYC '24)

What happens to a MYSQL INSERT query under the hood

It’s interesting to learn “how stuff works”, especially the part that is abstracted from us. I was curious to learn how a MYSQL INSERT query is performed by maintaining ACID properties. Writing briefly on what I’ve learned from various sources.

Depending on where you run the query, it is transported to the MYSQL server through a TCP connection or a socket and temporarily stored in a buffer area. If your query is too long, then it might exceed the max_allowed_packet size resulting in error.

Parsing This is the first step. MYSQL parses your query to determine the column and table where the data needs to be inserted.

Locking Depending on the query, MYSQL acquires appropriate locks to ensure the consistency of your data. This is required to support concurrency. (Allowing other Mysql clients to operate on the same data). Technologies like Multi Version Concurrency Control (MVCC) is used here.

Writing to InnoDB Write Buffer Your data (the newly inserted row) is written into a temporary storage area that resides in the RAM.

Redo Log Imagine what will happen if MYSQL process crashes while it was writing the rows. It might result in partial updation leaving the entire system in an inconsistent state. To prevent this, MYSQL immediately writes your data into the redo log file on physical disk to ensure that your data is safe even in case of a system crash!

Buffer Pool MySQL maintains a memory area where actual data (pages) is kept for shorter period of time so it can reduce disk I/O. If the data page where your row needs to be inserted is already loaded to the memory cache, MYSQL writes your new row to that page without additional I/O operation.

Operating System File Buffer MySQL frequently clears the buffer memory by informing the operating system to write it to the disk. But operating system is smart. They don’t write the data immediately to disk because there might be other i/o happening. So, OS has another cache in the memory. Your row finally gets written into this buffer.

OS Disk Write MySQL has done it’s job. But remember, your data is still in the RAM and haven’t persisted to the disk. The operating system eventually write the page to the storage disk (HDD/SSD) by seeking the appropriate disk block.

Acknowledgment Upon flushing the row to disk, OS will inform MYSQL that the data has been written to disk!

Transaction Commit Once MYSQL receives this acknowledgement, it tells you that the row has been inserted.

Depending on how you run the query, MYSQL either keeps the data in buffer and lies to you that it has been inserted, or it actually performs a disk write and give you physical write confirmation. Queries wrapped in Transactions are written into the disk at once, (No, it’s not just the OS cache write, but the physical disk write). Hence, transactions are costly!

So, next time you perform an INSERT query in MySQL, appreciate magic behind the scenes that securely stores your data!