Two Transactions, One Row: What Does MySQL Actually Let You See ?

Two users, One database row, Both reading and writing at almost exactly the same time. Who sees what ?

Can one transaction see another transaction’s uncommitted changes ?
Can the same query return different results inside a single transaction ?
And why does SELECT sometimes give you a snapshot, while SELECT ... FOR UPDATE
gives you something completely different ?

If you’ve ever wondered what MySQL actually does when transactions run concurrently, this is where it gets interesting.

InnoDB gives you four transaction isolation levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Most developers know their names, fewer can predict what will actually happen when two transactions execute at the same time.

That’s what we’re going to do here.

No abstract definitions first. No memorizing a table. We’ll make the transactions collide and see what MySQL does.

Why Do We Need Transactions ?

Consider a typical e-commerce purchase. When a customer buys a product, several database operations may need to happen together:

1. Check the product inventory.
2. Decrease the inventory.
3. Create the order.
4. Record the payment.
5. Update the order status.

What happens if the inventory is successfully decreased but creating the order fails ?

You could end up with a database where the product’s inventory has decreased, but no corresponding order exists.

That’s exactly the kind of problem transactions are designed to prevent.

A transaction lets us treat multiple database operations as a single logical unit:

Either all of the operations succeed, or the changes are rolled back.

For example:

START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 10;
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;

If something goes wrong:

ROLLBACK;

Transactions are one of the foundations of ACID, and the I in ACID stands for Isolation. Isolation is where things get interesting.

What Is a Transaction Isolation Level ?

Imagine two transactions running at approximately the same time:

Transaction A                Transaction B
      |                            |
      |---- read data ------------>|
      |                            |
      |                       update data
      |                            |
      |<--- what can A see ? ------|

Both transactions may be reading and modifying the same data. The isolation level defines the rules for what each transaction is allowed to see while other transactions are running.

Those rules can dramatically change the behavior of your application.

For example:

• Can you see another transaction’s uncommitted changes ?
• Can the same query return a different value later ?
• Do you keep seeing the same snapshot ?
• Does your read lock the row ?
• Can another transactions modify the row while you’re making a decision ?

Let’s find out.

1. READ UNCOMMITTED - When Uncommitted Data Becomes Visible

READ UNCOMMITTED is the least restrictive isolation level. A transaction can potentially see changes made by another transaction before those changes have been committed. This is known as a dirty read.

Suppose the database contains:

stock = 10

Transaction A starts:

START TRANSACTION;
UPDATE products SET stock = 0 WHERE id = 10;

But Transaction A hasn’t committed yet.

Now Transaction B executes:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT stock FROM products WHERE id = 10;

Transaction B may see:

0

Wait. That 0 hasn't actually been committed.

Now Transaction A rolls everything back:

ROLLBACK;

The actual value goes back to:

10

Transaction B just observed a value that never became part of the committed database state. That’s a dirty read.

Why is this dangerous ?

Imagine applying the same idea to:

• payments
• bank balances
• inventory
• orders
• seat reservations

You could make a business decision based on data that ultimately disappears. A simple way to remember it:

READ UNCOMMITTED: you get more concurrency by giving up consistency.

2. READ COMMITTED - Only See What Has Been Committed

READ COMMITTED takes a more conservative approach. A transaction doesn’t see another transaction’s uncommitted changes through a normal consistent read. But there’s a catch.

The data you see can change while your transaction is still running.

Suppose:

Initial stock = 10

Transaction A starts:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT stock FROM products WHERE id = 10;

It sees:

10

Now Transaction B changes the value:

START TRANSACTION;
UPDATE products SET stock = 5 WHERE id = 10;
COMMIT;

Transaction A executes the same query again:

SELECT stock FROM products WHERE id = 10;

This time:

5

The same transaction saw 10 and later 5. That’s a Non-Repeatable Read.

But why this happens ? Because under READ COMMITTED, each consistent read can establish its own fresh snapshot.

When is READ COMMITTED useful ?

It’s a good fit for systems where seeing relatively fresh committed data is more important than maintaining one consistent snapshot throughout the entire transaction.

It also changes InnoDB’s locking behavior. For locking READ, UPDATE, and DELETE, InnoDB generally uses record locks rather than gap locks, except where gap locking is needed for foreign-key and duplicate-key checks.

A simple way to remember it:

READ COMMITTED: every consistent read sees committed data as of that read, so a later read can see changes committed by other transactions.

3. REPEATABLE READ - Your Transaction Gets a Snapshot

Now we reach InnoDB’s default isolation level: REPEATABLE READ.

Suppose:

stock = 10

Transaction A starts:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT stock FROM products WHERE id = 10;

It sees:

10

Now Transaction B changes the value:

START TRANSACTION;
UPDATE products SET stock = 5 WHERE id = 10;
COMMIT;

Transaction A runs the same query again:

SELECT stock FROM products WHERE id = 10;

And for a normal, non-locking consistent read, it can still see 10, not 5.

Why is that ? Because consistent reads in a REPEATABLE READ transaction use the transaction's snapshot.

So from Transaction A’s perspective, the database can effectively look like:

Transaction A’s view:

stock = 10

even though the current committed value is now:

stock = 5

4. SERIALIZABLE - Make Concurrency Behave More Like Sequential Execution

SERIALIZABLE is the strictest of the four isolation levels. Its goal is to provide behavior that is closer to transactions executing one after another rather than freely interleaving.

Conceptually:

Transaction A
     |
     | read/write
     |
     v
Transaction B
     |
     | waits
     v
Transaction A commits
     |
     v
Transaction B continues

In InnoDB, when auto-commit is disabled, plain SELECT statements under SERIALIZABLE are implicitly converted to locking reads using FOR SHARE.

This provides stronger consistency guarantees, but the additional locking can reduce concurrency.

So while SERIALIZABLE sounds like the safest choice, it isn't automatically the best choice. Stronger isolation comes with a cost.

A simple way to remember it:

SERIALIZABLE: strongest isolation, but concurrency becomes more expensive.

Comparing the Four Levels

Here’s the quick mental model:

But don’t treat this table as the whole story.

InnoDB’s behavior also involves:

• MVCC
• consistent reads
• locking reads
• record locks
• gap locks
• next-key locks
• transaction boundaries
• indexes

The interesting part isn’t memorizing the table. It’s being able to predict what happens when two transactions collide.

The Bigger Lesson

Here’s the part that matters most in real applications:

Choosing an isolation level doesn’t automatically make your concurrent code correct.

Isolation level determines the visibility and concurrency rules of transactions, But your SQL statements determine how you interact with the data.

Compare:

SELECT stock FROM products WHERE id = 10;

with:

SELECT stock FROM products WHERE id = 10 FOR UPDATE;

They may look almost identical, but they are not.

The first is a consistent read.

The second is a locking read.

And when two requests hit your application at almost exactly the same time, that difference can determine whether your system behaves correctly.

The Question You Should Ask

When debugging or designing concurrent database code, don’t ask only:

“Which isolation level am I using ?”

Ask this instead:

“What happens if two transactions execute these exact statements at the same time ?”

That’s the question that exposes race conditions.

Once you start thinking in terms of concurrent transactions instead of isolated SQL statements, MySQL’s transaction model becomes much easier to understand.

Final Takeaway

Transaction isolation isn’t just a list of four configuration values. It’s a set of rules governing what your transactions can see, when they can see it, and how they interact with other transactions.

If you remember only one thing from this article, remember this:

The real test of your database design isn’t what happens when one transaction runs. It’s what happens when two transactions run at the same time.

And when those two transactions are fighting over the last item in stock, the difference between a normal SELECT and SELECT ... FOR UPDATE suddenly becomes very important.

That’s where we’re going next.

Next: MySQL Overselling - Why SELECT Isn't Enough and How SELECT ... FOR UPDATE Solves It