MySQL slave stuck at 'System lock'

Question

We have Master-Slave replication but since +1h none changes are replicated because slave is stuck on 'System lock':

(dh@DB02) [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXXXXXXXXXX
                  Master_User: XXXXXXXXXXX
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: XXXXXXXX-VM011-bin.008576
          Read_Master_Log_Pos: 779580207
               Relay_Log_File: DB-02-relay-bin.000494
                Relay_Log_Pos: 377550713
        Relay_Master_Log_File: XXXXXXXX-VM011-bin.008576
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 508746847
              Relay_Log_Space: 977983642
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 4998
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
1 row in set (0.00 sec)

I have checked event on Master server and it is:

MariaDB [(none)]> show binlog events in 'XXXXXXXX-VM011-bin.008576' from 508746847 limit 1;
+---------------------------+-----------+------------+-----------+-------------+---------------------------+
| Log_name                  | Pos       | Event_type | Server_id | End_log_pos | Info                      |
+---------------------------+-----------+------------+-----------+-------------+---------------------------+
| XXXXXXXX-VM011-bin.008576 | 508746847 | Gtid       |         1 |   508746889 | BEGIN GTID 0-1-6324465194 |
+---------------------------+-----------+------------+-----------+-------------+---------------------------+
1 row in set (0.00 sec)

Slave:

• Server version: 5.5.5-10.2.13-MariaDB-log MariaDB Server
• Process list query shows only sleeping queries.

I don't see any load on replication server. Can any one help me what to do?

Answer 1

The "System lock" occurs while a transaction is in the process of acquiring the locks on tables on which it operates.

The way this is stuck is obviously a deadlock. SHOW ENGINE INNODB STATUS or SHOW PROCESSLIST might show the other thread involved in the deadlock.

You can search JIRA for similar bug reports, to see if its fixed, but there's a lot. There's a reasonable chance the latest 10.2 release or an actual supported MariaDB release will fix this processing error.