Recently i'm facing to a recurrent Deadlocks, like 1 to 5 per minute on a 1K concurrent users. The query below is called an average of 2 times per minute and per user.
I don't know how to solve that since this query is built to handle possible race conditions when 2+ different players (or same player 2+ times) execute the Insert query.
Of course, I can handle race conditions by unique keys but is unmanageable on a peak of 100+ users searching for a free spot. Provably it will cause 91+ rejections by dupplicate key.
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 100539324, ACTIVE 0 sec inserting
mysql tables in use 5, locked 5
LOCK WAIT 23 lock struct(s), heap size 3520, 111 row lock(s), undo log entries 1
MySQL thread id 6782, OS thread handle 2460, query id 138188765 localhost 127.0.0.1 root Creating sort index
INSERT INTO app_tables_players (user_id, game_id, seat_number, username, state, folded) SELECT 597, a.id, b.id AS seat_number, 'some_username', 'temp', false FROM (SELECT id FROM app_tables_games WHERE table_id = 6 AND seats_total > seats_taken AND id NOT IN (SELECT game_id FROM app_users_state WHERE user_id = 597)) AS a, `app_temp_players_9` AS b WHERE b.id NOT IN (SELECT seat_number FROM app_tables_players WHERE game_id = a.id) ORDER BY a.id ASC LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 75 page no 51 n bits 1120 index seat_number_game_id of table `nodejs`.`app_tables_players` trx id 100539324 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 964 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d8f6; asc ;;
2: len 4; hex 006b437b; asc kC{;;
*** (2) TRANSACTION:
TRANSACTION 100539325, ACTIVE 0 sec setting auto-inc lock, thread declared inside InnoDB 4743
mysql tables in use 5, locked 5
22 lock struct(s), heap size 3520, 151 row lock(s)
MySQL thread id 6702, OS thread handle 11428, query id 138188764 localhost 127.0.0.1 root Creating sort index
INSERT INTO app_tables_players (user_id, game_id, seat_number, username, state, folded) SELECT 613, a.id, b.id AS seat_number, 'some_username2', 'tmp', false FROM (SELECT id FROM app_tables_games WHERE table_id = 14 AND seats_total > seats_taken AND id NOT IN (SELECT game_id FROM app_users_state WHERE user_id = 613)) AS a, `app_temp_players_9` AS b WHERE b.id NOT IN (SELECT seat_number FROM app_tables_players WHERE game_id = a.id) ORDER BY a.id ASC LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 75 page no 51 n bits 1120 index seat_number_game_id of table `nodejs`.`app_tables_players` trx id 100539325 lock mode S locks gap before rec
Record lock, heap no 18 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 06; asc ;;
1: len 4; hex 0008d6e4; asc ;;
2: len 4; hex 006b5a47; asc kZG;;
Record lock, heap no 97 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d97e; asc ~;;
2: len 4; hex 006b5d1e; asc k] ;;
Record lock, heap no 160 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 05; asc ;;
1: len 4; hex 0008d93e; asc >;;
2: len 4; hex 006b4cb3; asc kL ;;
Record lock, heap no 181 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d92f; asc /;;
2: len 4; hex 006b5c0b; asc k\ ;;
Record lock, heap no 267 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d9b2; asc ;;
2: len 4; hex 006b5b9a; asc k[ ;;
Record lock, heap no 272 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 05; asc ;;
1: len 4; hex 0008d999; asc ;;
2: len 4; hex 006b5d43; asc k]C;;
Record lock, heap no 307 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008da33; asc 3;;
2: len 4; hex 006b6182; asc ka ;;
Record lock, heap no 346 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008da38; asc 8;;
2: len 4; hex 006b5fbd; asc k_ ;;
Record lock, heap no 530 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 05; asc ;;
1: len 4; hex 0008d9b2; asc ;;
2: len 4; hex 006b544e; asc kTN;;
Record lock, heap no 556 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 05; asc ;;
1: len 4; hex 0008d97e; asc ~;;
2: len 4; hex 006b62f3; asc kb ;;
Record lock, heap no 629 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d999; asc ;;
2: len 4; hex 006b5f41; asc k_A;;
Record lock, heap no 804 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 05; asc ;;
1: len 4; hex 0008da33; asc 3;;
2: len 4; hex 006b60bc; asc k` ;;
Record lock, heap no 906 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d93e; asc >;;
2: len 4; hex 006b4988; asc kI ;;
Record lock, heap no 962 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d928; asc (;;
2: len 4; hex 006b5b03; asc k[ ;;
Record lock, heap no 964 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 04; asc ;;
1: len 4; hex 0008d8f6; asc ;;
2: len 4; hex 006b437b; asc kC{;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `nodejs`.`app_tables_players` trx id 100539325 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
Query causing the problem:
INSERT INTO app_tables_players
(user_id,
game_id,
seat_number)
SELECT
597,
a.id,
b.id AS seat_number
FROM
(SELECT id
FROM
app_tables_games
WHERE
table_id = 14
AND seats_total > seats_taken
AND id NOT IN (
SELECT game_id
FROM app_users_state
WHERE user_id = 597
)
) AS a,
app_temp_players_9 AS b
WHERE
b.id NOT IN (
SELECT seat_number
FROM app_tables_players
WHERE game_id = a.id
)
ORDER BY a.id ASC
LIMIT 1;
id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, INSERT, app_tables_players, , ALL, , , , , , ,
1, PRIMARY, app_tables_games, , ref, table_id,table_id_seats_taken_seats_total, table_id_seats_taken_seats_total, 4, const, 24, 33.33, Using where; Using index; Using temporary; Using filesort
1, PRIMARY, b, , index, , id, 1, , 9, 100.00, Using where; Using index; Using join buffer (Block Nested Loop)
4, DEPENDENT SUBQUERY, app_tables_players, , index_subquery, seat_number_game_id,game_id_user_id,game_id,seat_number,game_id_state, seat_number_game_id, 6, func,func, 2, 100.00, Using where; Using index
3, SUBQUERY, app_users_state, , ref, game_id_user_id,user_id,game_id, user_id, 4, const, 4, 100.00,
NOTE: Sometimes the Deadlock occurs with the query above and:
DELETE FROM app_users_states WHERE game_id = some_id AND user_id = some_id
After some more research about all the deadlocks faced in this application i saw that there was 2-3 different types or situations.
One of them was a lock mode AUTO-INC waiting
So, 1st transaction have the autoincrement id column locked and waiting for index 'X' and 2nd transaction have a lock on index 'X' and waiting for the AUTO-INC lock.
Since i need the id column to get the lastInsertId and use it later in a SELECT, there was a MUST to have this autoincrement column. To solve that i created a known UUID value in my application and stored as binary(16) instead of autoincrement intid.
Second and thid problem was related to INSERT on SELECT in different or the same table in the same query. Like (siple example);
INSERT INTO tableA some_colX (SELECT some_colY FROM tableA WHERE some_colZ = 1 LIMIT 1)
This SELECTs used a lock mode S locks gap before rec but the INSERT requires a lock_mode X locks gap before rec insert intention waiting. In my INSERT i was doing SELECTs in 3 different tables and in other part of the application there was some others UPDATEs or others INSERTs, so lots of deadlocks everywhere.
To solve part of that i changed (some of) the SELECTs inside de INSERT statement adding FOR UPDATE since this, sets a IX lock instead of S lock, preventing that other query get first the X lock in this record/gap
I realised later, that there was another deadlock in my application. What you think? Another different (and the latest) INSERT on SELECT query in other completly different tables.
From 1 to 5 deadlocks per minute to 1 deadlock per.. 10 minutes or more.
So, as my personal opinion (use case), dont use INSERT and SELECT in the same query. Use procedures, transactions... And check if the SELECTs are using the properly Indexes.
Sorry for my English :)
Good luck.
UPDATE:
Chganged 1 Index in the main table involved into de INSERT and 1 Index on one of the SELECT tables. Result: by now, 0 deadlocks in 2 hours