[MXS-4467] Explicit transactions without a default database do not work as expected with schemarouter Created: 2023-01-03 Updated: 2023-01-10 Resolved: 2023-01-08 |
|
| Status: | Closed |
| Project: | MariaDB MaxScale |
| Component/s: | schemarouter |
| Affects Version/s: | 2.5.23, 6.4.4, 22.08.3 |
| Fix Version/s: | 6.4.5, 22.08.4 |
| Type: | Bug | Priority: | Major |
| Reporter: | markus makela | Assignee: | markus makela |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Sprint: | MXS-SPRINT-174 |
| Description |
|
The following transaction demonstrates the problem:
The BEGIN and COMMIT are routed according to the queries without explicit databases case mentioned in the limitations. The result of this is that the queries are not routed to the same server where the SQL content of the transaction is sent. Here is a log excerpt from MaxScale that demonstrates the problem:
This also affects transactions that use autocommit=0 but commit it using the COMMIT SQL statement. Transactions that commit the transaction with SET autocommit=1 are not affected by this. The ideal thing to do would be to delay the routing of the explicit transaction start until the server on which it is started is known. A less ideal solution that would still solve this is to treat all BEGIN and COMMIT commands as session commands and route them to all backends. |
| Comments |
| Comment by markus makela [ 2023-01-03 ] |
|
The case for ROLLBACK and COMMIT is simple: assuming the transaction is not empty and has one target, the logical destination for the command is the same target where the previous command was routed. This will result in correct behavior when the transaction is located completely on a single target. However, transactions that touch more than one shard will result in unwanted behavior where only one shard receives the COMMIT. Another approach would be to route all BEGIN, COMMIT and ROLLBACK statements to all nodes. This would cause the transaction to be committed on all nodes but it would result in an increase of traffic on all shards. Neither of these approaches provide cross-shard consistency for transactions but the latter would cause BEGIN to also start a transaction on all nodes, same as SET autocommit=0. |