CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
kasiacode
kasiacode

Reputation: 620

Locks on postgres transactions

I am load testing my node.js application. At some point I reach state where requests are pending and my best guess it's because of a locked transaction. This is the last log statement:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

And in pg_lock I've got 4 rows with the above query which are GRANTED = true, with mode ExclusiveLock.

Where should I start looking for a bug? If in this locking request I make there are a lot of insert and update operations, should the isolation level be REPEATABLE READ? Is there any way to debug/process that kind of situations? Is there any mechanism to timeout that locks so app can be easily/automatically released and not blocking further requests?

Side question (since I'm not looking for a tool directly): are there any tools to monitor and spot that kind of situations? (I was hoping to use Munin.)

I am using nodejs 4.2.1 with express 4.13.3, sequelize 3.19.3 as Postgres 9.4.1 ORM.

Upvotes: 4

Views: 3371

Answers (2)

Smolkis
Smolkis

Reputation: 11

I encountered a similar situation where I started 5 parallell transactions requesting the same update lock, and the first one also continued with work that required more postgres calls. The entire system deadlocks, and the first transaction is listed as idle in transaction in pg_stat_activity and granted access to all locks it has requested in pg_locks.

What I think is happening;

The first transaction got the lock granted, and then finished the query. After this it drops its connection to postgres.

The following 4 transactions open a connection each and blocks on the lock, that is held by the first transaction.

Since they are blocked, the first transaction gets to execute, when it tries to connect to postgres to make a query, it gets deadlocked, because sequiezlize has run out of connections.

When I changed my sequiezlize initialisation and added more connections to the pool, default being 5, the deadlock disappears.

I am not sure who is using the 5'th connection, or if the default happens to be 4 and not 5, for some reason, but still seem to tick all the boxes.

Another solution is to use the NOWAIT option in postgres, so a transaction abort when asking for a lock and not getting it, depending on your usecase.

Hope it helps if someone else gets encounters the same issue.

Upvotes: 0

vitaly-t
vitaly-t

Reputation: 25840

Welcome to PostgreSQL transaction locks hell :)

You can spend a lot of time trying to figure out where exactly the lock happens and why. But there is a very little chance that it will help you in resolving the situation.

The general recipe for solving this kind of situations is as follows:

  • Keep your transactions size to the bare minimum required by the business logic of your application. For example, avoid same-type inserts or updates, replacing them with multi-row analogues, because query IO is expensive
  • Do not use transactions while executing only a single query that modifies data, i.e. avoid unnecessary transactions.
  • Implement error handling that can determine a transaction lock and provide a repeated attempt at executing the transaction. Logging such repeats will help you understand weak spots of your system and how to redesign it better.

Even in a well-engineered system the last step often becomes a necessity, don't let it scare you ;)

Upvotes: 4

Related Questions