I am happy to announce that slave_transaction_retries is available on multi-threaded slave now. You will no longer see the warning that slave_transaction_retries is not supported by multi-threaded slave since MySQL-5.7.5.
Multi-threaded slave and single-threaded slave share the system variable slave_transaction_retries and they have similar transaction retry logic. So this blog just brings you a little new knowledge and then refresh you with the transaction retry logic.
Transaction Retry In Worker Threads
For multi-threaded slave, Transaction retry is done in worker threads when they are applying their transactions. When a worker thread encounters a temporary error, it needs to read the transaction’s events from relay log by itself and then apply them again.
The only new thing is that worker threads need to read events from relay log by themselves. To support it, coordinator stores relay log position of events into the job structure which will be dispatched to workers. So workers can know start position of transactions.
When starting a transaction, the worker copies its start position in a variable. So it can be used after the first event is removed from the job queue. If an temporary error happens, it opens the certain relay log and start to read from the transaction’s start position. Since some events of the transaction are still in the job queue, the worker just reads and applies the events already removed from the job queue one by one and then resume the normal applying process.
General Transaction Retry Logic
Retry process is pretty simple(see below detail):
– Check if it is an temporary and if it can be rolled back safely when encountering an error
– Rollback current transaction
– Sleep a few seconds
– Retry the transaction again
Temporary Errors
Two types of errors are defined as temporary errors. They are
– ER_LOCK_DEADLOCK
– ER_LOCK_WAIT_TIMEOUT
For detail, please check reference manual.
Rollback The Tranasaction Before Retry
The retry is a transaction retry but not statement retry. So before retrying the transaction, it has to first rollback current transaction which encounters the temporary error. And then retry the transaction as a whole.
Sleep Before Retry
Sleep logic is pretty simple. It sleeps 1s for the first time to retry a transaction, and then sleep time will increase 1s in each retry of the same transaction until it arrive 5s. Sleep time is limited in is 5s.
Summary
Transaction retry feature works for both types of mutli-threaded slave(database based and logical clock based). It also works fine when you force slave to commit transactions in same order as master by setting system variable slave_preserve_commit_order to ON.
By default, transaction retry is on for all types of slave. Multi-threaded slave will not break if it just encounters some temporary errors mentioned above.