Readconnroute router allows starting a transaction which stops replication on slave due to 'Lock wait timeout exceeded; try restarting transaction' error

-- Running two services in maxscale

-- ReadWriteSplitService listening on port 3306

-- Read-Service listening on port 3307

[root@LAPTOP-15531O7Q ~]# maxctrl list services

┌───────────────────────┬────────────────┬─────────────┬───────────────────┬─────────────────────┐

│ Service               │ Router         │ Connections │ Total Connections │ Targets             │

├───────────────────────┼────────────────┼─────────────┼───────────────────┼─────────────────────┤

│ ReadWriteSplitService │ readwritesplit │ 0           │ 0                 │ node1, node2, node3 │

├───────────────────────┼────────────────┼─────────────┼───────────────────┼─────────────────────┤

│ Read-Service          │ readconnroute  │ 0           │ 0                 │ node2, node3        │

└───────────────────────┴────────────────┴─────────────┴───────────────────┴─────────────────────┘

-- Running SELECT... LOCK IN SHARE MODE in a transaction and not committing it via 3307 Read-only port

[root@localhost ~]# mysql -h192.168.20.26 -usbtest -pTest@123 -P3307

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 10.6.12-8-MariaDB-enterprise-log MariaDB Enterprise Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> BEGIN;

Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [test]> SELECT * FROM job_locks WHERE name = 'AUDIT_LOG' LOCK IN SHARE MODE;

+-----------+---------------------+

| name      | timestamp           |

+-----------+---------------------+

| AUDIT_LOG | 2024-01-02 17:51:54 |

+-----------+---------------------+

1 row in set (0.002 sec)

MariaDB [test]> SELECT @@hostname;

+---------------+

| @@hostname    |

+---------------+

| mariadb-slave |

+---------------+

1 row in set (0.005 sec)

-- Running an UPDATE query on 3306 port READWRITE port

[root@localhost ~]# mysql -h192.168.20.26 -usbtest -pTest@123 -A test

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 10.6.12-8-MariaDB-enterprise-log MariaDB Enterprise Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> UPDATE job_locks SET timestamp = '2024-01-02 19:00:00'  WHERE name = 'AUDIT_LOG' ;

Query OK, 1 row affected (0.013 sec)

Rows matched: 1  Changed: 1  Warnings: 0

-- Slave status on the slave

MariaDB [(none)]> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

                Slave_IO_State: Waiting for master to send event

                   Master_Host: 192.168.20.19

                   Master_User: repl

                   Master_Port: 3306

                 Connect_Retry: 60

               Master_Log_File: mariadb-master-bin.000002

           Read_Master_Log_Pos: 571

                Relay_Log_File: mariadb-slave-relay-bin.000006

                 Relay_Log_Pos: 659

         Relay_Master_Log_File: mariadb-master-bin.000002

              Slave_IO_Running: Yes

             Slave_SQL_Running: No

               Replicate_Do_DB:

           Replicate_Ignore_DB:

            Replicate_Do_Table:

        Replicate_Ignore_Table:

       Replicate_Wild_Do_Table:

   Replicate_Wild_Ignore_Table:

                    Last_Errno: 1205

                    Last_Error: Lock wait timeout exceeded; try restarting transaction

                  Skip_Counter: 0

           Exec_Master_Log_Pos: 351

               Relay_Log_Space: 1504

               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: NULL

 Master_SSL_Verify_Server_Cert: No

                 Last_IO_Errno: 0

                 Last_IO_Error:

                Last_SQL_Errno: 1205

                Last_SQL_Error: Lock wait timeout exceeded; try restarting transaction

   Replicate_Ignore_Server_Ids:

              Master_Server_Id: 100

                Master_SSL_Crl:

            Master_SSL_Crlpath:

                    Using_Gtid: No

                   Gtid_IO_Pos:

       Replicate_Do_Domain_Ids:

   Replicate_Ignore_Domain_Ids:

                 Parallel_Mode: optimistic

                     SQL_Delay: 0

           SQL_Remaining_Delay: NULL

       Slave_SQL_Running_State:

              Slave_DDL_Groups: 0

Slave_Non_Transactional_Groups: 0

    Slave_Transactional_Groups: 11

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT straight_join w.trx_mysql_thread_id waiting_thread, w.trx_id waiting_trx_id, w.trx_query waiting_query, b.trx_mysql_thread_id blocking_thread, b.trx_id blocking_trx_id, b.trx_query blocking_query, bl.lock_id blocking_lock_id, bl.lock_mode blocking_lock_mode, bl.lock_type blocking_lock_type, bl.lock_table blocking_lock_table, bl.lock_index blocking_lock_index, wl.lock_id waiting_lock_id, wl.lock_mode waiting_lock_mode, wl.lock_type waiting_lock_type, wl.lock_table waiting_lock_table, wl.lock_index waiting_lock_index FROM information_schema.INNODB_LOCK_WAITS ilw , information_schema.INNODB_TRX b , information_schema.INNODB_TRX w , information_schema.INNODB_LOCKS bl , information_schema.INNODB_LOCKS wl WHERE b.trx_id = ilw.blocking_trx_id AND w.trx_id = ilw.requesting_trx_id AND bl.lock_id = ilw.blocking_lock_id AND wl.lock_id = ilw.requested_lock_id\G

*************************** 1. row ***************************

     waiting_thread: 6

     waiting_trx_id: 136

      waiting_query: UPDATE job_locks SET timestamp = '2024-01-02 19:00:00'  WHERE name = 'AUDIT_LOG'

    blocking_thread: 14

    blocking_trx_id: 0

     blocking_query: NULL

   blocking_lock_id: 0:11:3:2

 blocking_lock_mode: S

 blocking_lock_type: RECORD

blocking_lock_table: `test`.`job_locks`

blocking_lock_index: PRIMARY

    waiting_lock_id: 136:11:3:2

  waiting_lock_mode: X

  waiting_lock_type: RECORD

 waiting_lock_table: `test`.`job_locks`

 waiting_lock_index: PRIMARY

1 row in set (0.000 sec)