What sequence of row locks takes place in the case of a SELECT ... INNER JOIN ... FOR UPDATE and how to avoid deadlock?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
15 views
Percona MySQL 5.7.21-20

The table schema
-- 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), IDX_JOIN INDEX 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

by | 15 views

1 Answer

0 like 0 dislike
What sequence of row locks takes place in the case of a SELECT ... INNER JOIN ... FOR UPDATE

The first process begins to run MT4_USERS SELECT...... FOR UPDATE. In MT4_USERS locit, for example, 1, 2... 3
At the same time, the second process starts to execute INSERT MT4_TRADES...... MT4_USERS. In MT4_USERS locit, for example, 3... 2
First waiting for the unlock for 3, unlock 2 second waits. A deadlock can occur.

how to avoid deadlock?

First, make sure that autocommit is off.
Second, in both queries to explicitly specify ORDER BY... MT4_USERS.id ASC

Or another way:
CREATE TEMPORARY TABLE ... SELECT ...; UPDATE ...
In this case, you can throw SELECT... FOR UPDATE
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
3 answers
asked Apr 20, 2019 by neonox
0 like 0 dislike
2 answers
110,608 questions
257,187 answers
0 comments
40,796 users