The SELECT ... FOR UPDATE statement is used in SQL to lock the selected rows in a table, ensuring that no other transaction can modify or delete them until the transaction is completed. This is typically used in scenarios where data consistency is critical, such as banking or inventory systems.
Here’s an example of its syntax:
Syntax:
SELECT *
FROM table_name
WHERE condition
FOR UPDATE;
Explanation:
FOR UPDATE: Locks the selected rows for the duration of the transaction.
WHERE condition: Specifies the rows to lock. If omitted, all rows in the table will be locked.
Example:
Imagine a table accounts:
If you want to lock the row where account_id = 1:
BEGIN; -- Start a transaction
SELECT *
FROM accounts
WHERE account_id = 1
FOR UPDATE;
-- Perform your update here
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
COMMIT; -- End the transaction
Key Points:
Lock Behavior: Other transactions trying to update or delete the locked rows will wait until the current transaction completes (or timeout occurs).
Deadlock: Be cautious to avoid deadlocks if multiple transactions are locking rows.
Database Support: Supported by most relational databases like PostgreSQL, Oracle, MySQL (with InnoDB), and SQL Server (with equivalent locking hints).
Top comments (0)