From: | Steve Clark <steve(dot)clark(at)netwolves(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: deadlock error - version 8.4 on CentOS 6 |
Date: | 2016-10-28 14:15:27 |
Message-ID: | dcba5e01-0a88-8f77-8d2d-bb516983e20d@netwolves.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/28/2016 09:48 AM, Tom Lane wrote:
> Steve Clark <steve(dot)clark(at)netwolves(dot)com> writes:
>> 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"
> That kicked the autovacuum off the table, but it didn't help because you
> still had a deadlock condition afterwards:
>
>> 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
>> So I feel pretty confident this is the issue. I guess I should retry the update in my application.
> Retrying might be a usable band-aid, but really this is an application
> logic error. The code that is trying to do "lock table t_unit in
> exclusive mode" must already hold some lower-level lock on t_unit, which
> is blocking whatever the "update t_unit_status_log" command wants to do
> with t_unit. Looks like a classic lock-strength-upgrade mistake to me.
>
> regards, tom lane
>
Oops - I forgot there is another process that runs every minute and takes about 1 second to run that does an
exclusive lock on t_unit and t_unit_status_log.
I only see this error maybe once or twice a day, so I am thinking of waiting 1 second and retrying when I see this error.
Thoughts?
--
Stephen Clark
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-10-28 14:25:31 | Re: deadlock error - version 8.4 on CentOS 6 |
Previous Message | Tom Lane | 2016-10-28 14:06:48 | Re: WHERE ... IN condition and multiple columns in subquery |