使用FOR UPDATE是否会阻止其他连接向表中插入数据？

Great question—this is a super common point of confusion with row-level locks! Let's break it down in plain terms:

The Short Answer #

FOR UPDATE does not block all inserts to the target table. It only locks the specific rows returned by your SELECT query, not the entire table. Other connections can still add new rows to the table, unless those inserts run into a specific edge case with gap locks (more on that below).

The Longer Explanation #

Let's use examples to make this concrete:

• Suppose you run this query:

  SELECT * FROM customers WHERE country = 'France' FOR UPDATE;
  

  This locks all existing rows where country = 'France'. Another transaction can still:
  • Insert a new customer from Germany (no issues at all)
  • Insert a new customer from France—this new row wasn't part of your original SELECT result set, so it won't be locked by your transaction, and the insert will go through immediately.

The Exception: Gap Locks in Repeatable Read Isolation #

If you're using InnoDB with the default REPEATABLE READ isolation level, range-based FOR UPDATE queries can trigger gap locks. These locks block inserts into the "gaps" between locked rows to prevent phantom reads (where a query returns different rows in the same transaction).

For example:

• You run:

  SELECT * FROM orders WHERE order_id BETWEEN 100 AND 200 FOR UPDATE;
  

  InnoDB will lock not just the existing rows with order_id 100-200, but also the empty gaps around those rows. That means another transaction trying to insert an order with order_id = 150 (a value in the range that doesn't exist yet) will be blocked until your transaction commits or rolls back.

• If you switch to the READ COMMITTED isolation level, gap locks are disabled, so even range-based FOR UPDATE queries won't block new inserts into the specified range.

Key Takeaways #

• FOR UPDATE locks only the rows returned by your query, not the entire table.
• Most inserts to the table will work normally while your locks are held.
• Gap locks (in REPEATABLE READ) can block inserts into specific ranges, but this is an edge case tied to your database's isolation level and the type of query you're running.

内容的提问来源于stack exchange，提问作者Zampanò