Bug #38188 Replication deadlock
Submitted: 16 Jul 2008 23:04 Modified: 14 Oct 2008 16:06
Reporter: Eric Thunberg Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.1.26 OS:Linux (CentOS release 5 (Final) x86_64)
Assigned to: CPU Architecture:Any
Tags: deadlock, myisam, replication

[16 Jul 2008 23:04] Eric Thunberg
Description:
I'm running a master slave replication setup and every 24-48 hours the slave will deadlock forcing me to manually kill the process and restart the server. The deadlock occurs between a SELECT statement from the application using the database and a INSERT/UPDATE statement from the replication thread. The threads cannot be killed with the KILL command from the MySQL prompt as the status changes to "killed" but they never clear. I've tried different versions of CentOS, the last 4 versions of MySQL 5.1.x, and running the setup on completely different hardware all to no avail.

Below is the top of the output from SHOW PROCESSLIST as it only allows me to log in with the last reserved admin slot, no other connections are allowed.

|      1 | system user |                    | NULL | Connect | 59992 | Waiting for master to send event | NULL                                                                                                 | 
|      2 | system user |                    | esea | Connect |  8245 | Locked                           | UPDATE servers SET esea_match_state = '3' WHERE id = 205                                             | 
|  15529 | root        | localhost          | NULL | Query   |     0 | NULL                             | show processlist                                                                                     | 
| 449534 | global      | 69.93.50.154:37703 | esea | Query   |  8229 | preparing                        | SELECT s.id AS server_id, s.host AS server_host, g.icon AS game_icon, u3.user_id AS is_playing, u.id | 

I was also able to use "mysqladmin debug" and this is the output from the lock section (I have the entire output available if there's any more information you need).

Thread database.table_name          Locked/Waiting        Lock_type

2       esea.servers                Waiting - write       Highest priority write lock
449534  esea.users                  Locked - read         Low priority read lock
449534  esea.users_buddies          Locked - read         Low priority read lock
449534  esea.servers                Locked - read         Low priority read lock
449534  esea.games                  Locked - read         Low priority read lock
449534  esea.countries              Locked - read         Low priority read lock
449534  esea.users_playing          Locked - read         Low priority read lock
449534  esea.users_online           Locked - read         Low priority read lock
449535  esea.servers                Waiting - read        Low priority read lock
449559  esea.recruit_ads_players    Locked - read         Low priority read lock
449563  esea.servers_locations      Locked - read         Low priority read lock
449563  esea.hltv_slaves            Locked - read         Low priority read lock
449609  esea.servers_maps           Locked - read         Low priority read lock

Please let me know what other information you need and thanks for looking into this.

How to repeat:
See description

Suggested fix:
Unknown
[17 Jul 2008 5:36] Sveta Smirnova
Thank you for the report.

Dead lock itself can be probably not a bug, as it can happen. But is strange what you can not use KILL. Please provide output of SHOW CREATE TABLE servers, full SELECT  statement and output of SHOW CREATE TABLE of all tables underlying for SELECT.
[17 Jul 2008 5:52] Eric Thunberg
Debug data requested by Sveta Smirnova on 7/17/2008

Attachment: mysql_debug.txt (text/plain), 10.97 KiB.

[17 Jul 2008 5:54] Eric Thunberg
As noted above, I've attached the requested information in the order you asked for it. Regarding the deadlock, I understand that they can occur I just find it strange that it's always with the replication thread.

Please let me know if there's anything else you need.
[17 Jul 2008 15:37] Eric Thunberg
It happened again last night, here is the debug output I captured.

Attachment: mysql_debug2.txt (text/plain), 21.79 KiB.

[14 Sep 2008 16:06] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.28, and inform about the results.
[14 Oct 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".