Description:
At some point mysql deadlock and my current connected threads are locked for ever in the showprocess list. This lock the replication too.
This happened twince in the last week. And to restart the mysql server I have to Kill -9 the mysql process then restart the server.
When this occured, I have no info at all in the Mysql logs, neither in Linux logs.
The query in the "how to repeat" section if issued very often, but for a reason or another at some point this lock the mysql process.
Here is the output of the show processlist when mysql deadlocked.
mysql> show processlist;
+------+-------------+---------------------------+---------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+---------------------------+---------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 1 | system user | | NULL | Connect | 389478 | Waiting for master to send event | NULL |
| 2 | system user | | cohlive | Connect | 9140 | update | INSERT INTO
COH_PlayerStats
SET
TeamID = 33194, gametype = 1, race = 1, wins = 0 |
| 7 | cohlive | ls09:4087 | cohlive | Query | 9144 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 8 | cohlive | ls09:4095 | cohlive | Query | 9138 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 9 | cohlive | ls09:4103 | cohlive | Query | 9140 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 10 | cohlive | ls10:3764 | cohlive | Query | 9140 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 11 | cohlive | ls09:4111 | cohlive | Query | 9137 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 12 | cohlive | ls10:3772 | cohlive | Query | 9128 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 13 | cohlive | ls10:3773 | cohlive | Query | 9133 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 14 | cohlive | ls11:4981 | cohlive | Query | 9138 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 16 | cohlive | ls11:4989 | cohlive | Query | 9140 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 17 | cohlive | ls10:3781 | cohlive | Query | 9133 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 619 | cohlive | ls08:2771 | cohlive | Query | 9133 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 620 | cohlive | ls08:2781 | cohlive | Query | 9133 | Sending data | SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l |
| 1320 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+-------------+---------------------------+---------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)
Thanks for your help
How to repeat:
I'm not sure how to reproduce this but running this query very often with multiple thread could be the way to reproduce this.
Here is the query:
SELECT
CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.lastUpdated, CPS.teamID,
(
SELECT COUNT(*) FROM COH_PlayerStats CPS2 WHERE CPS2.race = CPS.race AND CPS2.gametype = CPS.gametype
) as totalCount,
(
SELECT
COUNT(*)
FROM
COH_PlayerStats CPS3
WHERE
CPS3.race = CPS.race
AND CPS3.gametype = CPS.gametype
AND (
CPS3.rating > CPS.rating
OR (
CPS3.rating = CPS.rating
AND(
CPS3.lastUpdated > CPS.lastUpdated
OR (
CPS3.lastUpdated = CPS.lastUpdated
AND CPS3.TeamID < CPS.TeamID
)
)
)
)
) as rankingCount,
RP.id
FROM
(
SELECT
RP.personalTeamID, RP.id
FROM
RelicProfile RP
WHERE
RP.personalTeamID IS NOT NULL AND RP.id IN (47201, 44540, 29297, 37189, 41439, 39255, 45490, 47145, 34595, 47020, 47066, 47185, 40294, 47147, 34067, 39816, 47126, 30076, 39468, 42662, 43141, 45164, 45035, 42481, 42788, 47151, 47081, 43153, 45290, 45536, 45907, 45281, 39889, 47198, 37732, 46794, 46239, 43210, 37051, 27857, 33751, 46890, 29755, 45501, 5549, 43609, 42514, 39680, 47089, 31969, 9260, 47109, 28644, 42627, 47056, 47087, 40311, 47205, 44777, 46242, 44602, 47206, 46334, 47196, 46814, 37467, 47142, 40702, 20210, 10187, 47158, 35846, 29923, 41217, 43024, 34931, 40365, 30499)
) RP
JOIN COH_PlayerStats CPS ON CPS.TeamID = RP.personalTeamID
WHERE
CPS.gametype <> 0
ORDER BY
RP.id ASC,
CPS.rating DESC,
CPS.lastUpdated DESC,
CPS.wins DESC,
CPS.loses ASC,
CPS.streak ASC,
CPS.race ASC,
CPS.gametype ASC;
Description: At some point mysql deadlock and my current connected threads are locked for ever in the showprocess list. This lock the replication too. This happened twince in the last week. And to restart the mysql server I have to Kill -9 the mysql process then restart the server. When this occured, I have no info at all in the Mysql logs, neither in Linux logs. The query in the "how to repeat" section if issued very often, but for a reason or another at some point this lock the mysql process. Here is the output of the show processlist when mysql deadlocked. mysql> show processlist; +------+-------------+---------------------------+---------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+-------------+---------------------------+---------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ | 1 | system user | | NULL | Connect | 389478 | Waiting for master to send event | NULL | | 2 | system user | | cohlive | Connect | 9140 | update | INSERT INTO COH_PlayerStats SET TeamID = 33194, gametype = 1, race = 1, wins = 0 | | 7 | cohlive | ls09:4087 | cohlive | Query | 9144 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 8 | cohlive | ls09:4095 | cohlive | Query | 9138 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 9 | cohlive | ls09:4103 | cohlive | Query | 9140 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 10 | cohlive | ls10:3764 | cohlive | Query | 9140 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 11 | cohlive | ls09:4111 | cohlive | Query | 9137 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 12 | cohlive | ls10:3772 | cohlive | Query | 9128 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 13 | cohlive | ls10:3773 | cohlive | Query | 9133 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 14 | cohlive | ls11:4981 | cohlive | Query | 9138 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 16 | cohlive | ls11:4989 | cohlive | Query | 9140 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 17 | cohlive | ls10:3781 | cohlive | Query | 9133 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 619 | cohlive | ls08:2771 | cohlive | Query | 9133 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 620 | cohlive | ls08:2781 | cohlive | Query | 9133 | Sending data | SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.l | | 1320 | root | localhost | NULL | Query | 0 | NULL | show processlist | +------+-------------+---------------------------+---------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ 15 rows in set (0.00 sec) Thanks for your help How to repeat: I'm not sure how to reproduce this but running this query very often with multiple thread could be the way to reproduce this. Here is the query: SELECT CPS.gametype, CPS.race, CPS.wins, CPS.loses, CPS.streak, CPS.drops, CPS.rating, CPS.lastUpdated, CPS.teamID, ( SELECT COUNT(*) FROM COH_PlayerStats CPS2 WHERE CPS2.race = CPS.race AND CPS2.gametype = CPS.gametype ) as totalCount, ( SELECT COUNT(*) FROM COH_PlayerStats CPS3 WHERE CPS3.race = CPS.race AND CPS3.gametype = CPS.gametype AND ( CPS3.rating > CPS.rating OR ( CPS3.rating = CPS.rating AND( CPS3.lastUpdated > CPS.lastUpdated OR ( CPS3.lastUpdated = CPS.lastUpdated AND CPS3.TeamID < CPS.TeamID ) ) ) ) ) as rankingCount, RP.id FROM ( SELECT RP.personalTeamID, RP.id FROM RelicProfile RP WHERE RP.personalTeamID IS NOT NULL AND RP.id IN (47201, 44540, 29297, 37189, 41439, 39255, 45490, 47145, 34595, 47020, 47066, 47185, 40294, 47147, 34067, 39816, 47126, 30076, 39468, 42662, 43141, 45164, 45035, 42481, 42788, 47151, 47081, 43153, 45290, 45536, 45907, 45281, 39889, 47198, 37732, 46794, 46239, 43210, 37051, 27857, 33751, 46890, 29755, 45501, 5549, 43609, 42514, 39680, 47089, 31969, 9260, 47109, 28644, 42627, 47056, 47087, 40311, 47205, 44777, 46242, 44602, 47206, 46334, 47196, 46814, 37467, 47142, 40702, 20210, 10187, 47158, 35846, 29923, 41217, 43024, 34931, 40365, 30499) ) RP JOIN COH_PlayerStats CPS ON CPS.TeamID = RP.personalTeamID WHERE CPS.gametype <> 0 ORDER BY RP.id ASC, CPS.rating DESC, CPS.lastUpdated DESC, CPS.wins DESC, CPS.loses ASC, CPS.streak ASC, CPS.race ASC, CPS.gametype ASC;