I am trying to implement some parts of
MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions.
So, whatever we try, deadlocks occur. What we do, we start a transaction, then
SELECT ... FOR UPDATE, try an
INSERT and if it causes an 23xxx integrity error try an UPDATE instead. Deadlocks. We removed the
FOR UPDATE cos we decided that for our use, it's OK. Still deadlocks.
I can't just switch isolation levels because
READ COMMITTED needs row logging per SET TRANSACTION:
As of MySQL 5.1, if you use READ COMMITTED [...] you must use row-based binary logging.
To change the global binlog_format value, you must have the SUPER privilege. This is also true for the session value as of MySQL 5.1.29.
I can't require every Drupal setup to have
SUPER nor we can say that Drupal is incompatible with statement based binlogs when that's the default and the most widespread.
INSERT ... ON DUPLICATE KEY is neither versatile enough nor is it deadlock free.
So what now?