Forem

Pranav Bakare
Pranav Bakare

Posted on

1 1 1 1 1

FOR UPDATE statement

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:

  1. Lock Behavior: Other transactions trying to update or delete the locked rows will wait until the current transaction completes (or timeout occurs).

  2. Deadlock: Be cautious to avoid deadlocks if multiple transactions are locking rows.

  3. Database Support: Supported by most relational databases like PostgreSQL, Oracle, MySQL (with InnoDB), and SQL Server (with equivalent locking hints).

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More