I have a mysql innodb table named "radacct". This table contains the users internet usage records like upload,download,account id etc(table schema below). The radacct table gets updated at random intervals with data sent by routers. We also use this table to calculate the total bandwidth of the internet user and the bandwidth calculation query(select) takes about 3-4 seconds. The problem arises when there is concurrent execution of the bandwidth calculation query and the update query from routers competing for locks. Is this due to RepeatableRead locks(table level locking) and better to use ReadCommitted isolation here ?
describe freeradius.radacct;
+----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+----------------+
| radacctid | bigint(21) | NO | PRI | NULL | auto_increment |
| acctsessionid | varchar(32) | NO | MUL | | |
| acctuniqueid | varchar(32) | NO | MUL | | |
| username | varchar(64) | NO | MUL | | |
| groupname | varchar(64) | NO | | | |
| realm | varchar(64) | YES | | | |
| nasipaddress | varchar(15) | NO | MUL | | |
| nasportid | varchar(15) | YES | | NULL | |
| nasporttype | varchar(32) | YES | | NULL | |
| acctstarttime | datetime | YES | MUL | NULL | |
| acctstoptime | datetime | YES | MUL | NULL | |
| acctsessiontime | int(12) | YES | MUL | NULL | |
| acctauthentic | varchar(32) | YES | | NULL | |
| connectinfo_start | varchar(50) | YES | | NULL | |
| connectinfo_stop | varchar(50) | YES | | NULL | |
| acctinputoctets | bigint(20) | YES | | NULL | |
| acctoutputoctets | bigint(20) | YES | | NULL | |
| calledstationid | varchar(50) | NO | | | |
| callingstationid | varchar(50) | NO | | | |
| acctterminatecause | varchar(32) | NO | | | |
| servicetype | varchar(32) | YES | | NULL | |
| framedprotocol | varchar(32) | YES | | NULL | |
| framedipaddress | varchar(15) | NO | MUL | | |
| acctstartdelay | int(12) | YES | | NULL | |
| acctstopdelay | int(12) | YES | | NULL | |
| xascendsessionsvrkey | varchar(10) | YES | | NULL | |
+----------------------+-------------+------+-----+---------+----------------+
mysql> show session variables like '%isol%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
Bandwidth calculation query
SELECT sum(acctinputoctets),sum(acctoutputoctets) from radacct
where username='davidjohnsoon' and acctstarttime
between '2015-10-11 14:10:17' and '2016-08-22 14:53:00'
Insert/Update query seen on mysql> show full processlist;
INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime,acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause,servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay)
VALUES ('260204248', 'a5b889ad247a514b', 'johnson', '','100.44.44.44', '297797794', 'Ethernet', DATE_SUB('2016-08-23 13:02:50',INTERVAL (1 + 0) SECOND), '2016-08-23 13:02:50', '1', 'RADIUS', '', '', '0' << 32 | '0', '0' << 32 | '0','','90:61:0c:1a:94:96','User-Error','Framed-User', 'PPP', '','0', '0')
the diff between RC and RR is : the select result will change in one transaction or not;
so in RR, the result will not change in one transaction, if update query occurs;
Example in RR:
session 1: 10:00:00-open a transaction;
session 1: 10:00:00-execution of the bandwidth calculation query;
session 1: 10:00:08-finish the bandwidth calculation query, result=100;
session 2: 10:00:05-update query;
session 1: 10:00:09-execution of the bandwidth calculation query;
session 1: 10:00:17-finish the bandwidth calculation query, result=100;
session 1: 10:00:18-end a transaction;
but with RC, the second bandwidth calculation query in example will get the different result, may 101;
if update query executed when the bandwidth calculation query is executing, nothing changes;
my opinion: in this case, both RC and RR are OK;