Deadlock free MERGE-alike in MySQL?

Question

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 http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

As of MySQL 5.1, if you use READ COMMITTED [...] you must use row-based binary logging.

And per http://stackoverflow.com/a/2032096/308851 READ UNCOMMITTED also needs row logging. And here comes http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

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 http://bugs.mysql.com/bug.php?id=52020

So what now?

Answer 1

Here is what the MySQL Documentation says on binlog_format:

Specify whether to use row-based, statement-based, or mixed replication. Statement-based is the default in MySQL 5.5. This is also true for MySQL Cluster NDB 7.2.1 and later. See Section 16.1.2, “Replication Formats”.

Under some conditions, changing this variable at runtime is not possible, or causes replication to fail. See Section 5.2.4.2, “Setting The Binary Log Format”, for more information.

Prior to MySQL 5.5, setting the binary logging format without enabling binary logging prevented the MySQL server from starting. In MySQL 5.5, the server starts in such cases, the binlog_format global system variable is set, and a warning is logged instead of an error. (Bug #42928)

Long story short, all I see that needs to be done is the following:

STEP 01 : Set binlog_format to ROW in /etc/my.cnf (my.ini for Windows)

[mysqld]
binlog-format=ROW

STEP 02 : Restart mysql

• For Linux, service mysql restart
• For Windows
  • net stop mysql
  • net start mysql

That's all. No need to worry about passing out the SUPER privilege to anyone.

UPDATE 2013-02-01 00:18 EDT

If you are going to be emulating ANSI-SQL MERGE, someone has to "bite the bullet" and set binlog-format=ROW for MySQL.

PostgreSQL's default transaction isolation level is already read committed, so there is no issue in terms of any MERGE SQL. Same story with Oracle.

When using the InnoDB storage engine, you cannot stay with its default isolation level (repeatable read). Transactions involving MERGE must have two things:

1. Some mechanism for manipulating the transaction isolation level
2. Visibility to forms of data intermittency

As mentioned earlier, handing out SUPER privilege is out of the question. But, why not just start the transaction and name the isolation level?

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Notwithstanding, the binlog format must be set to ROW in conjunction with the SET SESSION TRANSACTION. This will basically have Drupal coerce InnoDB into behaving properly as you attempt your MERGE emulation.