I'm having an issue with my application causing MySQL table to be locked due to inserts which take a long time, after reviewing online articles, it seems like it's related to auto increment, info below -
Python that inserts data (row at a time unfortunately as I need the auto incremented id for reference in future inserts) -
for i, flightobj in stats[ucid]['flight'].items():
flight_fk = None
# Insert flights
try:
with mysqlconnection.cursor() as cursor:
sql = "insert into cb_flights(ucid,takeoff_time,end_time,end_event,side,kills,type,map_fk,era_fk) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(sql, (
ucid, flightobj['start_time'], flightobj['end_time'], flightobj['end_event'], flightobj['side'],
flightobj['killnum'], flightobj['type'], map_fk, era_fk))
mysqlconnection.commit()
if cursor.lastrowid:
flight_fk = cursor.lastrowid
else:
flight_fk = 0
except pymysql.err.ProgrammingError as e:
logging.exception("Error: {}".format(e))
except pymysql.err.IntegrityError as e:
logging.exception("Error: {}".format(e))
except TypeError as e:
logging.exception("Error: {}".format(e))
except:
logging.exception("Unexpected error:", sys.exc_info()[0])
The above runs every 2 minutes on the same data and is supposed to insert only non duplicates as the MySQL would deny duplicates due to the unique ucid_takeofftime index.
MYSQL info, cb_flights table -
`pk` int(11) NOT NULL AUTO_INCREMENT,
`ucid` varchar(50) NOT NULL,
`takeoff_time` datetime DEFAULT NULL,
`end_time` datetime DEFAULT NULL,
`end_event` varchar(45) DEFAULT NULL,
`side` varchar(45) DEFAULT NULL,
`kills` int(11) DEFAULT NULL,
`type` varchar(45) DEFAULT NULL,
`map_fk` int(11) DEFAULT NULL,
`era_fk` int(11) DEFAULT NULL,
`round_fk` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `ucid_takeofftime` (`ucid`,`takeoff_time`),
KEY `ucid_idx` (`ucid`) /*!80000 INVISIBLE */,
KEY `end_event` (`end_event`) /*!80000 INVISIBLE */,
KEY `side` (`side`)
) ENGINE=InnoDB AUTO_INCREMENT=76023132 DEFAULT CHARSET=utf8;
Now inserts into the table from the Python code, can take sometimes over 60 seconds. I beleive it might be related to the auto increment that is creating the lock on the table, if so, I'm looking for a workaround.
innodb info -
innodb_autoinc_lock_mode 2
innodb_lock_wait_timeout 50
buffer is used up to 70% more or less.
Appreciate any assistance with this, either from application side or MySQL side.
EDIT Adding the create statement for the cb_kills table which is also used with inserts but without an issue as far as I can see, this is in response to the comment on the 1st answer.
CREATE TABLE `cb_kills` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`time` datetime DEFAULT NULL,
`killer_ucid` varchar(50) NOT NULL,
`killer_side` varchar(10) DEFAULT NULL,
`killer_unit` varchar(45) DEFAULT NULL,
`victim_ucid` varchar(50) DEFAULT NULL,
`victim_side` varchar(10) DEFAULT NULL,
`victim_unit` varchar(45) DEFAULT NULL,
`weapon` varchar(45) DEFAULT NULL,
`flight_fk` int(11) NOT NULL,
`kill_id` int(11) NOT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `ucid_killid_flightfk_uniq` (`killer_ucid`,`flight_fk`,`kill_id`),
KEY `flight_kills_fk_idx` (`flight_fk`),
KEY `killer_ucid_fk_idx` (`killer_ucid`),
KEY `victim_ucid_fk_idx` (`victim_ucid`),
KEY `time_ucid_killid_uniq` (`time`,`killer_ucid`,`kill_id`),
CONSTRAINT `flight_kills_fk` FOREIGN KEY (`flight_fk`) REFERENCES `cb_flights` (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=52698582 DEFAULT CHARSET=utf8;
I want to put in here some of the ways I worked on finding a solution to this problem. I'm not an expert in MySQL but I think these steps can help anyone looking to find out why he has lock wait timeouts.
So the troubleshooting steps I took are as follows -
1- Check if I can find in the MySQL slow log the relevant query that is locking my table. Usually it's possible to find queries that run a long time and also locks with the info below and the query right after it
# Time: 2020-01-28T17:31:48.634308Z
# User@Host: @ localhost [::1] Id: 980397
# Query_time: 250.474040 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 195738
2- The above should give some clue on what's going on in the server and what might be waiting for a long time. Next I ran the following 3 queries to identify what is in use:
show full processlist;
show open tables where in_use>0;
SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;
3- The above 2 steps should give enough information on which query is locking the tables. in my case here I had a SP that ran an insert into <different table> select from <my locked table>, while it was inserting to a totally different table, this query was locking my table due to the select operation that took a long time.
To work around it, I changed the SP to work with temporary tables and now although the query is still not completely optimized, there are no locks on my table.
Adding here how I run the SP on temporary tables for async aggregated updates.
CREATE DEFINER=`username`@`%` PROCEDURE `procedureName`()
BEGIN
drop temporary table if exists scheme.temp1;
drop temporary table if exists scheme.temp2;
drop temporary table if exists scheme.temp3;
create temporary table scheme.temp1 AS select * from scheme.live1;
create temporary table scheme.temp2 AS select * from scheme.live2;
create temporary table scheme.temp3 AS select * from scheme.live3;
create temporary table scheme.emptytemp (
`cName1` int(11) NOT NULL,
`cName2` varchar(45) NOT NULL,
`cName3` int(11) NOT NULL,
`cName4` datetime NOT NULL,
`cName5` datetime NOT NULL,
KEY `cName1` (`cName1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT into scheme.emptytemp
select t1.x,t2.y,t3.z
from scheme.temp1 t1
JOIN scheme.temp2 t2
ON t1.x = t2.x
JOIN scheme.temp3 t3
ON t2.y = t3.y
truncate table scheme.liveTable;
INSERT into scheme.liveTable
select * from scheme.emptytemp;
END
Hope this helps anyone that encounters this issue