Mysql insert lock wait timeout exceeded - auto increment

Question

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;

Morrtz · Accepted Answer

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:

check process list on which process are running -

show full processlist;

check tables in use currently -

show open tables where in_use>0;

check running transactions -

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 select from , 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

Mysql insert lock wait timeout exceeded - auto increment

Answers (2)

Related Questions