Deadlocks in PostgreSQL when running a simple UPDATE

Question

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.

Answer 1

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

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