CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5postgresql-9.5database-deadlocks
Surya
Surya

Reputation: 2699

Deadlocks in PostgreSQL when running a simple UPDATE

update cities set cdb_data = NULL, updated_at = now() where cities.id = 1;

We loop through cities and update cities with cdb_data as a part of rails code, however we keep getting the below error.

ActiveRecord::StatementInvalid: PG::TRDeadlockDetected: ERROR:  deadlock detected
DETAIL:  Process 26741 waits for ShareLock on transaction 2970537161; blocked by process 26818.
Process 26818 waits for ShareLock on transaction 2970537053; blocked by process 26741.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (39,15) in relation "cities"
UPDATE "cities" SET "cdb_data" = $1, "updated_at" = $2 WHERE "cities"."id" = $3

Ruby code that updates the city object

    city              = City.find_or_create_by(uuid: city_data['uuid'])
    city.name         = city_data['name']
    city.state_id     = city_data['state_id']
    city.cdb_data     = city_data
    city.save

I am clueless about to which record this error is happening and why? Even with the production dump on local or in staging, this doesn't seem to happen. Any help would be much appreciated.

I am running the server on heroku so I am not really sure I could see the postgres logs.

Upvotes: 0

Views: 2156

Answers (2)

Surya
Surya

Reputation: 2699

To find what is locking the update query, one could use

SELECT pg_blocking_pids(<pid of the query that is locked>);

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246878

Two such transactions can easily deadlock.

To avoid that problem make sure that when you “loop through the cities”, you always do so in the same order, using something like:

FOR c IN
   SELECT * FROM city
   WHERE /* whatever */
   ORDER BY city.id
LOOP
   /* perform the update */
END LOOP;

Upvotes: 2

Related Questions