The main purpose of SELECT ... FOR UPDATE is to lock selected rows during a transaction to prevent other sessions from modifying them until the transaction completes which ensures data consistency in concurrent environments such as banking and inventory systems 1 It places row-level locks allowing others to read but not update or delete until commit or rollback 2 It requires transactions via BEGIN or autocommit=off to hold locks across queries 3 Common use cases include preventing double bookings managing financial transfers and avoiding overselling 4 Locks should be used carefully by keeping transactions short committing promptly and acquiring locks in consistent order to avoid performance issues and deadlocks.

When you see SELECT ... FOR UPDATE, it’s doing something a bit more serious than a regular SELECT. Its main purpose? To lock the selected rows so no one else can modify them until your transaction is done.

This is especially important in systems where multiple users or processes might be trying to access or change the same data at the same time — like when handling inventory, banking transactions, or booking seats.

Prevents Other Sessions from Modifying Rows

The biggest thing SELECT ... FOR UPDATE does is place a row-level lock on the data you’re looking at. That means:

• Other transactions can still read the data (depending on your isolation level).
• But they can't UPDATE or DELETE those rows until your transaction either commits or rolls back.

For example, imagine two people trying to book the last seat on a flight at the same time. Without locking, both could check availability and think it's available. With SELECT ... FOR UPDATE, the first session locks the seat row, so the second one has to wait and will see the updated (already booked) status.

Works Within Transactions

You need to be inside a transaction for this to work properly. That means starting with BEGIN or being in autocommit=off mode. If you run SELECT ... FOR UPDATE outside of a transaction block, the lock usually gets released immediately after the query finishes.

So if you're using this in an app, make sure your database driver isn’t running each query in its own auto-committed transaction.

Common Use Cases

Here are some real-world cases where SELECT ... FOR UPDATE comes in handy:

• Banking systems: When deducting money from one account and adding it to another, you want to make sure the balance doesn’t change mid-calculation.
• Inventory management: Prevent overselling by locking product stock levels while processing an order.
• Queue processing: Multiple workers pulling tasks from a queue should not pick the same task.

Let’s say you have a table called orders and you want to process only unprocessed orders. You might do something like:

BEGIN;

SELECT * FROM orders WHERE processed = false ORDER BY id LIMIT 1 FOR UPDATE;

-- Now update that row to mark it as processed
UPDATE orders SET processed = true WHERE id = 123;

COMMIT;

This ensures that even if two workers run the query at the same time, only one of them will get to process the order.

Locking Isn't Free — Use It Carefully

Locking rows can cause performance issues or even deadlocks if not handled right. Here are a few things to keep in mind:

• Keep transactions short. The longer you hold the lock, the more you block others.
• Always commit or rollback once you're done — don’t leave open transactions hanging.
• Be careful about locking multiple tables or rows in different orders across sessions — that's a recipe for deadlocks.

In PostgreSQL, for example, you’ll often see deadlock errors when two sessions are waiting for each other’s locks. So always design your queries to acquire locks in a consistent order.

That’s basically what SELECT ... FOR UPDATE is for — locking rows during a transaction to prevent conflicts. Not super complicated, but really powerful when used right.

The above is the detailed content of What is the purpose of SELECT ... FOR UPDATE?. For more information, please follow other related articles on the PHP Chinese website!