Isolation Levels and Locking

What this page covers

This page builds on Connections and Transactions. That page already covers COMMIT, ROLLBACK, SAVEPOINT, the undo log, timeouts, and deadlock basics, so they are not repeated here.

Introduction

What is it? A transaction groups statements into one unit. Isolation answers a different question: when two transactions run at the same time on the same rows, how much of each other's work can they see? The isolation level is the setting that decides this. Locks are one of the tools the database uses to keep that promise. A lock is a marker on a row that says "I am using this, others must wait".

Why do we need it? Most bugs here never show up on your laptop, because on your laptop only one request runs at a time. They show up at 10 AM on Tatkal day, when 40,000 people try to book the same train in the same minute. Two requests read "1 seat left", both say "yes", and you have sold one berth to two people. Knowing the isolation level and the right locking pattern is how you stop that.

Real-world analogy Think of a shared Google Sheet with a team budget. If you can see someone's half-typed numbers, that is a dirty read. If a number changes while you are adding up a column, that is a non-repeatable read. If a new row appears while you are counting rows, that is a phantom. If two people overwrite the same cell and one person's change silently disappears, that is a lost update.

Business use case A wallet app (think Paytm or PhonePe) lets a user pay ₹250 at a Swiggy checkout while an auto-debit for ₹800 runs at the same moment. The balance is ₹1,000. Without correct locking, both payments read ₹1,000 and both succeed. The user ends up with a balance that does not add up, and finance has to clean it up by hand.

Dataset Used

Three small tables, used through the whole page.

CREATE TABLE seats (
  seat_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
  train_no     INT NOT NULL,
  journey_date DATE NOT NULL,
  coach        VARCHAR(5) NOT NULL,
  berth        VARCHAR(5) NOT NULL,
  status       VARCHAR(10) NOT NULL DEFAULT 'AVAILABLE',
  booked_by    BIGINT NULL,
  KEY idx_seats_train (train_no, journey_date, status)
) ENGINE=InnoDB;

CREATE TABLE wallets (
  user_id  BIGINT PRIMARY KEY,
  balance  DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id BIGINT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  stock      INT NOT NULL,
  version    INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO seats (train_no, journey_date, coach, berth, status) VALUES
  (12951, '2026-10-20', 'B2', '33', 'BOOKED'),
  (12951, '2026-10-20', 'B2', '34', 'AVAILABLE'),
  (12951, '2026-10-20', 'B2', '35', 'AVAILABLE');
INSERT INTO wallets VALUES (42, 1000.00);
INSERT INTO products VALUES (7, 'Redmi Note 13 (Big Billion Day)', 9999.00, 100, 0);

seats

wallets: user 42 has ₹1,000. products: product 7 has 100 units in stock, version 0.

Beginner Core Path: See the Race, Then Pick the Fix

Before learning every isolation level, follow one rule: a transaction does not automatically make a read-then-write sequence safe. Use two database connections so Session A and Session B can overlap.

Dirty-read timeline: reading work that may disappear

1. Session A starts a transaction and changes user 42's balance from ₹1,000 to ₹0, but does not commit.
2. Session B, using MySQL READ UNCOMMITTED, reads ₹0 and rejects a purchase.
3. Session A rolls back. The real balance is ₹1,000 again.
4. Session B made a decision from a value that never became permanent. That is a dirty read.

The practical fix is normally simple: do not use READ UNCOMMITTED. MySQL READ COMMITTED and stronger levels hide uncommitted changes; PostgreSQL never exposes them, even when READ UNCOMMITTED is requested.

Lost-update timeline: two valid writes, one result disappears

Start with balance ₹1,000:

The correct business result is not ₹200. After the ₹250 payment, only ₹750 remains, so the ₹800 debit should be refused. COMMIT did not prevent the bug because each transaction computed from a stale read.

Practical fix chooser

For the wallet's one-row deduction, prefer the smallest correct statement:

UPDATE wallets
SET balance = balance - 250
WHERE user_id = 42
  AND balance >= 250;
-- affected rows = 1: paid; 0: insufficient balance

Use the rest of the chapter to understand why these fixes work and when their dialect-specific behavior differs.

Syntax

Mental Model

 Weaker isolation                                    Stronger isolation
 READ UNCOMMITTED -> READ COMMITTED -> REPEATABLE READ -> SERIALIZABLE

 more anomalies possible  <------------------------>  fewer anomalies
 less waiting, more speed <------------------------>  more waiting or retries

Isolation is a trade. Stronger levels give you fewer surprises but more waiting, more lock conflicts, or more transactions that fail and must be retried. Most apps run at the default level and add explicit locks only where money or stock is involved.

1. The Four Anomalies

An anomaly is something that would never happen if transactions ran one after another, but can happen when they overlap. In every diagram below, Session A and Session B are two different connections.

1.1 Dirty read

You read data that another transaction has changed but not committed. If that transaction rolls back, you acted on a value that never really existed.

Only READ UNCOMMITTED allows this, and only in MySQL. PostgreSQL never shows uncommitted data, even if you ask for that level. There is rarely a good reason to use READ UNCOMMITTED in application code.

1.2 Non-repeatable read

You read the same row twice in one transaction and get two different values, because someone else committed a change in between.

This matters when you compute something in two steps, for example an invoice that reads the price once for the line total and once for the tax.

1.3 Phantom read

You run the same range query twice and the set of rows changes. Rows appear (or disappear) because another transaction inserted or deleted rows that match your WHERE.

Non-repeatable read is about one row changing. Phantom is about the set of rows changing.

1.4 Lost update

Two transactions read the same value, both compute a new value from it, and both write. The second write silently overwrites the first.

Of the four, this is the one you will actually meet in production code, and interviewers know it. The root cause is "read in the app, compute in the app, write back a fixed number". Sections 5 and 8 show the fixes.

2. The Four Isolation Levels

This is what the SQL standard says each level must prevent:

The standard sets a minimum. Real databases often do better, so the table above is not the full story:

• PostgreSQL READ UNCOMMITTED is accepted but behaves exactly like READ COMMITTED. Dirty reads never happen in PostgreSQL.
• PostgreSQL REPEATABLE READ is snapshot isolation. A snapshot is a frozen picture of the committed data at one moment (section 4 explains it). The whole transaction reads from one snapshot, so phantoms do not appear either.
• MySQL REPEATABLE READ also prevents phantoms, in two ways. Plain SELECT reads from a snapshot. Locking reads like SELECT ... FOR UPDATE take next-key locks that stop other sessions from inserting into the range (section 7). There is one InnoDB quirk: if your transaction runs an UPDATE over a range, rows that another session committed into that range become visible to your later plain SELECTs. Mixing plain reads and writes on the same range can still show you a phantom.

Lost update is not in the standard's table at all, and here the two databases really differ:

So in MySQL, the default level does not save you from lost updates. You need a locking read, an atomic update, or a version check. In PostgreSQL at REPEATABLE READ the update is not lost, but the losing transaction gets error 40001, so your code has to catch it and retry.

3. Defaults and How to Change the Level

-- MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;          -- next transaction only
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- every transaction on this connection
SELECT @@transaction_isolation;                          -- check it

-- PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- your statements
COMMIT;
SHOW transaction_isolation;

Many large MySQL deployments run at READ COMMITTED instead of the default. It takes far fewer gap locks (section 7), so busy tables see fewer lock waits and deadlocks. For a typical web app with many short transactions, that is a sensible choice, but make it on purpose: set it in the server config or the connection pool, and test the code paths that quietly relied on repeatable reads.

4. MVCC in Simple Words

MVCC means Multi-Version Concurrency Control. The idea: when a row is updated, the database keeps the old version around for a while, so readers do not have to wait for writers.

• A writer changes the row and keeps the old version in the undo log (InnoDB) or as an old row version in the table (PostgreSQL).
• A reader gets a snapshot: a rule that says "show me the latest version that was committed before my snapshot was taken".
• Plain SELECT statements normally read from the snapshot. At READ COMMITTED and REPEATABLE READ they do not take row locks, so ordinary row writers do not block them. Table/metadata locks, DDL, resource pressure, and engine-specific operations can still make a SELECT wait; "readers do not block writers" is the useful MVCC rule, not an absolute promise that reads never wait.

 Row: wallets user 42

 version 3  balance = 750   committed by T105 at 10:00:05   <- newest
 version 2  balance = 1000  committed by T101 at 09:58:10   <- in undo log
 version 1  balance = 1200  committed by T88  at 09:40:00   <- in undo log

 A reader whose snapshot is from 10:00:00 sees 1000.
 A reader whose snapshot is from 10:00:10 sees 750.

The difference between the levels is mostly when the snapshot is taken. READ COMMITTED takes a fresh snapshot for every statement. REPEATABLE READ keeps one snapshot for the whole transaction. That one rule explains why READ COMMITTED shows non-repeatable reads and REPEATABLE READ does not.

One catch in InnoDB: snapshots are only for plain reads. UPDATE, DELETE, SELECT ... FOR UPDATE, and SELECT ... FOR SHARE always work on the latest committed version, not your snapshot. This is called a current read. So inside one REPEATABLE READ transaction, a plain SELECT can show balance ₹1,000 while SELECT ... FOR UPDATE on the same row shows ₹750. Most people do not expect this, which is why interviewers ask about it.

PostgreSQL handles the same situation differently. At REPEATABLE READ, if you try to update or lock a row that another transaction changed after your snapshot, PostgreSQL does not quietly hand you the newer value. It fails with a serialization error (40001) and expects you to retry the whole transaction.

5. Shared and Exclusive Row Locks

There are two basic kinds of row lock. A shared lock means "I am reading this row and it must not change until I am done". Many sessions can share it. An exclusive lock means "I am about to change this row". Only one session can hold it.

Row locks are held until the transaction ends with COMMIT or ROLLBACK. This is the part that hurts in production. A transaction that locks a wallet row and then sits idle (waiting on a slow API call, or a bug that forgets to commit) blocks every other payment for that wallet until it ends.

Outside a transaction (auto-commit), a SELECT ... FOR UPDATE locks the row and releases it right away, which is useless. Always use it inside START TRANSACTION.

5.1 SELECT ... FOR UPDATE

Use it when you read a row and then write it based on what you read. It fixes the lost update from section 1.4.

START TRANSACTION;
SELECT balance FROM wallets WHERE user_id = 42 FOR UPDATE;  -- returns 1000 and locks the row
-- App checks: 1000 >= 250, OK
UPDATE wallets SET balance = balance - 250 WHERE user_id = 42;
COMMIT;

If the auto-debit runs the same code at the same moment, its SELECT ... FOR UPDATE waits. When the first transaction commits, the second one reads ₹750, checks 750 >= 800, fails cleanly, and rolls back.

5.2 SELECT ... FOR SHARE

Use it when a row must not change or disappear while you insert something that depends on it, but you are not going to change the row yourself. For example, SELECT price FROM products WHERE product_id = 7 FOR SHARE before inserting a cart item, so nobody deletes or reprices the product in between.

Many readers can hold a shared lock together. A writer has to wait for all of them. FOR SHARE arrived in MySQL 8.0. Older code uses LOCK IN SHARE MODE, which still works in MySQL 8 but cannot take NOWAIT or SKIP LOCKED. PostgreSQL has always used FOR SHARE.

In practice FOR SHARE is used much less than FOR UPDATE. If two sessions both hold a shared lock on a row and then both try to update it, each waits for the other, and you get a deadlock. If you plan to write the row later in the same transaction, take FOR UPDATE from the start.

6. NOWAIT and SKIP LOCKED

By default, if a row is locked, your statement waits. In MySQL the wait ends after innodb_lock_wait_timeout, which is 50 seconds by default, and only the waiting statement fails, not the whole transaction. PostgreSQL waits forever unless you set lock_timeout. Fifty seconds, or forever, is far longer than any user will wait for a page, so most teams lower these for app connections.

Two options change the waiting behaviour. Both work in MySQL 8.0 and later, and in PostgreSQL (SKIP LOCKED since 9.5).

6.1 A job queue with SKIP LOCKED

A team at a Bengaluru startup sends order confirmation SMS through a jobs table. Ten worker processes poll it. Without SKIP LOCKED, all ten workers try to lock the same first five rows, nine of them wait, and throughput is no better than one worker.

The table is jobs(job_id, payload, status, picked_at) with an index on (status, job_id).

-- Each worker runs this loop
START TRANSACTION;
SELECT job_id, payload
FROM jobs
WHERE status = 'PENDING'
ORDER BY job_id
LIMIT 5
FOR UPDATE SKIP LOCKED;
-- Suppose it got job_id 101 to 105
UPDATE jobs SET status = 'RUNNING', picked_at = NOW() WHERE job_id IN (101, 102, 103, 104, 105);
COMMIT;
-- Send the SMS outside the transaction, then mark the jobs DONE

A second worker running the same query at the same moment gets jobs 106 to 110 at once, without waiting. Keep the transaction short. Mark the jobs as RUNNING and commit, then do the slow work (calling the SMS provider) outside the transaction. Workers crash, so add a cleanup job that resets rows stuck in RUNNING for more than, say, 10 minutes. That also means a job can run twice, so the SMS step should be safe to repeat.

SKIP LOCKED gives an inconsistent view of the table on purpose. Do not use it for reports or counts.

7. Gap and Next-Key Locks

This one is specific to InnoDB. PostgreSQL does not have gap locks.

An index keeps its entries in sorted order. A gap is the empty space between two neighbouring entries, where a new row could be inserted later. A gap lock locks that empty space, so nobody can insert into it. A next-key lock is a row lock plus a lock on the gap just before that row.

At REPEATABLE READ, when InnoDB runs a locking read or an update over a range (for example WHERE train_no = 12951 AND journey_date = '2026-10-20' FOR UPDATE), it takes next-key locks on the index entries it scans. Other transactions cannot insert new rows into that range until you commit. That is how InnoDB prevents phantoms for locking reads.

The side effects are what you see in production:

• An INSERT from another session can block even though it does not touch any existing row.
• Two sessions can deadlock on gaps without ever touching the same row.
• If your WHERE has no usable index, InnoDB scans and locks every row it reads. On a big table that behaves like a table lock. Check the plan with EXPLAIN (see Performance and Debugging).

At READ COMMITTED, InnoDB turns gap locking off for normal reads and updates (it keeps it only for foreign key and duplicate key checks). It also releases locks on scanned rows that did not match the WHERE. That is the main reason teams move busy MySQL systems to READ COMMITTED.

8. Optimistic vs Pessimistic Locking

Pessimistic locking assumes conflicts will happen, so it locks first and works second (SELECT ... FOR UPDATE).

Optimistic locking assumes conflicts are rare. It does not lock while reading. It adds a version column and checks at write time that nobody changed the row in between.

-- 1. Read without a lock: returns stock = 100, version = 0
SELECT stock, version FROM products WHERE product_id = 7;

-- 2. Write only if the version is still 0
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE product_id = 7 AND version = 0;

-- 3. Check how many rows changed (in app code, use the driver's affected rows)
SELECT ROW_COUNT();

• Affected rows = 1: your write won.
• Affected rows = 0: someone else changed the row first. Read again and retry, or show "the item was updated, please refresh".

JPA/Hibernate does exactly this with the @Version annotation and throws OptimisticLockException when the count is 0.

Which one to pick

There is a third option that people forget: a single atomic statement that does the check and the write together.

UPDATE wallets SET balance = balance - 250 WHERE user_id = 42 AND balance >= 250;
-- affected rows = 0 means "insufficient balance"

The UPDATE takes the exclusive lock itself and checks the condition against the latest committed value, so nothing can sneak in between the check and the write. When the logic fits in one statement, this is the version I would ship. (In PostgreSQL this works as described at READ COMMITTED. At REPEATABLE READ, a concurrent change to the row gives you error 40001 instead, and you retry.)

9. Real Cases

9.1 Booking the last IRCTC seat

Two people in Delhi and Patna click "Book" on the last two berths of train 12951 at 10:00:01. The bad version reads "available seats" with a plain SELECT, picks the first one in the app, and updates it. Both pick seat 2.

START TRANSACTION;
SELECT seat_id, coach, berth
FROM seats
WHERE train_no = 12951
  AND journey_date = '2026-10-20'
  AND status = 'AVAILABLE'
ORDER BY seat_id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Delhi gets seat 2, Patna gets seat 3. Nobody waits.
UPDATE seats SET status = 'BOOKED', booked_by = 501 WHERE seat_id = 2;  -- Delhi's session
COMMIT;

If the query returns no row, the train is full for this user. With plain FOR UPDATE (no SKIP LOCKED), Patna would wait for Delhi, then find seat 2 already booked, and the app would need to loop. SKIP LOCKED hands out different seats at the same time.

9.2 Wallet balance deduction

Use the atomic update from section 8 when it is a single wallet. When a payment touches two wallets (user 42 pays the merchant wallet 9001), lock both rows with FOR UPDATE in a fixed order, for example the smaller user_id first. That avoids the deadlock pattern explained in Connections and Transactions.

START TRANSACTION;
SELECT user_id, balance FROM wallets WHERE user_id IN (42, 9001) ORDER BY user_id FOR UPDATE;
UPDATE wallets SET balance = balance - 250 WHERE user_id = 42 AND balance >= 250;
-- if affected rows = 0, ROLLBACK and return "insufficient balance"
UPDATE wallets SET balance = balance + 250 WHERE user_id = 9001;
COMMIT;

9.3 Inventory for a Flipkart Big Billion Day flash sale

10,000 phones, 2 lakh users hitting "Buy" at 12:00 noon. Optimistic locking with a version column looks fine on paper but falls apart here: almost every attempt conflicts, so most requests retry again and again. The atomic conditional update works much better:

UPDATE products SET stock = stock - 1 WHERE product_id = 7 AND stock > 0;
-- affected rows = 1: reserved one unit. 0: sold out.

Every buyer still queues on the same row lock, but each statement keeps the lock for only the short duration of that database operation; the actual time depends on contention, hardware, logging, and transaction boundaries. At very high scale, teams go further: keep the counter in Redis and write orders to the DB later, or split stock into 10 rows (buckets) of 1,000 units each and pick a bucket at random to spread the lock.

Deep Dive

Why SERIALIZABLE is rarely the default. In MySQL, SERIALIZABLE makes every plain read take shared locks, so readers and writers block each other and deadlocks go up. In PostgreSQL, SERIALIZABLE (called SSI) does not block much, but it aborts transactions it thinks could break the serial order. Your app must catch SQLSTATE 40001 and retry the whole transaction. It is a good choice for complex money logic if you write the retry loop. It is a bad choice if nobody does.

Snapshot isolation is not serializable. PostgreSQL REPEATABLE READ blocks every anomaly in section 1, yet it still allows write skew. Example: a rule says at least one doctor must be on call. Two doctors each check "is someone else on call?", both see yes, and both go off call. Each transaction changed a different row, so no row conflict was detected. Only SERIALIZABLE, or an explicit lock on the rows you checked, stops this.

Long transactions hurt MVCC. The database cannot throw away old row versions that an open snapshot might still need. One forgotten transaction left open for 3 hours makes InnoDB's undo log (the history list) grow, and in PostgreSQL it stops VACUUM from cleaning dead rows. Everything gets slower, and nothing in the slow query log points at the real cause.

Lock queues during migrations. A schema change like ALTER TABLE needs a table-level lock for at least a moment. If a long transaction is still open on that table, the ALTER waits. Every new query on the table then queues behind the waiting ALTER, and the app looks frozen even though the ALTER has not started. Before a migration, check for long-running transactions, and set a short lock_wait_timeout (MySQL) or lock_timeout (PostgreSQL) on the migration session so it gives up instead of freezing the table.

Seeing locks. In MySQL 8, performance_schema.data_locks shows current locks and performance_schema.data_lock_waits shows who waits for whom. SHOW ENGINE INNODB STATUS shows the latest deadlock. In PostgreSQL, look at pg_locks joined with pg_stat_activity.

Error codes to recognise. MySQL: 1205 (lock wait timeout), 1213 (deadlock), 3572 (NOWAIT could not get the lock). PostgreSQL: 55P03 (lock not available), 40P01 (deadlock), 40001 (serialization failure, retry it).

Real-world Use Cases

• Railway and movie seat booking: FOR UPDATE SKIP LOCKED to give different seats to concurrent users.
• Wallets and ledgers: atomic conditional updates, or FOR UPDATE with a fixed lock order for transfers.
• Flash sale stock: UPDATE ... SET stock = stock - 1 WHERE stock > 0, with buckets or Redis at extreme scale.
• Background workers: job and outbox tables polled with SKIP LOCKED.
• CMS and admin panels: optimistic locking with a version column so two editors do not overwrite each other.
• Month-end reports: a REPEATABLE READ transaction so every query in the report sees the same snapshot.

Review Tips

• Draw the two-session timeline on the whiteboard. Interviewers like it more than definitions.
• Know both defaults: MySQL REPEATABLE READ, PostgreSQL READ COMMITTED.
• Say clearly that MySQL's REPEATABLE READ does not stop a lost update when the app reads then writes a fixed value.
• For "how do you stop overselling?", start with the atomic conditional update, then mention FOR UPDATE and optimistic locking as alternatives with trade-offs.
• Mention SKIP LOCKED for queues. Few candidates bring it up.
• In an interview, the answer is usually a pattern (FOR UPDATE, atomic update, version column). In a real system, also say how you would see the problem: lock wait timeouts and deadlock errors in the logs, performance_schema.data_lock_waits or pg_locks, and transactions that stay open too long.

Common Mistakes to Avoid

• FOR UPDATE outside a transaction. With auto-commit on, the lock is released the moment the statement finishes. Wrap it in START TRANSACTION ... COMMIT.
• Reading in the app, writing a fixed value back. UPDATE wallets SET balance = 750 after reading 1000 is the classic lost update. Write balance = balance - 250 with a condition, or lock first.
• Assuming REPEATABLE READ means "safe". It stops non-repeatable reads for plain selects. It does not stop lost updates in MySQL.
• Holding locks while calling an external API. Locking a wallet row and then waiting 4 seconds for a payment gateway blocks every other payment for that user. Do the slow call outside the transaction.
• Locking rows with no index on the WHERE columns. InnoDB then locks every row it scans. One booking query can block the whole seats table.
• Ignoring affected rows in optimistic locking. If you do not check that exactly one row changed, the version column does nothing.
• SERIALIZABLE without a retry loop. In PostgreSQL you will get serialization failures under load. Without retries, users just see errors.
• Changing the isolation level mid-request on a pooled connection. The setting leaks to the next request that gets that connection. Configure it at the pool level.

Interview Questions

"What is the difference between a non-repeatable read and a phantom read?"

A non-repeatable read is when one row you already read changes value because another transaction committed an update. A phantom read is when the set of rows matching your WHERE changes because another transaction inserted or deleted rows.

"What are the default isolation levels of MySQL and PostgreSQL?"

MySQL InnoDB defaults to REPEATABLE READ. PostgreSQL defaults to READ COMMITTED.

"Does REPEATABLE READ in MySQL prevent lost updates?"

No. If two transactions read a balance with a plain SELECT and both write back a value computed in the app, the second write overwrites the first without any error. You need SELECT ... FOR UPDATE, an atomic UPDATE with a condition, or a version check.

"What is MVCC?"

The database keeps older versions of rows so readers can see a consistent snapshot without waiting for writers. READ COMMITTED takes a new snapshot for each statement, and REPEATABLE READ keeps one snapshot for the whole transaction.

"When would you use SKIP LOCKED?"

When many workers or users compete for any one of a set of rows, like jobs in a queue or seats on a train. Each one gets a different unlocked row instead of waiting on the same one.

"Optimistic or pessimistic locking for a flash sale?"

Neither in its plain form is ideal. Optimistic locking causes a storm of retries when everyone hits one row, so I would use a single atomic UPDATE with stock > 0 in the WHERE and check affected rows, and move the counter to Redis or split it into buckets if one row still cannot keep up.

Key Takeaways

• Isolation decides how much concurrent transactions see of each other. The four anomalies: dirty read, non-repeatable read, phantom read, lost update.
• MySQL defaults to REPEATABLE READ, PostgreSQL to READ COMMITTED.
• MVCC lets plain reads use a snapshot without locks. Writes and locking reads always see the latest committed data.
• FOR UPDATE takes an exclusive lock, FOR SHARE a shared one. Both last until the transaction ends.
• NOWAIT fails fast. SKIP LOCKED skips busy rows, which is perfect for queues and seat allocation.
• Gap and next-key locks block inserts into a locked range at REPEATABLE READ in InnoDB.
• Optimistic locking uses a version column and checks affected rows. Pessimistic locking locks first.
• When the check and the write fit in one statement, an atomic conditional UPDATE is usually the simplest correct answer.