I’m currently struggling with CockroachDB retryable transaction:
As described here, this behavior is really a problem in my situation:
In some cases, the
RELEASE SAVEPOINTstatement itself can fail with a retry error, mainly because transactions in CockroachDB only realize that they need to be restarted when they attempt to commit. If this happens, the retry error is handled as described in step 4.
In my application, I want to implement the following REST endpoint for creating new entries:
- Do some validation checks (basically running some SQL statements, all in one transaction)
- Run the insert statements
- Publish a message to a message queue, if that fails, rollback the transaction
I’m running this all in one transaction and have implemented client side transaction retries with the go library.
If I understand the behavior of RELEASE SAVEPOINT correctly, it is possible that the RELEASE SAVEPOINT operation fails and in this situation, the whole process described above would get retried. In this case, the message for the message queue is already published and that would trigger another publish to the message queue for the retry run.
Is there a general solution for the described problem?