Search Postgresql Archives

Re: deadlock error - version 8.4 on CentOS 6

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10/28/2016 09:48 AM, Tom Lane wrote:
Steve Clark <steve.clark@xxxxxxxxxxxxx> 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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux