FR 2.2.1 rlm_sqlippool / mysql 5.5.31 - deadlocks

25 Nov 2013

      Hello,

I have recently switched from ippool to sqlippool. I decided to use 
Calling-Station-Id as a pool-key:

pool-key = "%{Calling-Station-Id}"

related queries in sqlippool.conf:

allocate-find = "SELECT framedipaddress FROM ${ippool_table} WHERE 
pool_name = '%{control:Pool-Name}' AND expiry_time IS NULL ORDER BY 
RAND() LIMIT 1 FOR UPDATE"
allocate-update = "UPDATE ${ippool_table} SET nasipaddress = 
'%{NAS-IP-Address}', pool_key = '${pool-key}', expiry_time = NOW() + 
INTERVAL ${lease-duration} SECOND WHERE framedipaddress = '%I' AND 
expiry_time IS NULL"
allocate-clear = "UPDATE ${ippool_table} SET nasipaddress = '', pool_key 
= 0, expiry_time = NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND"

and radippool table structure:

CREATE TABLE `radippool` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `pool_name` varchar(30) NOT NULL,
    `framedipaddress` varchar(15) NOT NULL DEFAULT '',
    `nasipaddress` varchar(15) NOT NULL DEFAULT '',
    `expiry_time` datetime DEFAULT NULL,
    `pool_key` varchar(30) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `radippool_poolname_expire` (`pool_name`,`expiry_time`),
    KEY `framedipaddress` (`framedipaddress`),
    KEY `radippool_nasip_poolkey_ipaddress` 
(`nasipaddress`,`pool_key`,`framedipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=65025 DEFAULT CHARSET=latin1;

At the first glance everything seemed to be fine, however after some 
time, I realized that there were a number of deadlocks every now and 
then. Attached you can sample outputs from "SHOW ENGINE INNODB STATUS"

I have tried to lower the transaction isolation level to READ-COMMITED, 
but it didn't help.

Investigating the source code for rlm_sqlippool brought me to an idea 
that moving "CLEAR" query to the distinct transaction can help, because 
I can't really see any good reason to keep it in the same transaction 
with "FIND". I have made required minor changes and gave it a try: for 
two days the server is running without a single deadlock.

$ diff orig/rlm_sqlippool.c rlm_sqlippool.c
586a587,598
...
* COMMIT
     */
    sqlippool_command(data->allocate_commit, sqlsocket, instance,
              request, (char *) NULL, 0);
/*
     * BEGIN
     */
    sqlippool_command(data->allocate_begin, sqlsocket, data, request,
              (char *) NULL, 0);
/*
$ uname -a
Linux radius 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64 GNU/Linux

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using 
readline 6.2

Best Regards,

-- 
George Chelidze

Software Developer
Magticom Ltd.