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]

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