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_航班表-

  `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信息-

innodb_autoinc_lock_mode    2
innodb_lock_wait_timeout    50

缓冲区的使用率高达70%左右

感谢应用程序端或MySQL端对此提供的任何帮助

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

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

您可以检查autocommit是否设置为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()

Answer 2

我想在这里介绍一些我在寻找解决这个问题的方法。我不是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，当它插入到一个完全不同的表时，由于select操作花费了很长时间，这个查询锁定了我的表。 为了解决这个问题，我将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

希望这能帮助任何遇到这个问题的人