Auto-retry transactions only in case of lock-waits and dead-locks

[hfazai]

This commit allows to retry when transaction fails. But it retries for any reason, even when table doesn't exist, which is useless because in next attemps it has no chance to complete successfully.

There shoud be a condition to check if the exception allows a retry of the transaction before.

[hfazai]

We can identify dead-locks based on SQLState and ErrorCode of the thrown SQLException. I did an implementation for the feature but still not added unit tests.

@Tapac do you know a way to simulate a dead-lock in my tests?

[spand]

I think its very difficult to enumerate all the cases that should or should not be retried. Not to mention correctly detecting them for all the databases and jdbc versions.

What makes you think these are the only cases that can benefit from a retry? (Off the top of my head I can come up with several additional cases that you did not mention)

[hfazai]

I think its very difficult to enumerate all the cases that should or should not be retried. Not to mention correctly detecting them for all the databases and jdbc versions.

Every SQLException has a code that allows to identify the type of the error. For example to identify a deadlock exception in Postgresql SQLState should be = 40P01 or SQLState=40001

What makes you think these are the only cases that can benefit from a retry? (Off the top of my head I can come up with several additional cases that you did not mention)

Could you give an example of another case that can benefit from a retry?

[spand]

Could you give an example of another case that can benefit from a retry?

Violation of a unique constraint. We use this often for preventing race conditions.

[hfazai]

How can a transaction succeed on the second attempt when having a violation of unique constraint?

Le mer. 16 févr. 2022 1:15 PM, spand @.***> a écrit :

Could you give an example of another case that can benefit from a retry?

Violation of a unique constraint. We use this often for preventing race conditions.

— Reply to this email directly, view it on GitHub https://github.com/JetBrains/Exposed/issues/1449#issuecomment-1041428728, or unsubscribe https://github.com/notifications/unsubscribe-auth/ANS5JQPYVZ23WJ5XD7Q54D3U3OINTANCNFSM5NW4SB7A . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

You are receiving this because you authored the thread.Message ID: @.***>

[spand]

Anything that depends on the data in database ie.

INSERT INTO Loglines FROM (SELECT MAX(linenumber) + 1 AS linenumber, "new text" AS text FROM Loglines)

The point I was trying to get across is that at best this should be implemented using a whitelist of types that are known to not benefit from retries since there are so much uncertainty.

I will refrain from commenting further here.