MySQL: Deadlock found when trying to get lock

[f1nger]

Здравствуйте! Пытаемся настроить грид для использования с Mysql, но если параллельных сессий больше, чем 2, то возникают дедлоки.

time="2020-01-23T08:59:05Z" level=error msg="Can't reserve available node, add node to storage, [MysqlStorage/Add] insert entry in node table, Error 1213: Deadlock found when trying to get lock; try restarting transaction"
time="2020-01-23T08:59:05Z" level=error msg="Can't reserve available node, add node to storage, [MysqlStorage/Add] No rows was affected (may be limit reached)"

SHOW ENGINE INNODB STATUS говорит следующее:

LATEST DETECTED DEADLOCK
2020-01-23 08:59:06 2b52e0881700
*** (1) TRANSACTION:
TRANSACTION 5285471136, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 448624, OS thread handle 0x2b52de5c6700, query id 27854189 10.200.x.y selenium executing
INSERT INTO node (key, type, address, status, sessionId, updated, registred) SELECT 'wd-node-124378c5-f21b-4834-8b3c-4c6f1db14c7f', 'kubernetes', 'temp-value-replace-me', 'reserved', '', 1579769945, 1579769945 FROM DUAL WHERE 0 = 30 OR EXISTS (SELECT TRUE FROM node WHERE type = 'kubernetes' HAVING count() < 30)ON DUPLICATE KEY UPDATE type = 'kubernetes', address = 'temp-value-replace-me', status = 'reserved', sessionId = '', updated = 1579769945, registred = 1579769945
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table selenium.node trx id 5285471136 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 5285471134, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 448625, OS thread handle 0x2b52e0881700, query id 27854186 10.200.x.y selenium executing
INSERT INTO node (key, type, address, status, sessionId, updated, registred) SELECT 'wd-node-d8a5c452-4427-47c5-aeee-31e5f5ed700b', 'kubernetes', 'temp-value-replace-me', 'reserved', '', 1579769945, 1579769945 FROM DUAL WHERE 0 = 30 OR EXISTS (SELECT TRUE FROM node WHERE type = 'kubernetes' HAVING count() < 30)ON DUPLICATE KEY UPDATE type = 'kubernetes', address = 'temp-value-replace-me', status = 'reserved', sessionId = '', updated = 1579769945, registred = 1579769945
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table selenium.node trx id 5285471134 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33012 page no 3 n bits 72 index PRIMARY of table selenium.node trx id 5285471134 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
WE ROLL BACK TRANSACTION (1)

Конфиг грида:

 {
  "logger": {
    "level": "debug"
  },
  "db": {
      "implementation": "mysql",
      "connection": "selenium:secret@(mysql-hostname:3306)/selenium?tx_isolation=SERIALIZABLE&parseTime=true&interpolateParams=true"
  },
  "grid": {
    "client_type": "selenium",
    "port": 4444,
    "strategy_list": [
      {
        "type": "kubernetes",
        "limit": 30,
        "params": {
          "namespace":"selenium",
          "pod_creation_timeout": "5m"
        },
        "node_list": [
          {
            "params": {
              "image":"selenium/standalone-chrome:3.141.59-zinc",
              "port": "4444"
            },
            "capabilities_list": [
              {
                "browserName": "chrome",
                "browserVersion": "79",
                "platform": "LINUX"
              }
            ]
          }
        ]
      }
    ],
    "busy_node_duration": "15m",
    "reserved_node_duration": "2m"
  }

Версия jsonwire-grid последняя, собранная через https://github.com/qa-dev/jsonwire-grid/blob/master/Dockerfile

Подскажите, пожалуйста, что делаем не так? Может быть, есть требования к версии MySQL?

[andrskom]

А какую версию mysql вы используете? Что бы попробовать воспроизвести. Быть может какие-то спецефические настройки?

[f1nger]

Спасибо за быстрый ответ! Пробовали docker image: mysql:5.6, docker image: mysql:5.7 и AWS RDS 5.6.44. Без специфических настроек. Результат везде одинаковый.

[andrskom]

Ок, постараюсь до конца недели глянуть.