Blogfoon

Blogfoon is a personal space for thoughtful notes on technology, coding, travel, and ordinary life.

  • Published:

Background

The previous discussion focused on how MySQL behaves under the Repeatable Read (RR) isolation level and the different lock patterns it introduces. Under Read Committed (RC), locking is noticeably simpler, and the differences become obvious once you compare the same update statements side by side.

What follows is a lock behavior walkthrough under RC, organized by access path: primary key, unique index, non-unique index, and no index.

Locking behavior in the tests

Primary key index

Equality update

Row found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where id = 10;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

lock result

In this case, besides the table-level intention exclusive lock, MySQL adds only a record lock on the primary key. This is the same as under RR.

Row not found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where id = 11;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

lock result

This is where RC differs from RR. Other than the intention exclusive lock on the table, no additional lock is taken.

Range update

Rows found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where id >= 5 and id <=15;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

lock result

Again, this is different from RR. MySQL takes the table intention exclusive lock and primary key record locks, but no gap locks are added.

No rows found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where id >= 5 and id <=7;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

This still differs from RR, but it matches the behavior of a missed equality lookup on the primary key: apart from the table intention exclusive lock, nothing else is locked.

Unique index

Equality update

Row found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_uq = 10;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

lock result

MySQL adds a table intention exclusive lock, a record lock on the unique index, and a record lock on the primary key.

Row not found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_uq = 11;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

When nothing matches, the behavior is the same as with a primary key lookup: other than the table intention exclusive lock, no other lock is taken.

Range update

Rows found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_uq >= 5 and f_uq <=15;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

This behaves the same as the equality update above: MySQL takes the table intention exclusive lock, unique index record locks, and primary key record locks.

No rows found

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_uq >= 5 and f_uq <=7;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

This also matches the equality case: aside from the table intention exclusive lock, no additional locks are held.

Non-unique index

  • Equality update
<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_index = 10;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

Range update

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_index >= 5 and f_index <=15;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

lock result

When rows are matched, both equality and range updates take a table intention exclusive lock, record locks on the non-unique index, and record locks on the primary key.

When no rows match, the pattern remains consistent with the earlier cases: except for the table intention exclusive lock, no lock is added.

No index

<table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_num = 10;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table> <table> <thead> <tr> <th>1</th> <th>update lock_test set f_num = 0 where f_num >= 5 and f_num <=15;</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> </tr> </tbody> </table>

lock result

This differs sharply from RR. Whether it is an equality update or a range update, if rows are matched, MySQL adds only primary key record locks. If nothing matches, then aside from the table intention exclusive lock, no other lock appears.

What stands out under RC

summary

The contrast between RC and RR is fairly clear from these tests.

Under RR, locking is more involved. In addition to record locks, MySQL may introduce gap locks and next-key locks, and even failed lookups can leave locks behind so that repeatable reads remain guaranteed.

Under RC, the logic is much more direct:

  • if a record is found, MySQL locks the record
  • if no record is found, it usually does not lock anything beyond the table intention exclusive lock

That simpler behavior is also a clue to performance differences. Unless the business scenario truly depends on repeatable read semantics, RC usually performs much better than RR.

There is one more detail worth noting: under RC, updating without an index may appear to take fewer locks than updating through a unique or non-unique index, because only primary key record locks are visible when rows are matched. But this does not make no-index updates preferable. Without an index, MySQL must perform a full table scan, and that cost usually outweighs any reduction in locking. In practice, update statements without supporting indexes are still a bad trade-off.

Related Posts