Howto bulk update an InnoDB table without deadlocks?

I have two tables, one of which has a many-to-many relationship ( fooBarTable

with fooId

and columns barId

) and the other is an InnoDB table fooCounterTable

with fooId

and columns counter

, counting occurrences fooId

in fooBarTable

.

When deleting everything barId

from fooBarTable

I need to update accordingly fooCounterTable

.

The first thing I tried was:

UPDATE fooCounterTable SET counter = counter - 1
    WHERE fooId IN (SELECT fooId FROM fooBarTable WHERE barId = 42 ORDER BY fooId);

      

        
        
        
      

    

But I got this error:

MySQL error (1205): Lock wait timeout exceeded; try restarting transaction

      

        
        
        
      

    

Updating a table on add barId

works fine with this SQL statement:

INSERT INTO `fooCounterTable` (fooId, counter) VALUES (42,1), (100,1), (123,1)
    ON DUPLICATE KEY UPDATE counter = counter + 1;

      

        
        
        
      

    

So, I thought I would do the same when decrementing the counter, even if it looks silly to insert 0-Values, which should never be:

INSERT INTO `fooCounterTable` (SELECT fooId, 0 FROM fooBarTable WHERE barId = 42 ORDER BY fooId)
    ON DUPLICATE KEY UPDATE counter = counter - 1;'

      

        
        
        
      

    

Most of the time this works fine, but sometimes I get a deadlock:

MySQL error (1213): Deadlock found when trying to get lock; try restarting transaction

      

        
        
        
      

    

So, I read about dead ends and found out about SELECT ... FOR UPDATE

and I tried this:

START TRANSACTION;
SELECT fooId FROM fooCounterTable 
    WHERE fooId IN (SELECT fooId FROM fooBarTable WHERE barId = 42 ORDER BY fooId) FOR UPDATE;
UPDATE fooCounterTable SET counter = counter - 1
    WHERE fooId IN (SELECT fooId FROM fooBarTable WHERE barId = 42 ORDER BY fooId);
COMMIT;

      

        
        
        
      

    

resulting in:

MySQL error (2014): commands out of sync

      

        
        
        
      

    

Can anyone tell me how to solve my problem?

Update

The last error occurred (2014) because I did not use or free the results of the SELECT statement before executing the UPDATE statement, which is required. I fixed this and I got rid of the 2014 error, but I still get deadlocks (error 1205) from time to time and I don't understand why.