[Bucardo-general] Occasional deadlocks

[Paul Theodoropoulos]

We're experiencing some occasional ('rare' might be the better term) 
deadlocks, which appear to be caused by Bucardo conflicting with the 
monolithic application we run that is constantly hitting the db. By 
occasional/rare I mean perhaps a few days will pass, we'll have a 
deadlock and the master process will abort/crash and restart. Obviously 
we'd prefer not to have the minute or so of downtime as monit discovers 
the outage and restarts it.

Is this a case where just increasing the kid_deadlock_sleep would help? 
Is there an upper bounds on the sleep that would tend to make things 
wonky? I currently have it set at one second.

error as reported by our application (sorry for the obfuscation, 
customer privacy issues):

Exec: UPDATE units SET last_contact=now(), last_event='2014/12/15 
19:18:51 xxxxx uuuuu id:42 evid:3 position:xx.50' ,latitude=xx.xxxxxxxx, 
longitude=-yyy.yyyyyyyy, location_timestamp='2014/12/15 19:18:51 GMT' 
WHERE unit_id=x'zzzzzzzz'::bigint
execDB: ERROR:  deadlock detected
DETAIL:  Process 20196 waits for RowExclusiveLock on relation 17212 of 
database 16389; blocked by process 29795.
Process 29795 waits for AccessExclusiveLock on relation 108623566 of 
database 16389; blocked by process 16616.
Process 16616 waits for AccessExclusiveLock on relation 17212 of 
database 16389; blocked by process 20196.
HINT:  See server log for query details.
CONTEXT:  SQL statement "INSERT INTO int_unit_moves SELECT NEW.unit_id, 
NEW.longitude,NEW.latitude, 1, NEW.xxxx_id"
PL/pgSQL function "on_units_change" line 5 at SQL statement

And as reported by postgresql:
2014-12-15 11:19:03 PST ERROR:  deadlock detected
2014-12-15 11:19:03 PST DETAIL:  Process 20196 waits for 
RowExclusiveLock on relation 17212 of database 16389; blocked by process 
29795.
     Process 29795 waits for AccessExclusiveLock on relation 108623566 
of database 16389; blocked by process 16616.
     Process 16616 waits for AccessExclusiveLock on relation 17212 of 
database 16389; blocked by process 20196.
     Process 20196: UPDATE units SET last_contact=now(), 
last_event='2014/12/15 19:18:51 xxxxxxx uuuuu id:42 evid:3 
position:xx.50' ,latitude=xx.xxxxxxxx, longitude=-yy.yyyyyyyyy, 
location_timestamp='2014/1
2/15 19:18:51 GMT' WHERE unit_id=x'zzzzzzzzz'::bigint
     Process 29795: TRUNCATE int_unit_moves ;
     Process 16616: TRUNCATE TABLE public.int_unit_moves
2014-12-15 11:19:03 PST HINT:  See server log for query details.
2014-12-15 11:19:03 PST CONTEXT:  SQL statement "INSERT INTO 
int_unit_moves SELECT NEW.unit_id, NEW.longitude,NEW.latitude, 1, 
NEW.xxxxx_id"
     PL/pgSQL function "on_units_change" line 5 at SQL statement
2014-12-15 11:19:03 PST STATEMENT:  UPDATE units SET last_contact=now(), 
last_event='2014/12/15 19:18:51 xxxxx uuuuu id:42 evid:3 position:xx.50' 
,latitude=xx.xxxxxxx, longitude=-yyy.yyyyyyyy, location_time
stamp='2014/12/15 19:18:51 GMT' WHERE unit_id=x'zzzzzzzzzz'::bigint
2014-12-15 11:19:03 PST ERROR:  deadlock detected
2014-12-15 11:19:03 PST DETAIL:  Process 16616 waits for 
AccessExclusiveLock on relation 17212 of database 16389; blocked by 
process 29795.
     Process 29795 waits for AccessExclusiveLock on relation 108623566 
of database 16389; blocked by process 16616.
     Process 16616: TRUNCATE TABLE public.int_unit_moves
     Process 29795: TRUNCATE int_unit_moves ;
2014-12-15 11:19:03 PST HINT:  See server log for query details.
2014-12-15 11:19:03 PST STATEMENT:  TRUNCATE TABLE public.int_unit_moves

Thanks in advance!!

-- 
Paul Theodoropoulos
www.anastrophe.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20141215/27685db2/attachment.html>