Re: deadlock error - version 8.4 on CentOS 6

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