|
The tracking of read-only transactions does not appear to work when executed in a pipelined manner. The following SQL demonstrates the problem:
START TRANSACTION READ ONLY;
|
SELECT @@server_id;
|
COMMIT;
|
Instead of the queries being routed to the same server where the read-only transaction starts, the queries are routed to different servers. This behavior is caused by the problem described in MXS-4419 where the transaction state advances too soon.
2022-11-30 22:06:09 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 32, type: QUERY_TYPE_READ|QUERY_TYPE_BEGIN_TRX, stmt: START TRANSACTION READ ONLY
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server2 <
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Transaction starting on 'server2'
|
2022-11-30 22:06:09 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 23, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: SELECT @@server_id
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Storing query (len: 23 cmd: 3), expecting 1 replies to current command: SELECT @@server_id. Would route TARGET_SLAVE to 'server2'.
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); New COM_QUERY received while query execution is active: COMMIT
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server2' (OK: 0 warnings)
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Transaction complete on 'server2'
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); >>> Routing stored queries
|
2022-11-30 22:06:09 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 23, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: SELECT @@server_id
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server3 <
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Transaction starting on 'server3'
|
2022-11-30 22:06:09 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 11, type: QUERY_TYPE_COMMIT, stmt: COMMIT
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Storing query (len: 11 cmd: 3), expecting 1 replies to current command: COMMIT. Would route TARGET_SLAVE to 'server3'.
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); <<< Stored queries routed
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server3' (Resultset: 1 rows in 70B)
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Transaction complete on 'server3'
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); >>> Routing stored queries
|
2022-11-30 22:06:09 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 11, type: QUERY_TYPE_COMMIT, stmt: COMMIT
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server4 <
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Transaction starting on 'server4'
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); <<< Stored queries routed
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server4' (OK: 0 warnings)
|
2022-11-30 22:06:09 info : (1) [readwritesplit] (RW-Split-Router); Transaction complete on 'server4'
|
To make matters worse, the normal protections that read-only transactions would otherwise have do not apply if the routing doesn't work correctly:
2022-11-30 22:15:42 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 32, type: QUERY_TYPE_READ|QUERY_TYPE_BEGIN_TRX, stmt: START TRANSACTION READ ONLY
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server2 <
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Transaction starting on 'server2'
|
2022-11-30 22:15:42 info : (1) (server2); Storing COM_QUERY while in state 'Authenticating': START TRANSACTION READ ONLY
|
2022-11-30 22:15:42 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 35, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.t1 VALUES (1)
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Storing query (len: 35 cmd: 3), expecting 1 replies to current command: INSERT INTO test.t1 VALUES (1). Would route TARGET_SLAVE to 'server2'.
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); New COM_QUERY received while query execution is active: COMMIT
|
2022-11-30 22:15:42 info : (1) Authentication to 'server1' succeeded.
|
2022-11-30 22:15:42 info : (1) Authentication to 'server2' succeeded.
|
2022-11-30 22:15:42 info : (1) Authentication to 'server3' succeeded.
|
2022-11-30 22:15:42 info : (1) Authentication to 'server4' succeeded.
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server2' (OK: 0 warnings)
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Transaction complete on 'server2'
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); >>> Routing stored queries
|
2022-11-30 22:15:42 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 35, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.t1 VALUES (1)
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server3 <
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Transaction starting on 'server3'
|
2022-11-30 22:15:42 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 11, type: QUERY_TYPE_COMMIT, stmt: COMMIT
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Storing query (len: 11 cmd: 3), expecting 1 replies to current command: COMMIT. Would route TARGET_SLAVE to 'server3'.
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); <<< Stored queries routed
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server3' (Error: 1146, 42S02 Table 'test.t1' doesn't exist)
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Transaction complete on 'server3'
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); >>> Routing stored queries
|
2022-11-30 22:15:42 info : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 11, type: QUERY_TYPE_COMMIT, stmt: COMMIT
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server4 <
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Transaction starting on 'server4'
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); <<< Stored queries routed
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server4' (OK: 0 warnings)
|
2022-11-30 22:15:42 info : (1) [readwritesplit] (RW-Split-Router); Transaction complete on 'server4'
|
The only workaround to this is to wait for all the pipelined commands to finish before executing the COMMIT command.
|