I have a piece of (Perl) code, of which I have multiple instances running at the same time, all with a different - unique - value for a variable $dsID. Nearly all of them keep falling over when they try to execute the following (prepared) SQL statement:

DELETE FROM ssRates WHERE ssID IN (SELECT id FROM snapshots WHERE dsID=?)

returning the error:

Lock wait timeout exceeded; try restarting transaction

Which sounds clear enough, except for a few things.

1. I have autocommit enabled, and am not using (explicit) transactions.
2. I'm using InnoDB which is supposed to use row-level locking.
3. The argument passed as $dsID is unique to each code, so there should be no conflicting locks to get into deadlocks.
4. Actually, at present, there are no rows that match the inner SELECT clause (I have verified this).

Given these things, I cannot understand why I am getting lock problems -- no locks should be waiting on each other, and there is no scope for deadlocks! (Note, though, that the same script later on does insert into the ssRates table, so some instances of the code may be doing that).

Having googled around a little, this looks like it may be a "gap locking" phenomenon, but I'm not entirely sure why, and more to the point, I'm not sure what the right solution is. I have some possible workarounds, -- the obvious one being to split the process up: do the select clause, and then loop over results giving delete command. But really, I'd like to understand this otherwise I'm going to end up in this mess again!

So I have two questions for you friendly experts.

1. Is this a gap-locking thing?
2. If not - what is it? If yes -- why. I can't see how this condition matches the gap lock definition.

(NB, server is running MariaDB: 5.5.68-MariaDB; in case this is something fixed in newer versions).