Doubt about connection rollback

I am confused about the rollback that is mentioned in cockroach transaction doc and the JDBC doc .

In the example in JDBC doc conn.commit() is in a try block and rolling back to savepoint in case of exception.

But in the cockroach doc we are releasing the savepoint before doing commit. So what if there is an exception while doing conn.commit()?

Hi @tuk. We have some documentation for how to do client-side transactions
in java at
https://www.cockroachlabs.com/docs/stable/build-a-java-app-with-cockroachdb.html#transaction-with-retry-logic
.

Yes @dan I have seen that. But I am getting confused about the below issue in github and the doc that you shared.

Ah, I see. It looks like there is a PR out to fix that example, but it
hasn’t merged yet: https://github.com/cockroachdb/docs/pull/2126. I’ll pull
in one of the experts.

There is indeed an additional problem with our java code sample in that we never call conn.rollback(). Thanks for hinting at that; we’ll fix it. conn.rollback() should be called whenever an exception is propagating out of the transaction block. It definitely needs to be called if conn.commit() hasn’t been called; the jdbc docs seem unclear to me on whether it actually has to be called in case conn.commit() was called (and failed), but it’s probably best to always call it and swallow any exception from the rollback call.

Now, about savepoints. It’s important to understand that crdb does not have general support for savepoints: crdb lets you define a single savepoint cockroach_restart and releasing this savepoint actually commits the transaction. If the release call succeeds, the transaction is done. A commit call is still required before the conn can accept new statements, but won’t do much. If the commit call fails, that can only mean that the db connection is broken - and so in this case it doesn’t matter much how your code reacts to the commit exception; hopefully jdbc/the database driver you’re using will not let you do anything further with the connection anyway. As I was saying above, you should probably call conn.rollback() just in case jdbc wants you to do it, and call it a day.

Does this help?

Thanks @andrei it is clear. One more query

Let’s say there may be a use case in which user might not want to use client side retry in a loop , and handle the retry based on some application logic.

Then in this case there is no need to use savepoint and all the changes will be committed when conn.commit() is called? Is it right?

Then in this case there is no need to use savepoint and all the changes will be committed when conn.commit() is called? Is it right?

Correct. If you don’t want to retries based on the retriable error code, then you write code just like you would against Postgres (or any other database supported by JDBC).