Hi Daniel,
On 2025-10-08 23:37, Daniel Black wrote:
I attach a python script that reproduces the issue by force using
different indexes, and dead lock details generated by
innodb_print_all_deadlocks=ON.
(I ran this on MariaDB 11.8.3).
...
In one case, UPDATE vs UPDATE made a deadlock, where one waits for
index_1 and the other waits for index_2.
In other cases, UPDATE vs SELECT made a deadlock, where UPDATE waits
for
index_1 (or index_2) and SELECT waits for PRIMARY.
Is this an expected behaviour ? Is there a way to prevent such
deadlocks
?
I had someone else having the same problem a few months ago:
https://mariadb.zulipchat.com/#narrow/channel/118759-general/topic/.E2.9C.94.20Deadlocks.20on.20UPDATE.20after.20SELECT.20FOR.20UPDATE
The TLDR version is that in_predicate_conversion_threshold[1],
defaulting to 1000 will convert a IN list into a range search which
will lock the intermediate records in the range. Adjusting the system
variable, for session or via SET STATEMENT
in_predicate_conversion_threadshold= FOR query[2] or number of items
in the IN list will likely resolve this for you.
[1]
https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#in_predicate_conversion_threshold
[2]
https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-statement
Thanks for your reply.
That's very interesting as I didn't know that parameter.
But in my case, IN (...) list is up to 100 thus less than the default
in_predicate_conversion_threshold value.
And setting it 0 or whatever value seems not change the situation.
My guess is ...
* 'the order of updating indexes by UPDATE' is affected by index used in
SELECT ?
(like thread 1 updates index_1 then index_2, whereas thread 2 updates
index_2 then index_1)
* SELECT ... FOR UPDATE (SKIP LOCKED) does not lock selected rows
themselves but locks something in the used index ?
because this issue will not happen if all SELECT queries use the same
index.
Kazuhiko
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
