Mysql插入锁等待超时超过-自动增量

Question

我的应用程序出现问题，导致MySQL表由于插入时间过长而被锁定，在查看在线文章之后，它似乎与自动增量有关，如下所示-

Python，它插入数据(不幸的是，每次行，因为我需要在以后的插入中引用自动递增id )

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])

上面每2分钟运行一次相同的数据，并且应该只插入非重复项，因为MySQL会因为唯一的ucid_takeofftime索引而拒绝重复。

MYSQL信息，cb_flights表-

  `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;

现在从Python代码插入到表中，有时需要超过60秒。我认为这可能与创建表上的锁的自动增量有关，如果是的话，我正在寻找解决办法。

无敌信息-

innodb_autoinc_lock_mode    2
innodb_lock_wait_timeout    50

缓冲器的消耗可达70%左右。

感谢您对此的任何帮助，无论是从应用程序端还是从MySQL端。

编辑添加cb_kills表的create语句，该语句也与inserts一起使用，但据我所见，没有问题，这是对第一个答案的评论的回应。

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;

Answer 1

我想在这里介绍一些我努力寻找解决这个问题的方法。我不是MySQL方面的专家，但我认为这些步骤可以帮助任何想找出为什么他有锁等待超时的人。

因此，我所采取的故障排除步骤如下-

1-检查是否可以在MySQL慢速日志中找到锁定表的相关查询。通常，可以找到运行时间很长的查询，也可以使用下面的信息和后面的查询锁定查询。

# 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-上面应该提供了一些关于服务器上正在发生的事情以及可能等待很长一段时间的线索。接下来，我运行了以下3个查询，以确定正在使用的是什么：

• 检查正在运行的进程列表-

show full processlist;

当前正在使用的

• 检查表-

show open tables where in_use>0;

• 检查正在运行的事务-

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;

3.以上两个步骤应提供足够的信息，说明查询锁定表的情况。在我的例子中，我有一个运行insert into <different table> select from <my locked table>的SP，当它插入到一个完全不同的表时，由于选择操作花费了很长时间，这个查询锁定了我的表。为了解决这个问题，我将SP更改为使用临时表，现在虽然查询还没有完全优化，但我的表上没有锁。

在这里添加在临时表上运行SP的方式，以便进行异步聚合更新。

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

希望这对遇到这个问题的人有帮助。

Answer 2

您可以检查自动提交是否设置为1，这会强制提交每一行并禁用它使其更快。

不要提交每一个插入，而是尝试批量插入。

为此，您应该检查https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html

做一些类似的事情

data = [
('city 1', 'MAC', 'district 1', 16822),
('city 2', 'PSE', 'district 2', 15642),
('city 3', 'ZWE', 'district 3', 11642),
('city 4', 'USA', 'district 4', 14612),
('city 5', 'USA', 'district 5', 17672),
]

sql = "insert into city(name, countrycode, district, population) 
VALUES(%s, %s, %s, %s)"

number_of_rows = cursor.executemany(sql, data)
db.commit()