What is SELECT FOR UPDATE? What is it used for, have you worked with this construct?

SELECT FOR UPDATE is an SQL construct that locks the selected rows of a table for updating within the current transaction. This is necessary to prevent conflicts during concurrent access and to ensure data integrity. When you execute SELECT FOR UPDATE, other transactions cannot modify or lock these rows until your transaction is completed.

Example of usage:

BEGIN;
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- process order
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

This approach is often used in ERP systems for proper order processing to prevent simultaneous modifications of the same data by different users.