CODEWITHSUNDEEP
mysqltransactionsinnodbisolation-level
Markus Malkusch
Markus Malkusch

Reputation: 7878

How would a long running transaction affect an auto commit application?

This is my current situation: There is some existing application which doesn't use explicit transactions at all. I.e. the application runs in auto commit mode on InnoDB. I understand that the transaction isolation level for those auto commits should be REPEATABLE READ by default.

I'll have to extend that application by some long running background tasks (read and write) which I want to put into a transaction. As I discovered already a phantom read scenario I have to choose SERIALIZABLE as isolation level.

What effects would this transaction have on those auto commits (mostly read) from the existing application? Would I introduce e.g. blocking?

I understand that my transaction might fail, but that is fine for me.

Upvotes: 1

Views: 189

Answers (1)

Argeman
Argeman

Reputation: 1353

In the worst case your quick running writing auto-commit-transactions will be blocked until some long running transaction finishes. I don't see a reason why your transactions should fail, neither will your auto-commit updates. Reading queries will always execute independently from any running transactions (by simply reading data in the state before the transaction)

In the case that your transactions and the auto-commit updates work on different data, they will painlessly run simultaneous. But you have to find out if that's the case.

Upvotes: 1

Related Questions