What are the options for using the SELECT FOR UPDATE construct in databases?

The SELECT FOR UPDATE construct is used to lock selected rows in a database to prevent them from being modified by other transactions until the current one is completed. Main use cases include:

• Ensuring data integrity during concurrent access. For example, when updating an account balance, the relevant rows are first locked to avoid race conditions.
• Implementing application-level locking mechanisms. This guarantees that other transactions cannot modify or delete the selected records.
• Using with different locking modes. Some DBMSs allow specifying NOWAIT or SKIP LOCKED to control lock behavior.

Example in Go using a PostgreSQL database:

row := db.QueryRow("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", accountID)
// You can safely update the balance afterwards, knowing the row is locked