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', 'user1@example.com'), ('user2', 'user2@example.com'); 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 blockpostgres=# 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)
