What is the purpose of SELECT FOR UPDATE? What level of locking does it provide? Are there weaker options?

SELECT FOR UPDATE is used in SQL to lock selected rows for the duration of a transaction to prevent other transactions from modifying them. This ensures data consistency when working with the database concurrently.

The lock level is exclusive (write lock) at the row level. Other transactions cannot modify or lock these rows until the current transaction is completed.

Lighter lock options include:

• SELECT FOR SHARE (in some DBMS) — locks rows for reading, allowing other transactions to read but not modify them.
• Using transaction isolation levels, such as READ COMMITTED or REPEATABLE READ, which provide different guarantees without explicit locks.

Example:

BEGIN;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- processing
COMMIT;