RELEASE SAVEPOINT - transaction errors

Hey community,

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 SAVEPOINT statement 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:

  1. Do some validation checks (basically running some SQL statements, all in one transaction)
  2. Run the insert statements
  3. 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?

Hi @Sebholstein,

You would be correct in your assumption that your messages would be in the queue twice if RELEASE SAVEPOINT where to fail. This would also be the case for any ordinary COMMIT, there isn’t anything . inhereintly special about RELEASE SAVEPOINT that would cause this to happen. You would need to somehow handle the duplicate messages in the queue or publish them only after the transaction completes.

Thanks,

Ron