PG::TRDeadlockDetected: ERROR: deadlock detected

Question

I am restarting 8 puma workers via bundle exec pumactl -F config/puma.rb phased-restart what works fine. Now I am getting more and more postgres errors:

PG::TRDeadlockDetected: ERROR:  deadlock detected

I found a about 50 of idle postgres processes running:

postgres: myapp myapp_production 127.0.0.1(59950) idle
postgres: myapp myapp_production 127.0.0.1(60141) idle
...

They disappear when I am running bundle exec pumactl -F config/puma.rb stop. After starting the app with bundle exec pumactl -F config/puma.rb start, I get exactly 16 idle processes. (Eight too many in my opinion.)

How can I manage these processes better? Thanks for your help!

---

Update

My puma.rb:

environment 'production'
daemonize true

pidfile 'tmp/pids/puma.pid'
state_path 'tmp/pids/puma.state'

threads 0, 1
bind 'tcp://0.0.0.0:3010'

workers 8

quiet

Answer 1

I might have found a solution to my question: I had some queries outside of my controllers (custom middleware), which seem to have caused the problem.

If you have queries outside of controllers (ActiveMailer could also cause this problem), put your code in a ActiveRecord::Base.connection_pool.with_connection block:

ActiveRecord::Base.connection_pool.with_connection do
  # code
end

ActiveRecord’s with_connection method yields a database connection from its pool to the block. When the block finishes, the connection is automatically checked back into the pool, avoiding connection leaks.

I hope this helps some of you!

Answer 2

Looks like this may be due to the database connections not getting closed on server shutdown. https://github.com/puma/puma/issues/59 A lot of people in that issue are using ActiveRecord:: ConnectionAdapters::ConnectionManagement to handle this, or you may be able to roll your own by using Puma's on_restart hook.