Hints and transaction
ccll ddd
Markus Mäkelä
Hi,
More often than not it's a bad idea to try and route queries to other nodes than the one where the transaction was originally started. This is because it pretty much breaks all the guarantees that a transaction could give you. A simpler solution for these kinds of situations is to turn on autocommit and do everything without a transaction.
In certain situations readwritesplit will ignore hints and
transactions with transaction_replay are one such case. This is
because readwritesplit needs to track the progress of the
transaction and keep track of the contents to make sure that if
the node fails, it'll be able to replay it on a replacement node.
The correct way to retain a consistent read view for a transaction while still being able to load balance them is to use read-only transactions. Explicit read-only transactions (START TRANSACTION READ ONLY) will be load balanced by readwritesplit so that you get some read scaleout while still having the guarantees that a transaction provides. Starting with MaxScale 22.08, SET TRANSACTION SESSION READ ONLY is also treated the same way.
If you know that the transaction is fully read-only but the application you use still starts it with START TRANSACTION or BEGIN, you could try the optimistic_trx feature of readwritesplit. It'll start by routing the transaction as if it was a read-only one and if it later turns out to be a transaction that modifies data, it'll migrate it to a node where writes can be done. This of course isn't very smart if you know that most of your transactions are going to be read-write so I'd say it should only be used to work around limitations in third-party applications.
If you believe you have a valid use-case in mind for routing
SELECTs to other nodes in transactions, I'd be interested in
hearing some details.
Markus
If I want to use the Hints feature in a transaction to route SELECT statements to a secondary node, is this possible? --
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/maxscale/ccfc6d5d-7203-45af-8178-739518527492n%40googlegroups.com.
-- Markus Mäkelä, Senior Software Engineer MariaDB Corporation