Какая последовательность блокировок строк происходит в случае SELECT … INNER JOIN … FOR UPDATE и как избежать deadlock?

Question

[mr_blond97]

Percona MySQL 5.7.21-20

Схемы таблиц

-- Memory temporary table TQueue
CREATE TEMPORARY TABLE IF NOT EXISTS TQueue (
    ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    QUEUE_STATUS enum ('ADDED', 'PROCESSED', 'SUCCESS', 'ERROR') NOT NULL DEFAULT 'ADDED',
    QUEUE_TIMEOUT datetime NOT NULL,
    ACTION enum ('INSERT', 'DELETE', 'UPDATE') NOT NULL,
    REPORT_ID tinyint(4) UNSIGNED NOT NULL,
    LOGIN int(11) NOT NULL,
    `GROUP` char(16) NOT NULL,
    ENABLE int(11) NOT NULL,
    ENABLE_CHANGE_PASS int(11) NOT NULL,
    ENABLE_READONLY int(11) NOT NULL,
    ENABLE_OTP int(11) NOT NULL,
    PASSWORD_PHONE char(32) NOT NULL,
    NAME char(128) NOT NULL,
    COUNTRY char(32) NOT NULL,
    CITY char(32) NOT NULL,
    STATE char(32) NOT NULL,
    ZIPCODE char(16) NOT NULL,
    ADDRESS char(128) NOT NULL,
    LEAD_SOURCE char(32) NOT NULL,
    PHONE char(32) NOT NULL,
    EMAIL char(48) NOT NULL,
    COMMENT char(64) NOT NULL,
    ID_DOCUMENT char(32) NOT NULL,
    STATUS char(16) NOT NULL,
    REGDATE datetime NOT NULL,
    LASTDATE datetime NOT NULL,
    LEVERAGE int(11) NOT NULL,
    AGENT_ACCOUNT int(11) NOT NULL,
    TIMESTAMP int(11) NOT NULL,
    BALANCE double NOT NULL,
    PREVMONTHBALANCE double NOT NULL,
    PREVBALANCE double NOT NULL,
    CREDIT double NOT NULL,
    INTERESTRATE double NOT NULL,
    TAXES double NOT NULL,
    SEND_REPORTS int(11) NOT NULL,
    MQID int(10) UNSIGNED NOT NULL,
    USER_COLOR int(11) NOT NULL,
    EQUITY double NOT NULL,
    MARGIN double NOT NULL,
    MARGIN_LEVEL double NOT NULL,
    MARGIN_FREE double NOT NULL,
    CURRENCY char(16) NOT NULL,
    API_DATA blob DEFAULT NULL,
    MODIFY_TIME datetime NOT NULL,
    PRIMARY KEY (ID),
    INDEX IDX_JOIN USING BTREE (LOGIN, REPORT_ID)
  )
  ENGINE = MEMORY;


CREATE TABLE `MT4_USERS` (
  ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    REPORT_ID tinyint(4) UNSIGNED NOT NULL,
    LOGIN int(11) NOT NULL,
    `GROUP` char(16) NOT NULL,
    ENABLE int(11) NOT NULL,
    ENABLE_CHANGE_PASS int(11) NOT NULL,
    ENABLE_READONLY int(11) NOT NULL,
    ENABLE_OTP int(11) NOT NULL,
    PASSWORD_PHONE char(32) NOT NULL,
    NAME char(128) NOT NULL,
    COUNTRY char(32) NOT NULL,
    CITY char(32) NOT NULL,
    STATE char(32) NOT NULL,
    ZIPCODE char(16) NOT NULL,
    ADDRESS char(128) NOT NULL,
    LEAD_SOURCE char(32) NOT NULL,
    PHONE char(32) NOT NULL,
    EMAIL char(48) NOT NULL,
    COMMENT char(64) NOT NULL,
    ID_DOCUMENT char(32) NOT NULL,
    STATUS char(16) NOT NULL,
    REGDATE datetime NOT NULL,
    LASTDATE datetime NOT NULL,
    LEVERAGE int(11) NOT NULL,
    AGENT_ACCOUNT int(11) NOT NULL,
    TIMESTAMP int(11) NOT NULL,
    BALANCE double NOT NULL,
    PREVMONTHBALANCE double NOT NULL,
    PREVBALANCE double NOT NULL,
    CREDIT double NOT NULL,
    INTERESTRATE double NOT NULL,
    TAXES double NOT NULL,
    SEND_REPORTS int(11) NOT NULL,
    MQID int(10) UNSIGNED NOT NULL,
    USER_COLOR int(11) NOT NULL,
    EQUITY double NOT NULL,
    MARGIN double NOT NULL,
    MARGIN_LEVEL double NOT NULL,
    MARGIN_FREE double NOT NULL,
    CURRENCY char(16) NOT NULL,
    API_DATA blob DEFAULT NULL,
    MODIFY_TIME datetime NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE KEY IDX_LOGIN_REPORT_ID (`LOGIN`,`REPORT_ID`)
) 
ENGINE=InnoDB 
ROW_FORMAT=COMPRESSED

Данные выбираются из очереди в memory table TQueue
Далее мне нужно произвести insert в таблицу MT4_USERS. Существующие строки необходимо обновить, поэтому я использую ON DUPLICATE KEY UPDATE
Для того, что бы не произошел deadlock я пытаюсь взять exclusive lock, до выполнения INSERT ON DUPLICATE KEY UPDATE

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- hold exclusive lock on MT4_USERS table
SELECT 
u.* 
FROM 
TQueue q 
INNER JOIN MT4_USERS u USING (LOGIN, REPORT_ID) 
FOR UPDATE;

-- insert to MT4_USERS form TQueue ON DUPLICATE KEY UPDATE
INSERT MT4_USERS (REPORT_ID, LOGIN, `GROUP`, ENABLE …)
SELECT
    REPORT_ID, LOGIN, `GROUP`, ENABLE, ENABLE_CHANGE_PASS …
FROM TQueue
ORDER BY ID ASC
ON DUPLICATE KEY UPDATE
    `GROUP` = VALUES(`GROUP`),
    ENABLE = VALUES(ENABLE),
    ENABLE_CHANGE_PASS = VALUES(ENABLE_CHANGE_PASS),
    …
COMMIT;

В идеале, я хочу взять exclusive блокировку на строки таблицы MT4_USERS, которые соответствуют строкам таблицы TQueue, по полям (LOGIN, REPORT) для того что бы выполнить INSERT ON DUPLICATE KEY UPDATE без deadlock.
Но я поймал deadlock в параллельной транзакции. Она производит те же самые действия, но с таблицей MT4_TRADES. Однако дополнительно производит LEFT JOIN MT4_USERS

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- hold exclusive lock on MT4_TRADES table
SELECT
t.*
FROM
TQueue q
INNER JOIN MT4_TRADES t USING (TICKET, REPORT_ID)
FOR UPDATE;

-- insert to MT4_TRADES form TQueue ON DUPLICATE KEY UPDATE
INSERT MT4_TRADES (REPORT_ID, TICKET, LOGIN, SYMBOL, …)
SELECT
q.REPORT_ID, q.TICKET, q.LOGIN, q.SYMBOL, …
FROM
TQueue q
LEFT JOIN MT4_USERS mu USING(REPORT_ID, LOGIN)
ORDER BY
q.ID ASC
ON DUPLICATE KEY UPDATE
LOGIN = VALUES(LOGIN)
,SYMBOL = VALUES(SYMBOL)
,DIGITS = VALUES(DIGITS)
,…
COMMIT;

SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-05-05 00:24:35 0x7fd53e5a6700
*** (1) TRANSACTION:
TRANSACTION 178168806, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 1136, 45 row lock(s)
MySQL thread id 17661615, OS thread handle 140555520485120, query id 4591798647 event_scheduler Sending data
SELECT u.* FROM TQueue q INNER JOIN MT4_USERS u USING (LOGIN, REPORT_ID) FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 876643 page no 46339 n bits 952 index IDX_LOGIN_REPORT_ID of table `Developer`.`MT4_USERS` trx id 178168806 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 178168783, ACTIVE 3 sec starting index read
mysql tables in use 6, locked 4
8870 lock struct(s), heap size 1138896, 22372 row lock(s), undo log entries 1876
MySQL thread id 17661574, OS thread handle 140553850873600, query id 4591797640 event_scheduler
INSERT MT4_TRADES
  (
    REPORT_ID, TICKET, LOGIN, SYMBOL, DIGITS, CMD, VOLUME, OPEN_TIME, OPEN_PRICE, SL, TP, CLOSE_TIME,
    EXPIRATION, REASON, CONV_RATE1, CONV_RATE2, COMMISSION, COMMISSION_AGENT, SWAPS, CLOSE_PRICE,
    PROFIT, TAXES, COMMENT, INTERNAL_ID, MARGIN_RATE, `TIMESTAMP`, MAGIC, GW_VOLUME, GW_OPEN_PRICE,
    GW_CLOSE_PRICE, MODIFY_TIME, CURRENCY, DELIMER, RATE, TICKET_STATUS
  )
  SELECT
    q.REPORT_ID, q.TICKET, q.LOGIN, q.SYMBOL, q.DIGITS, q.CMD, q.VOLUME, q.OPEN_TIME, q.OPEN_PRICE, q.SL, q.TP, q.CLOSE_TIME,
    q.EXPIRATION, q.REASON, q.CONV_RATE1, q.CONV_RATE2, q.COMMISSION, q.COMMISSION_AGENT, q.SWAPS, q.CLOSE_PRICE,
    q.PROFIT, q.TAXES, q.COMMENT, q.INTERNAL_ID, q.MARGIN_RATE, q.`TIMESTAMP`, q.MAGIC, q.GW_VOLUME, q.GW_OPEN_PRICE,
    q.GW_CLOSE_PRICE, q.MODIFY_TIME, mu.CURRENCY, mu.DELIMER, IF(mu.CURRENCY = 'USD', 1, SearchOfRate(q.REPORT_ID, mu.CURRENCY, 'USD', q.MODIFY_TIME, -1)), IF(q.`
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 876643 page no 46339 n bits 952 index IDX_LOGIN_REPORT_ID of table `Developer`.`MT4_USERS` trx id 178168783 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 876643 page no 46338 n bits 952 index IDX_LOGIN_REPORT_ID of table `Developer`.`MT4_USERS` trx id 178168783 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

Какая последовательность блокировок строк происходит в случае SELECT … INNER JOIN … FOR UPDATE и как избежать deadlock?

Answer 1

[Boris Köln]

Какая последовательность блокировок строк происходит в случае SELECT … INNER JOIN … FOR UPDATE

Первый процесс начинает выполнять SELECT... MT4_USERS... FOR UPDATE. В MT4_USERS лочит, например, 1, 2... 3
В это же время второй процесс начинает выполнять INSERT MT4_TRADES... MT4_USERS.... В MT4_USERS лочит, например, 3... 2
Первый ждет анлока 3, второй ждет анлока 2. Дедлок.

как избежать deadlock?

Во-первых, убедиться, что autocommit выключен.
Во-вторых, в обоих запросах явно указать ... ORDER BY MT4_USERS.id ASC

Или другой способ:
CREATE TEMPORARY TABLE ... SELECT ...; UPDATE ...
В этом случае можно выкинуть SELECT... FOR UPDATE

Comments

[mr_blond97]

«Во-первых, убедиться, что autocommit выключен.»
я использую явное указание начала/конца транзакции «START TRANSACTION…COMMIT». Зачем в этом случае ставить SET autocommit = 0?

«Во-вторых, в обоих запросах явно указать ... ORDER BY MT4_USERS.id ASC»

-- hold exclusive lock on MT4_USERS table
SELECT 
   u.* 
FROM 
   TQueue q 
   INNER JOIN MT4_USERS u USING (LOGIN, REPORT_ID)
ORDER BY
   u.ID ASC
FOR UPDATE;

-- insert to MT4_TRADES form TQueue ON DUPLICATE KEY UPDATE
INSERT MT4_TRADES (REPORT_ID, TICKET, LOGIN, SYMBOL, …)
SELECT
   q.REPORT_ID, q.TICKET, q.LOGIN, q.SYMBOL, …
FROM
   TQueue q
   LEFT JOIN MT4_USERS mu USING(REPORT_ID, LOGIN)
ORDER BY
   mu.ID ASC
ON DUPLICATE KEY UPDATE
   LOGIN = VALUES(LOGIN)
   ,SYMBOL = VALUES(SYMBOL)
   ,DIGITS = VALUES(DIGITS)
   ,…

Что дает сортировка в моем случае? Правильно написал запрос?

«Или другой способ:
CREATE TEMPORARY TABLE ... SELECT ...; UPDATE ...
В этом случае можно выкинуть SELECT... FOR UPDATE»

Таблица TQueue является TEMPORARY TABLE, зачем мне создавать еще одну?

Можете написать пример запроса без взятия exclusive lock?
«Первый процесс начинает выполнять SELECT... MT4_USERS... FOR UPDATE. В MT4_USERS лочит, например, 1, 2... 3
В это же время второй процесс начинает выполнять INSERT MT4_TRADES... MT4_USERS.... В MT4_USERS лочит, например, 3... 2
Первый ждет анлока 3, второй ждет анлока 2. Дедлок.»
Я правильно понимаю, что происходит вот так:
— В первой транзакции происходит взятие exclusive lock для строчек 1 (x), 2 (x), … (x), 3 (транзакция 2 уже поставила share lock на эту строку. жду освобождения от share lock, что бы взять x lock). Следующие строки не будут обработаны, пока транзакция не получит желаемую exclusive lock на 3-й строке
— Во второй транзакции происходит взятие share lock для строчек 3 (s), … (s) 2 (первая транзакция уже взяла exclusive lock на этой строке, жду освобождения от exclusive lock, что бы взять share lock). Следующие строки не будут обработаны, пока транзакция не получит желаемую share lock на 2-й строке

1-я транзакция 2-я транзакция
(row 1) exclusive lock (row 3) share lock
(row 2) exclusive lock
(row 2) wait hold share lock…
(row 3) wait hold exclusive lock…

Может быть тогда необходимо во второй транзакции получать exclusive lock вместо share lock? добавив в выражение

FOR UPDATE
INSERT MT4_TRADES (REPORT_ID, TICKET, LOGIN, SYMBOL, …)
SELECT
   q.REPORT_ID, q.TICKET, q.LOGIN, q.SYMBOL, …
FROM
   TQueue q
   LEFT JOIN MT4_USERS mu USING(REPORT_ID, LOGIN)
ORDER BY
   q.ID ASC
FOR UPDATE
ON DUPLICATE KEY UPDATE
   LOGIN = VALUES(LOGIN)
   ,SYMBOL = VALUES(SYMBOL)
   ,DIGITS = VALUES(DIGITS)
   ,…

взятие одинаковой exclusive блокировки для таблицы MT4_USERS поможет?

[Boris Köln]

Что дает сортировка в моем случае?

Делать одинаковый порядок лока строчек, чтобы избежать дед-лока. Пример есть в моем предыдущем ответе.

Таблица TQueue является TEMPORARY TABLE, зачем мне создавать еще одну?

Чтобы во втором запросе не было лока MT4_USERS