MySQL transaction: From Begin to Commit

Transactions are the core feature of MySQL database, and one of the reasons why engineers choose it as a datastore for their applications.

But have you ever wondered what happens during a transaction?
How is the consistency maintained?
What happens if a concurrent read occurs during a transaction?

In this article, we will discuss what happens when a transaction is executed.

People who are not members can read this article here:
Read without paywall

Photo by Kevin Ku on Unsplash

Before diving deeper, you should be aware of some common terms:

1. Buffer Pool:
   In-memory storage that keeps data pages. All reads first check here.

2. Redo Logs:
   Write-Ahead Log (WAL) used for durability.

3. Undo Logs:
   Keeps older versions of data for rollback and MVCC.

4. Redo Logs Buffer:
   In-memory buffer storing redo logs before flushing to disk.

5. Undo Logs Buffer:
   In-memory buffer storing undo logs.

Transaction scenario

Imagine a transaction is going on where data has to be inserted and the state of the record is getting updated.
At the same time, another service requests to read the value of the same row which is not yet committed.

You might think the simple answer is that a query outside the transaction will return nothing.
But how does MySQL ensure this?

To understand it, let’s walk through an example transaction with the following steps:

1. Transaction Begin
2. Insert a row in the user table
   (id: 1, name: Rony, state: CREATED)
3. Update the state to INPROGRESS
   (id: 1, name: Rony, state: INPROGRESS)
4. Read the data of user with id = 1
5. Update the state to COMPLETED
   (id: 1, name: Rony, state: COMPLETED)
6. Transaction End

Let’s go step by step in the process…

Step 1: Transaction begins

When a transaction begins, the database:

• Allocates a Transaction ID (TID)
• Creates in-memory metadata structures
• Tracks:
  • Modified pages in buffer pool
  • Undo log entries
  • Locks acquired

Nothing is written to disk yet.
No redo or undo logs are flushed at this point.

The transaction is added to the system-wide active transaction list to support:

• MVCC visibility
• Crash recovery

Step 2: Insert a row in the user table

This is the first data-modifying operation.

1. MySQL checks if the primary key already exists.
2. If not found:
   • Looks for the data page in the buffer pool
   • If not present → loads it from disk
3. Inserts the row and marks the data page as dirty

A dirty page means the in-memory page is modified but not yet flushed to disk.

Before completing insertion:

• An entry is created in the Undo Log Buffer
  (to support rollback and MVCC)
• An entry is created in the Redo Log Buffer
  (to guarantee durability)
• A lock is acquired on the record

At this point:

• All changes are only in memory
• Nothing is written to disk yet

created by ankit

Step 3: Update the state of the user to INPROGRESS

Now an UPDATE query is executed.

The engine:

1. Looks for the data page in the buffer pool
2. If the page is already dirty and modified by the same transaction:
   • It directly updates the row
3. The user state becomes:(id: 1, name: Rony, state: INPROGRESS)

Along with the update:

• Previous state is written to the Undo Log Buffer
• Change is written to the Redo Log Buffer
• Page remains dirty in memory

This ensures:

• Rollback safety
• MVCC consistency
• Crash durability

created by ankit

Step 4: Read the data of the user (SELECT)

When a SELECT query is issued:

1. MySQL checks the buffer pool
2. If the page is dirty and uncommitted:

• MVCC rules decide what version of data is visible

Two cases arise:

1. Same transaction reading:

• Returns its own latest uncommitted changes

2. Different transaction reading:

• Uses Undo Log pointer
• Reads the last committed version
• If no committed version exists → returns nothing

In our case:

Another transaction reading id = 1 gets no result,
because the record has no committed version yet.

created by ankit

Step 5: Update the state to COMPLETED

This is identical to Step 3.

State becomes: (id: 1, name: Rony, state: COMPLETED)

Actions:

• Update happens in buffer pool
• Undo log entry created
• Redo log entry created
• Page stays dirty

created by ankit

Step 6: Commit Transaction

This is the final step.

When COMMIT is executed:

1. Redo Log Buffer is flushed to disk
2. Transaction is marked as committed
3. Data pages remain dirty in memory
4. Actual page flush happens lazily later

This guarantees:

• Durability → via redo logs
• Consistency → via undo + MVCC
• Performance → delayed disk writes

What if the transaction fails? (Rollback)

If at any point the transaction fails and needs to be rolled back:

1. MySQL reads the Undo Log
2. It restores every modified record to its previous state
3. Buffer pool pages are reverted using undo entries
4. Locks are released
5. The transaction is removed from the active transaction list

This guarantees:

• Atomicity
• No partial writes
• Data consistency

For crash recovery:

• Redo logs are replayed for committed transactions
• Undo logs are applied for uncommitted ones

Conclusion

In this article, we walked through the complete lifecycle of a MySQL transaction, from BEGIN to COMMIT, and understood how:

• Buffer Pool manages in-memory data
• Undo Logs enable rollback and MVCC
• Redo Logs guarantee durability
• MVCC provides consistency during concurrent reads

Together, these components make MySQL:

• Fast
• Crash-safe
• Consistent under concurrency

You can now visualize what actually happens when a query runs inside a transaction instead of treating it as a black box.

If you found this helpful, clap, share, and follow for more insights on databases, backend systems, and distributed engineering.