[v10.2.x] Annotations: Split cleanup into separate queries and deletes to avoid deadlocks on MySQL #80485
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
![grafana-delivery-bot[bot]](https://avatars.githubusercontent.com/in/329617?s=60&v=4){kind=small}
… deadlocks on MySQL (#80329) * Split subquery when cleaning annotations * update comment * Raise batch size, now that we pay attention to it * Iterate in batches * Separate cancellable batch implementation to allow for multi-statement callbacks, add overload for single-statement use * Use split-out utility in outer batching loop so it respects context cancellation * guard against empty queries * Use SQL parameters * Use same approach for tags * drop unused function * Work around parameter limit on sqlite for large batches * Bulk insert test data in DB * Refactor test to customise test data creation * Add test for catching SQLITE_MAX_VARIABLE_NUMBER limit * Turn annotation cleanup test to integration tests * lint --------- Co-authored-by: Sofia Papagiannaki <1632407+papagian@users.noreply.github.com> (cherry picked from commit 81c45bf)
grafana-delivery-bot
bot
requested review from
papagian,
zserge and
undef1nd
and removed request for
a team
grafana-delivery-bot
bot
added
add to changelog
area/annotations
area/backend
backport
alexweav
approved these changes
Jan 16, 2024
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Backport 81c45bf from #80329
What is this feature?
Writes to annotations are sometimes rejected on MySQL due to a recurring deadlock with the annotation cleanup job.
Details here: #64979
Why do we need this feature?
The deadlock seems to be a result of the subquery, under normal circumstances a plain insert and delete should not lock, and there are no extended transactions at play.
This PR splits the subquery into a separate SQL statement. These statements do not share a transaction, and therefore allow locks to flush in between.
Unfortunately we could not reduce this to a single DELETE statement, due to the batching - DELETE LIMIT is not supported on all databases without needing to reintroduce the subquery. The IDs loaded into memory are of bounded size due to the batch size configuration.
Which issue(s) does this PR fix?:
#64979
Special notes for your reviewer:
Please check that: