I currently have a retry policy for SQL write operations.

My question is, if the operation is not idempotent will it matter?

I've always thought that if an exception comes up and the operation is retried, it means that the original attempt does not go through and therefore it will not matter if it is idempotent.

If it DOES matter, what should I look out for in the sql procedures to determine if they are idempotent or not.

Thank you !

CodePudding user response：

Assuming the retry is caused by the DB engine (i.e. it's JDBI or JOOQ or whatnot picking up an SQLException, seeing it's the db engine telling you to retry, and JDBI/JOOQ restarting your code block), yes, of course. That means the transaction has aborted and anything you wrote in SQL that modified anything will be null and void.

However, note that certain SQL statements exist outside of a transaction. Generally, anything that is 'model definition' instead of 'data definition' falls outside of transactions. If doesn't start with INSERT, UPDATE, SELECT, or DELETE, it's likely it's beyond the transaction model and therefore is not being 'aborted'. ALTER TABLE, CREATE VIEW, TRUNCATE someTable, DROP USER, USE someNamespace, SET something - those are usually not transactional. Some db engines can make them transactional if you ask for it, such as CREATE TABLE TRANSACTIONAL in h2. I don't know of any that just do that out of the box.

I would assume that whatever db engine or framework you are using, it doesn't retry unless an SQLException occurs that specifically indicates it. If any other exception occurs, or an SQLException occurs that doesn't mean 'retry', the transaction is just aborted, your 'code block' exits, and the exception is propagated. If somehow that's not how it works - then you have no guarantees. However, that'd be a rather bizarre take on how to interact with DBs.