Same Name

REPEATABLE READ. There was a time I had no idea what it meant.

Transaction isolation levels don't matter when you're just building apps. Everything works. Everything runs fine. Then production breaks in ways you can't reproduce. That's what makes it tricky.

I finally faced it when I had to touch payment code. MySQL defaults to REPEATABLE READ. Within the same transaction, every read returns the same result. Textbooks say so. And they're right — as long as you stick to plain reads.

The trouble is locking. The moment you issue SELECT ... FOR UPDATE, the world shifts. You step outside the snapshot. Rows committed by other transactions become visible. Rows that didn't exist a second ago appear. Repeatable stops being repeatable.

As if that weren't enough, InnoDB has gap locks. They lock the space between index records — the absence of rows — to prevent inserts into that void. Locking things that don't exist. Next-key locks grab a record and the gap before it at once. Two transactions acquiring these in different order: deadlock.

After getting burned enough, I started briefing every new engineer on this before anything else. Usually I had to back up and explain isolation levels from scratch. The SQL standard only defines anomalies. How to prevent them is up to each database. REPEATABLE READ in MySQL and REPEATABLE READ in PostgreSQL are entirely different under the same name.

Same name doesn't mean same thing. Matching spec language doesn't mean matching behavior. In large-scale payment systems, you can't write code without this baptism. Lately I've been letting AI write most of my code. I wonder if it has been through the baptism yet.