Hello! I was afraid of locks when I started to manage databases. It affects the performance and reliability of the database. In this article, I will cover how to check db locks in PostgreSQL topic.

How to Check DB Locks in Postgres?

Locks are indirect indicators of database health. Locks don’t point out a database issue directly, but they are a signal of a potential issue. They might point to a worse performing, unoptimized query, or an unoptimized database. That is important to know how to check db locks in postgres to manage a database successfully.

What is a Database Lock?

Let’s assume you are trying to run an UPDATE statement on a table. Meanwhile another session tries to run a SELECT statement. If we want second session running SELECT statement to select COMMITTED data only, we should make that wait. So we are locking data until UPDATE completes and data commits.

Locking data is only letting one session to use that particular data. We say particular data because there are different types of lock: Row Lock, Table Lock, Page Lock. Different types lock different amount of data.

Lock Modes

Lock modes are not same. Even for the table level lock, there are 8 Lock modes in PostgreSQL:

Table Level Lock Modes:

• Access Share
• Row Share
• Row Exclusive
• Share Update Exclusive
• Share
• Share Row Exclusive
• Exclusive
• Access Exclusive

Only access exclusive locks blocks SELECT statements. Other locks do not prevent SELECT statement to read committed data.

Using pg_locks View

pg_locks view contains information about locks held by active sessions.

SELECT * FROM pg_locks;

After turning auto commit off i run a select insert statement:

SELECT * FROM pg_locks;

pid 37 and 233 is locking some objects, let’s check query it runs:

Let’s commit SELECT INSERT statement and check the locks again:

10 locks are gone.

You can refer to our article How to check running queries in Postgresql.

Deadlocks

Transaction A updates table X then updates table Y. Transaction B updates table Y then table X.

Transaction A will lock table X and transaction B will lock table Y.

Transaction A will wait for Transaction B to complete, Transaction B will wait for Transaction A to complete.

This situation is deadlock. Unless one of the transactions cancelled or rolled back, this can not be solved.

PostgreSQL has an internal deadlock detection mechanism, you can configure the interval time to detect deadlocks.

In postgresql.conf file, there is a parameter called deadlock_timeout, that configures deadlock check interval. Deadlock check is an expensive process, so engine doesn’t run this check each time. Postgresql assumes, deadlocks are uncommon on the production systems. So you need to design your applications, transaction and database structure carefully.

Preventing Locks

Using indexes makes run queries faster and it prevents locks via running queries faster.

Trying not to write / read data simultaneously on a table will help you to manage a system with less locks (that is a theorical suggestion, in practice it is very rare to achieve this goal)

To Sum Up

I hope you found this article on checking database locks in PostgreSQL insightful and informative. Now it’s your turn! I would love to hear your thoughts, questions, and feedback. Have you encountered challenging lock situations in your database management journey? Do you have any specific tips for preventing or resolving locks that you’d like to share with the community? Your comments and insights are incredibly valuable and can contribute to a better understanding of this critical topic. Don’t hesitate to share your experiences or reach out with any questions you may have. Your input can further enrich the discussion and help others facing similar database challenges. Thank you in advance for your contribution!

Feel free to drop a comment below and let me know your thoughts on the topic. Have you ever grappled with database locks and deadlocks in your PostgreSQL environment? Do you have any additional tips for preventing locks or managing database performance? Your feedback is highly appreciated and can benefit others navigating the complexities of database management. Whether you have inquiries about specific lock modes, deadlock detection mechanisms, or any other related queries, I’m here to address them. Your engagement can spark valuable conversations and provide clarity on database locking complexities. I eagerly await your comments and questions!

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch