Hi Kazuhiko, On Thu, 2 Oct 2025 at 18:38, Kazuhiko via discuss <[email protected]> wrote: > > Hello, > > We have a job queue using MariaDB like : > > SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; > BEGIN; > SELECT uid FROM message WHERE (several patterns of conditions) FOR > UPDATE SKIP LOCKED; > UPDATE message SET processing_node=(thread_id) WHERE UID IN (chosen uids > by SELECT above); > COMMIT; > > As we have several patterns of conditions in SELECT, different indexes > are used based on the condition. > And that seem to cause deadlocks. > > 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). > > $ ./venv3/bin/python reproduce_deadlock.py > Setting up schema and data... > Inserting rows... > Inserted 10000 rows. > Created secondary indexes. > Starting workers... > Remaining: 10000, Updated: 0, SELECT Deadlocks: 0, UPDATE Deadlocks: 0 > Remaining: 0, Updated: 10000, SELECT Deadlocks: 3, UPDATE Deadlocks: 1 > All workers finished. > Final stats: {'updated_rows': 10000, 'select_deadlocks': 3, > 'update_deadlocks': 1, 'errors': 4, 'transactions': 100} > > 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 in advance ! You're welcome in advance ! :-) _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
