Locks in PostgreSQL part 3

Demystifying PostgreSQL Locks for Everyone: Following our previous two blog posts where we delved into table lock levels, let's now redirect our attention to the captivating realm of Row Locks. Buckle up, because we're about to take a thrilling dive into this topic! 🌟

Row Level Lock

Row-level locking is a crucial mechanism in relational databases like PostgreSQL to ensure data consistency and concurrency control. Unlike table-level locks that lock the entire table, row-level locks provide a more granular approach by allowing you to lock specific rows within a table while leaving other rows accessible for manipulation by other transactions concurrently.

FOR UPDATE LOCK:
The FOR UPDATE lock in PostgreSQL is used to explicitly lock rows in a table during a SELECT query within a transaction. This lock mode is typically employed when you want to ensure that the selected rows remain unchanged until the transaction completes, preventing other transactions from modifying or locking those rows in a conflicting way.

Let's consider a scenario involving an online ticket booking system. Imagine you have a ticketstable that stores information about available tickets for various events. You want to allow users to select and book tickets, but you need to ensure that once a user is in the process of booking, the selected tickets don't get sold to someone else until the booking is completed.

Here's how you might use the FOR UPDATE lock in this scenario:

-- Begin a transaction
BEGIN;

-- Select the available tickets for a specific event
SELECT * FROM tickets WHERE event_id = 123 AND status = 'available' FOR UPDATE;

-- Assuming user confirms the booking, update the ticket status to 'booked'
UPDATE tickets SET status = 'booked' WHERE event_id = 123 AND status = 'available';

-- Commit the transaction
COMMIT;

In this example, the SELECT ... FOR UPDATE statement locks the selected rows with the specified conditions. Other transactions attempting to select or modify the same rows will be blocked until the current transaction completes. This ensures that the user's selected tickets can't be sold to someone else during the booking process.

Here's a breakdown of the steps:

1.The transaction begins with BEGIN;.
2.The SELECT statement retrieves available tickets for a specific event and locks them using the FOR UPDATE lock. This prevents other transactions from booking the same tickets concurrently.
3.After the user confirms the booking, the UPDATE statement changes the status of the selected tickets to booked.
4.The transaction is committed using COMMIT;, releasing the locks and finalizing the changes.

FOR NO KEY UPDATE LOCK:

The FOR NO KEY UPDATE lock mode in PostgreSQL is used when you want to prevent other transactions from acquiring a FOR UPDATE lock on the same rows but without blocking other transactions that are only reading the data. This can be useful in scenarios where you need to ensure data consistency for a short period without blocking unrelated operations.

Let's consider a scenario involving an e-commerce platform where multiple users are viewing product details simultaneously. You want to display real-time information about product availability while ensuring that the data isn't modified during the viewing process.

Here's how you might use the FOR NO KEY UPDATE lock in this scenario:

-- Begin a transaction
BEGIN;

-- Select product details for a specific product ID
SELECT * FROM products WHERE product_id = 456 FOR NO KEY UPDATE;

-- Display the product details to the user

-- Commit the transaction
COMMIT;

In this example, theSELECT ... FOR NO KEY UPDATE statement locks the selected row(s) with the specified conditions, but it does not block other transactions from reading the same rows. This means that other users can continue to view the same product details concurrently, but any attempt to acquire a FOR UPDATE lock on the same rows will be blocked until the current transaction completes.

1.The transaction begins with BEGIN;.
2.The SELECT statement retrieves product details for a specific product ID and applies a FOR NO KEY UPDATE lock. This lock mode ensures that other transactions using FOR UPDATE won't be able to lock the same rows concurrently, but it doesn't block regular SELECT statements.
3.The product details are displayed to the user.
4.The transaction is committed using COMMIT;, releasing the lock and finalizing the transaction.

The key difference between FOR NO KEY UPDATE and a regular SELECTstatement is that it acquires a non-blocking lock to prevent modification by other transactions using FOR UPDATE while allowing other transactions to read the data. This can be helpful in maintaining data consistency for scenarios where immediate updates are not necessary or desirable during a short period of user interaction.

FOR SHARE LOCK:

The FOR SHARE lock mode in PostgreSQL is used to prevent other transactions from acquiring an exclusive lock (FOR UPDATE or FOR NO KEY UPDATE) on the selected rows. It allows multiple transactions to share a read lock on the same rows simultaneously, ensuring data consistency without blocking other readers.

Let's consider a scenario where you have a social media platform with posts that users can comment on. You want to allow users to view post details and comments concurrently while preventing edits or additions to comments while the post is being viewed.

Here's how you might use the FOR SHARE lock in this scenario:

-- Begin a transaction
BEGIN;

-- Select post details and comments for a specific post ID
SELECT * FROM posts WHERE post_id = 789 FOR SHARE;
SELECT * FROM comments WHERE post_id = 789 FOR SHARE;

-- Display the post and comments to the user

-- Commit the transaction
COMMIT;

In this example, the two SELECTstatements use the FOR SHARE lock mode to acquire a shared lock on the selected rows from the posts and comments tables. This means that multiple transactions can concurrently read these rows, ensuring that the data remains consistent during the user's interaction.

Here's a breakdown of the steps:

1.The transaction begins with BEGIN;.
2.The first SELECTstatement retrieves post details for a specific post ID and applies a FOR SHARE lock. This lock mode allows other transactions to also acquire a shared lock and read the same rows concurrently.
3.The second SELECTstatement retrieves comments for the same post ID and applies a FOR SHARE lock. Again, other transactions can acquire shared locks and read these rows concurrently as well.
4.The post details and comments are displayed to the user.
5.The transaction is committed using COMMIT;, releasing the shared locks and finalizing the transaction.

both FOR SHARE and FOR NO KEY UPDATE locks are mechanisms to control concurrency during reads without allowing modifications. FOR SHARE focuses on sharing read locks among multiple transactions, whereas FOR NO KEY UPDATE aims to prevent more aggressive locks (exclusive locks) while still allowing regular reads to continue. Choosing between them depends on your specific use case and the level of concurrency control you require.

FOR KEY SHARE LOCK:

In the world of PostgreSQL locks, the FOR KEY SHARE lock is like a friendly guardian that helps keep things in order while allowing everyone to do their thing. It's a bit like the FOR SHARElock, but with a more laid-back attitude, making sure that important things stay safe while still letting others take a peek.

Just like FOR SHARE it lets multiple folks look at the same info at once without causing any fuss. But the cool thing about FOR KEY SHARE is that it's smart enough to say, "Hey, no exclusive access for you!" to anyone trying to make big changes using SELECT FOR UPDATE. However, it's totally chill with letting others do their regular SELECT FOR NO KEY UPDATE.

This lock mode goes a step further by making sure that nobody messes with the important keys. It won't let anyone delete or change key stuff, but it's totally fine with other types of updates. It's like having a guard specifically for the really important bits.

When it comes to queries, FOR KEY SHARE plays nice with everyone. It happily hangs out with SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, and SELECT FOR KEY SHARE making sure they all get along and work together without any problems.

In a nutshell, the FOR KEY SHARE lock is a cool way to balance keeping things safe and letting people work together. It's a valuable tool for situations where you want to keep important data protected while still allowing everyone to be part of the action.