In critical environments like banking, healthcare, and online retail, ensuring safe data modifications is crucial to prevent data corruption and maintain system integrity. PostgreSQL offers a robust solution for this with its row-level locking mechanism, which ensures that the data being modified is protected from concurrent changes. One key feature of PostgreSQL is the SELECT FOR UPDATE clause, which locks the selected rows against concurrent updates. In this blog, we will explore how to implement the SELECT FOR UPDATE clause in PostgreSQL and discuss its real-world use cases.

PostgreSQL’s row-level locking mechanism

Row-level locking mechanism in PostgreSQL means that when a row of data is being changed or accessed by one user, it’s protected from being changed by another user at the same time. This helps prevent conflicts and ensures that data modifications are done safely and accurately.

SELECT FOR UPDATE clause

The SELECT FOR UPDATE clause in PostgreSQL is a powerful tool for managing concurrency and ensuring data integrity in real-world scenarios. When used in a SQL query, SELECT FOR UPDATE locks the selected rows, preventing other transactions from modifying or deleting them until the current transaction is complete. Lets try to understand this with a real word example

In an online ticket booking system, the SELECT FOR UPDATE clause plays a crucial role in ensuring a smooth and reliable user experience. When a user selects a ticket and begins the booking process, SELECT FOR UPDATE locks that specific ticket in the database, preventing other users from purchasing it concurrently. This mechanism ensures that the ticket remains reserved for the user until they complete the booking process. Without this safeguard, there’s a risk of double bookings or conflicts where multiple users attempt to purchase the same ticket simultaneously. By employing SELECT FOR UPDATE, the system maintains data integrity and prevents such issues.

Let’s delve into how SELECT FOR UPDATE ensures data consistency during ticket booking.

CREATE TABLE users (
     user_id SERIAL PRIMARY KEY,
     username VARCHAR(100) NOT NULL,
     email VARCHAR(100) UNIQUE NOT NULL);

CREATE TABLE event_tickets (
     ticket_id SERIAL PRIMARY KEY,
     seat_number VARCHAR(100) NOT NULL,
     status VARCHAR(20) DEFAULT 'available',
     user_id INT REFERENCES users(user_id));

INSERT INTO users (username, email) VALUES
     ('user1', '[email protected]'),
     ('user2', '[email protected]');

INSERT INTO event_tickets (seat_number) VALUES
     ('A1'),
     ('A2'),
     ('A3');

This code sets up a ticket booking system in PostgreSQL. It creates two tables: “users” with user IDs, usernames, and unique emails, and “tickets” with auto-generated IDs, seat numbers (text format), a status (default “available”), and a foreign key referencing the user ID, ensuring tickets belong to existing users. Sample data is then inserted into both tables

Let’s jump into action! Imagine a user starting a transaction to grab a ticket for that upcoming event.

postgres=# BEGIN ;
BEGIN
postgres=*# SELECT * FROM event_tickets WHERE seat_number = 'A1' and status = 'available' FOR UPDATE;
ticket_id | seat_number | status | user_id 
-----------+-------------+-----------+---------
1 | A1 | available | 
(1 row)

Before committing the booking and marking the ticket as booked, let’s simulate a conflict by opening another PostgreSQL session and attempting to book the same ticket.

postgres=# UPDATE event_tickets SET status = 'booked', user_id = 1 WHERE seat_number= 'A1';
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while updating tuple (0,1) in relation "event_tickets" 

Our query encountered a lock and waited for the first transaction to finish. We had to terminate it to avoid blocking further.

Time to finalize the booking! Let’s update the ticket status to ‘booked’ and commit our transaction to make it official.

postgres=*# UPDATE event_tickets SET status = 'booked', user_id = 1 WHERE seat_number = 'A1';
UPDATE 1
postgres=# COMMIT;

postgres=# select * from event_tickets;
ticket_id | seat_number | status | user_id 
-----------+-------------+-----------+---------
2 | A2 | available | 
3 | A3 | available | 
1 | A1 | booked | 1
(3 rows)

NOWAIT

The NOWAIT option with SELECT FOR UPDATE, allows a query to immediately return an error if it cannot acquire the necessary locks. Without NOWAIT, the query would wait until the required locks become available, potentially causing delays or deadlocks. NOWAIT provides a way to handle concurrency more efficiently, allowing applications to handle lock conflicts in real-time rather than waiting indefinitely for resources to become available.

postgres=# SELECT * FROM event_tickets WHERE seat_number = 'A1' AND status = 'available' FOR UPDATE nowait;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

SKIP LOCKED

The SKIP LOCKED option in SQL, particularly in the context of SELECT FOR UPDATE, allows a query to bypass rows that are already locked by other transactions. This feature is useful in scenarios where you want to process only rows that are available for modification without waiting for locked rows to become available.

Assume that the first user is booking seat number A1 in his transaction block 

postgres=# BEGIN;
BEGIN

postgres=*# SELECT * FROM tickets WHERE status = 'available' and seat_number = 'A1' FOR UPDATE SKIP LOCKED;
 ticket_id | seat_number |  status   | user_id 
-----------+-------------+-----------+---------
         1 | A1          | available |        
(1 row)

And when 2nd user tries to see the available seats he will only be able to see A2 and A3 because A1 is locked 

postgres=*# SELECT * FROM tickets WHERE status = 'available' FOR UPDATE SKIP LOCKED;
 ticket_id | seat_number |  status   | user_id 
-----------+-------------+-----------+---------
         2 | A2          | available |        
         3 | A3          | available |        
(2 rows)

Advantages of using SELECT FOR UPDATE

Data Integrity: Prevents data inconsistencies and conflicts by ensuring that only one transaction can modify selected rows at a time.

Concurrency Control: Helps manage concurrent access to data, reducing the likelihood of race conditions and ensuring transaction consistency.

Predictable Behavior: Guarantees that modifications are made in a controlled manner, minimizing the chances of unexpected outcomes.

Disadvantages of using SELECT FOR UPDATE

Concurrency Limitation: While SELECT FOR UPDATE ensures data integrity, it can also lead to contention if transactions frequently need to modify the same rows simultaneously, potentially reducing system performance.

Lock Duration: The locking mechanism can cause delays for other transactions waiting to access the locked rows, leading to potential bottlenecks in high-traffic systems.

Deadlock Risk: Improper usage or long-held locks can increase the risk of deadlocks, where transactions are unable to proceed due to conflicting resource locks.

Subscribe to the Stormatics Newsletter

Subscribe to our newsletter, Stormatics Spotlight, for exclusive insights and community-driven expertise and get access to all things PostgreSQL every month.