[sr-dev] [kamailio/kamailio] Usrloc race condition when db_mode is set to 2 (#2455)

[VolodyaIvanets]

Hello!

There is a ```delete from `location` where `expires`<'XYZ' AND `expires`<>'1970-01-01 03:00:00'``` query that runs before every time when insert, update or delete for location table is executed. The last query from the provided example was unsuccessful before the second last query deleted the corresponding record.

The test was made on the Kamailio v5.4.0. Usrloc module configuration is:
```
modparam("usrloc", "db_url", DATABASE_URL)
modparam("usrloc", "timer_interval", 10)
modparam("usrloc", "db_mode", 2)
modparam("usrloc", "nat_bflag", FLB_NATB)
modparam("usrloc", "db_timer_clean", 1)
```

The test phone I was using - Zoiper softphone updates registration 5 seconds before the expiration. The first registration was at 15:04:43. The following update was at 15:05:38. Following DB queries were executed:

```
529486 Query    delete from `location` where `expires`<'2020-08-24 15:04:45' AND `expires`<>'1970-01-01 03:00:00'
529485 Query    insert into `location` (`username`,`contact`,`expires`,`q`,`callid`,`cseq`,`flags`,`cflags`,`user_agent`,`received`,`socket`,`last_modified`,`ruid`,`reg_id`,`server_id`,`connection_id`,`keepalive`,`partition` ) values ('300-test','sip:300-test at 192.168.0.105:56022;rinstance=5dd76a9c6cb94169;transport=tcp','2020-08-24 15:05:43',-1.000000,'DQzsJiW2Cpjs66s88IkOMQ..',2,0,0,'Z 3.15.40006 rv2.8.20','sip:XXX.XXX.XXX.XXX:56317;transport=tcp','tcp:172.16.30.216:5060','2020-08-24 15:04:43','uloc-5f43acc8-1f59-1',0,0,1,0,0)

529486 Query    delete from `location` where `expires`<'2020-08-24 15:04:55' AND `expires`<>'1970-01-01 03:00:00'

529486 Query    delete from `location` where `expires`<'2020-08-24 15:05:05' AND `expires`<>'1970-01-01 03:00:00'

529486 Query    delete from `location` where `expires`<'2020-08-24 15:05:15' AND `expires`<>'1970-01-01 03:00:00'

529486 Query    delete from `location` where `expires`<'2020-08-24 15:05:25' AND `expires`<>'1970-01-01 03:00:00'

529486 Query    delete from `location` where `expires`<'2020-08-24 15:05:35' AND `expires`<>'1970-01-01 03:00:00'

529486 Query    delete from `location` where `expires`<'2020-08-24 15:05:45' AND `expires`<>'1970-01-01 03:00:00'
529485 Query    update `location` set `expires`='2020-08-24 15:06:38',`q`=-1.000000 ,`cseq`=3,`flags`=0,`cflags`=0,`user_agent`='Z 3.15.40006 rv2.8.20',`received`='sip:XXX.XXX.XXX.XXX:56317;transport=tcp',`path`=NULL,`socket`='tcp:172.16.30.216:5060',`methods`=NULL,`last_modified`='2020-08-24 15:05:38',`callid`='DQzsJiW2Cpjs66s88IkOMQ..',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=1,`keepalive`=0,`contact`='sip:300-test at 192.168.0.105:56022;rinstance=5dd76a9c6cb94169;transport=tcp' where `ruid`='uloc-5f43acc8-1f59-1'
```

Reducing timer_interval value to 3 seconds corrected the behavior for me. But is it possible to run ```delete from `location` where `expires`<'XYZ' AND `expires`<>'1970-01-01 03:00:00'``` query not before but after queries that manage particular registrations are executed?

Thank you!

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/2455
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-dev/attachments/20200824/7f5af6af/attachment.htm>