Re: deadlock error - version 8.4 on CentOS 6

[Steve Clark <steve.clark@xxxxxxxxxxxxx>]

On 10/28/2016 09:15 AM, Adrian Klaver wrote:

On 10/28/2016 05:28 AM, Steve Clark wrote:

Hello List,

I am occasionally seeing the following error:
ALERT  3 sqlcode=-400 errmsg=deadlock detected on line 3351

So what exactly is it doing at line 3351?

from an application written using ecpg when trying an update to the table.
Can autovacuum be causing this,
since no one else is updating this database table.

Is there more then one instance of the application running?

Thanks,

No. But I examined the pg_log/log_file and saw an error indicating it was autovacuum:


2016-10-27 09:47:02 EDT:srm2api:12968:LOG:  sending cancel to blocking autovacuum PID 12874
2016-10-27 09:47:02 EDT:srm2api:12968:DETAIL:  Process 12968 waits for ExclusiveLock on relation 955454549 of database 955447411.
2016-10-27 09:47:02 EDT:srm2api:12968:STATEMENT:  lock table t_unit_status_log in exclusive mode
2016-10-27 09:47:02 EDT::12874:ERROR:  canceling autovacuum task
2016-10-27 09:47:02 EDT::12874:CONTEXT:  automatic vacuum of table "srm2.public.t_unit_status_log"
2016-10-27 09:47:02 EDT:srm2api:9189:ERROR:  deadlock detected at character 8
2016-10-27 09:47:02 EDT:srm2api:9189:DETAIL:  Process 9189 waits for RowExclusiveLock on relation 955454549 of database 955447411; blocked by process 12968.
        Process 12968 waits for ExclusiveLock on relation 955454518 of database 955447411; blocked by process 9189.
        Process 9189: update t_unit_status_log set status_date = now ( ) , unit_active = 'y' , last_updated_date = now ( ) , last_updated_by = current_user , devices_down = $1  where unit_serial_no = $2
        Process 12968: lock table t_unit in exclusive mode

This is at the same time and same table that my application reported the error on.

So I feel pretty confident this is the issue. I guess I should retry the update in my application.

Thanks,

--
Stephen Clark