Understanding Postgres Locks and Managing Concurrent Transactions

In the realm of database management systems, concurrency control is paramount to ensure data integrity and consistency, particularly in multi-user environments. PostgreSQL, one of the most popular open-source relational database systems, employs a sophisticated locking mechanism to handle concurrent transactions effectively. In this article, we’ll delve into understanding Postgres locks, specifically focusing on RowShareLock and RowExclusiveLock, along with practical examples and techniques to manage transactional conflicts.

Postgres Locks Overview

PostgreSQL utilizes a multi-level locking mechanism to control access to data at various granularities, including tables, rows, and individual data pages. These locks prevent conflicting operations from occurring simultaneously, thus ensuring data integrity. Two fundamental lock types are RowShareLock and RowExclusiveLock.

• RowShareLock: Also known as a shared lock, it allows multiple transactions to read a row concurrently but prevents any transaction from modifying it. This lock type ensures that read operations do not interfere with each other.
• RowExclusiveLock: This exclusive lock mode allows a transaction to both read and modify a row exclusively. It prevents other transactions from accessing the same row for reading or writing until the lock is released.

Example: SharedLock in Action

Let’s illustrate the concept of shared locks with a practical example involving two concurrent transactions.

Consider a scenario where two transactions are attempting to update the balance of a bank account concurrently:

• Transaction 1: Updates the account balance by adding $1000.
• Transaction 2: Updates the account balance by adding $3000.

However, due to the absence of appropriate locks, both transactions might interfere with each other, leading to incorrect results and potential data inconsistencies.

Checking Lock Activity

Before delving into the example, let’s understand how to check lock activity in PostgreSQL using the following SQL command:

SELECT l.pid, l.mode, l.fastpath, l.waitstart, l.relation, p.pid AS process_id, p.usename AS username, p.query AS query, l.locktype AS lock_type, l.granted AS lock_granted, t.relname AS table_name, c.relname AS index_name, l.page AS locked_page, l.tuple AS locked_tuple, l.virtualxid AS virtual_transaction_id
FROM pg_locks l
JOIN pg_stat_activity p ON l.pid = p.pid
LEFT JOIN pg_class t ON l.relation = t.oid
LEFT JOIN pg_class c ON l.relation = c.oid AND l.page IS NOT NULL AND l.tuple IS NOT NULL
ORDER BY l.pid, l.locktype, l.locktype;

This query provides detailed information about active locks, including the process ID, lock mode, granted status, and associated transaction details.

Understanding the Example

Let’s walk through the example scenario step by step to understand how shared locks influence transaction outcomes:

1. Transaction 1: Begins by selecting the account balance for updating. It acquires a RowShareLock and ExclusiveLock on the row, ensuring that subsequent reads can occur concurrently but updates are exclusive.
2. Transaction 2: Similarly, starts by selecting the account balance. It also acquires a RowShareLock and ExclusiveLock on the row. However, due to the existing lock held by Transaction 1, it waits for the shared lock to be released.
3. Transaction 1: Proceeds to update the account balance. It acquires a RowExclusiveLock in addition to the previously held locks. This prevents Transaction 2 from acquiring an exclusive lock, causing it to wait.
4. Transaction 2: After Transaction 1 commits and releases its locks, Transaction 2 proceeds with the update. It acquires the necessary locks and successfully updates the balance.
5. Conclusion: Despite the absence of appropriate locks initially, both transactions are eventually able to update the account balance without conflicting, ensuring data integrity and consistency.

Managing Concurrent Transactions

To address the issues encountered in the initial example, it’s essential to use appropriate lock modes in transactions to prevent conflicts and ensure data consistency.

• Use FOR UPDATE clause in transactions that involve both reading and updating data. This ensures exclusive access to rows, preventing concurrent modifications.
• Conversely, use FOR SHARE clause in transactions that only involve reading data. This allows concurrent reads but prevents updates until the transaction is complete.

By utilizing these lock modes effectively, you can minimize transaction conflicts and maintain data integrity in PostgreSQL.

In conclusion, understanding Postgres locks and employing appropriate locking strategies are crucial for managing concurrent transactions effectively. By utilizing shared and exclusive locks judiciously, you can ensure data consistency and prevent conflicts in multi-user database environments.

-- Use this to get lock details

SELECT l.pid, l.mode, l.fastpath, l.waitstart, l.relation, p.pid AS process_id, p.usename AS username, p.query AS query, l.locktype AS lock_type, l.granted AS lock_granted, t.relname AS table_name, c.relname AS index_name, l.page AS locked_page, l.tuple AS locked_tuple, l.virtualxid AS virtual_transaction_id
FROM pg_locks l JOIN pg_stat_activity p ON l.pid = p.pid LEFT JOIN pg_class t ON l.relation = t.oid LEFT JOIN pg_class c ON l.relation = c.oid AND l.page IS NOT NULL AND l.tuple IS NOT null
where l.pid != 28152
ORDER BY l.pid, l.locktype, l.locktype;

pid  |mode            |fastpath|waitstart                    |relation|process_id|username|query                                                 |lock_type    |lock_granted|table_name  |index_name|locked_page|locked_tuple|virtual_transaction_id|
-----+----------------+--------+-----------------------------+--------+----------+--------+------------------------------------------------------+-------------+------------+------------+----------+-----------+------------+----------------------+
31740|RowExclusiveLock|true    |                             |   16654|     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|relation     |true        |account_pkey|          |           |            |                      |
31740|RowExclusiveLock|true    |                             |   16649|     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|relation     |true        |account     |          |           |            |                      |
31740|RowShareLock    |true    |                             |   16649|     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|relation     |true        |account     |          |           |            |                      |
31740|RowShareLock    |true    |                             |   16654|     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|relation     |true        |account_pkey|          |           |            |                      |
31740|ExclusiveLock   |false   |                             |        |     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|transactionid|true        |            |          |           |            |                      |
31740|ShareLock       |false   |2024-03-17 15:11:13.688 +0530|        |     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|transactionid|false       |            |          |           |            |                      |
31740|ExclusiveLock   |true    |                             |        |     31740|postgres|UPDATE account SET balance = 10000 + 3000 WHERE id = 1|virtualxid   |true        |            |          |           |            |6/1051                |
45228|RowShareLock    |true    |                             |   16649|     45228|postgres|SHOW search_path                                      |relation     |true        |account     |          |           |            |                      |
45228|RowShareLock    |true    |                             |   16654|     45228|postgres|SHOW search_path                                      |relation     |true        |account_pkey|          |           |            |                      |
45228|ExclusiveLock   |false   |                             |        |     45228|postgres|SHOW search_path                                      |transactionid|true        |            |          |           |            |                      |
45228|ExclusiveLock   |true    |                             |        |     45228|postgres|SHOW search_path                                      |virtualxid   |true        |            |          |           |            |7/830                 |

Connection 1
begin;
SELECT balance account WHERE id=1; // $balance = 10000
UPDATE account SET balance = $balance + 1000 WHERE id = 1;

Connection 2
begin;
SELECT balance account WHERE id=1; // $balance = 10000
UPDATE account SET balance = $balance + 3000 WHERE id = 1;

Connection 1:
commit;

Connection 2:
Commit;

Finally,Records Updates
id, amount
1, 13000

Step-1: Connection 1
begin;
SELECT balance account WHERE id=1 for SHARE; // $balance = 10000
-- first txn took RowShareLock and ExclusiveLock. 

Step-2: Connection 2
begin;
SELECT balance account WHERE id=1 for SHARE; // $balance = 10000
-- second txn took RowShareLock and ExclusiveLock. 

Step-3: Connection 1:
UPDATE account SET balance = $balance + 1000 WHERE id = 1; // 11000
-- first txn tried to get RowExclusiveLock, but get ShareLock becuase of txn2.

Step-4: Connection 2:
UPDATE account SET balance = $balance + 3000 WHERE id = 1;
-- Deadlock
-- Second txn could not take a RowExclusiveLock because of conflict with first txn.
-- Process 45228 waits for ShareLock on transaction 1095; blocked by process 31740.

Step-5: Connection 1:
Commit;

Step-5: Connection 2:
Rollback;

Finally,Records Updates
id, amount
1, 11000

Still, We are having same problem. We required 14000 as a result but got an exception from Postgres that second txn failed to complete.

Step-1: Connection 1
begin;
SELECT balance account WHERE id=1 for UPDATE; // $balance = 10000
-- first txn took RowShareLock and ExclusiveLock.

Step-2: Connection 2
begin;
SELECT balance account WHERE id=1 for UPDATE; // -- Locked
-- second txn tries to get RowShareLock and ExclusiveLock and gets ShareLock.

Step-3: Connection 1:
UPDATE account SET balance = $balance + 1000 WHERE id = 1; // 11000
-- first txn get RowExclusiveLock.

Step-4: Connection 1:
Commit;

Step-5: Connection 2:
-- locks released $balance 
UPDATE account SET balance = $balance + 3000 WHERE id = 1;
Commit;

Finally,Records Updates
id, amount
1, 14000