Freeradius 1.1.1: 'sqlippools' concurrency problems

[Robles Rodriguez,Alejandro]

Hi,

I'm performance-testing freeradius with rlm_sqlippools support
(an MySQL 5.0 Cluster) and have set up my SQLs in 'sqlipool.sqls'
file as follows (regarding IP address allocation):

------------------ sqlippool.sqls ----------------------
#
# This series of queries allocates an IP number
#
allocate_begin = "BEGIN"
allocate-clear = "UPDATE radippool \
                  SET SGSNIPV4Address = '', \
                      NASIPAddress = '', \
                      username = '', \
                      calling_station_id = '', \
                      expiry_time = NOW() - INTERVAL 1 SECOND \
                  WHERE calling_station_id = '%{Calling-Station-Id}'"
allocate-find = "SELECT ip_address FROM radippool \
                 WHERE pool_name = '%P' AND expiry_time < NOW() \
                 LIMIT 1 \
                 FOR UPDATE"
allocate-update = "UPDATE radippool \
                   SET calling_station_id = '%i', \
                       username = '%u', \
                       NASIPAddress = '%{NAS-IP-Address}', \
                       expiry_time = NOW() + INTERVAL '%J' SECOND, \
                       SGSNIPV4Address = '%{3GPP-SGSN-Address}' \
                   WHERE ip_address = '%I'"
allocate_commit = "COMMIT"
allocate_rollback = "ROLLBACK"
------------------------------------------------------------

  I'm using two 'radclient' that concurrently send 1000 Access-Requests
each. All of the requests get answered positively and an IP returned.
The problem is that in many instances these IP addresses are the "same"
i.e. the 'allocate-find' doesn't seem to lock the returned row properly.

  I have read that MySQL with Cluster Storage Engine uses 'READ COMMITED'
isolation level so I think it should be ok for my purposes (SQLs above).
However the locks don't seem to work.

  Has anyone noticed this behavior with similar SQL setup on MySQL or similar
Relational Database with similar isolation level and locking mechanisms? If so
do you have any advice on how to prevent the above described problem?

Thanks!

Cheers,
Alex.