[sr-dev] MySQL deadlock in RLS module

[M S]

Hi,

We have XCAP + RLS based presence setup which seems to work perfectly fine
in our dev environment, however same setup in identical production
environment gives no presence for contact list. When we do RLS subscribe,
we receive initial Notify with full contact list retrieved from XCAP
backend but aggregated presence Notify either does not come at all (most of
the time) or it comes too late (sometime and take over 3-5 minutes).

We see following error in syslogs,

--
ERROR: db_mysql [km_dbase.c:129]: db_mysql_submit_query(): ERROR: driver
error on query: Deadlock found when trying to get lock; try restarting
transaction (1213)
ERROR: <core> [db_query.c:386]: db_do_replace(): error while submitting
query
ERROR: rls [resource_notify.c:808]: rls_handle_notify(): in sql replace
--

This is quite strange since we are using db_mode = 0 in all presence
related modules e.g. presence, presence_xml, pua and rls. So ideally the
entire presence should be managed in in-memory hash tables and there should
be no DB query, right?

Tracing mysql db scoket we identified at lease following presence tables
are accessed,

- pua
- presentity
- watchers
- rls_watchers
- rls_presentity

The presence related tables are accessed / updated by both dev and
production setups but mysql deadlock appears only on production. The only
notice-able different between our dev and production setup is that on
production we have more then 25% users in contact list online (20-30 out of
80 users in contact list) while on dev setup we have too few online (5-10
out of 60 users in contact list).

So my question is,

1. What is the purpose of db_mode = 0 if presence setup is going to use db
tables any way?

2. What could be the possible cause of MySQL deadlock?

3. Is this deadlock the only possible problem for receiving no aggregate
notify at all or there may be some other reason(s)?

How can i debug these issues? These are happening ONLY in production
environment and every attempt to reproduce them in dev environment has
failed so far. :(

Platform: Debian 7.x 32bit on KVM.
Version:   Kamailio v4.4.1 rev.12b048

Thank you.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20160613/a73b171b/attachment.html>