On 10/28/16 9:27 AM, Steve Clark wrote: > 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, The problem is that you're doing: 'LOCK TABLE t_unit_status_log' If you were executing normal updates, autovacuum would be fine. Remove the exclusive lock from your code and you'll be fine. --Scott > > > > -- > Stephen Clark > -- Scott Mead Sr. Architect OpenSCG -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
