Drupals variable storage and locking

When using InnoDB and isolation level REPEATABLE-READ, locking may become an issue. Especially the Rules module has a reputation of overusing/abusing the variable storage. Beside the fact, that each time you change a variable it clears the entire monolithic cache entry for the variables, it can also cause deadlock issues during high concurrent load when deleting variables.

Drupal uses the db_merge() to update a variable, which begins with a transaction and then a SELECT ... FOR UPDATE.

The problem with a transactional SELECT ... FOR UPDATE, is that if 0 rows are found, a following INSERT can block other INSERTs. In principle, there's nothing wrong with this, however in the particular example regarding the variable_*() functions, this kind of locking can be greatly mitigated.

The problem arises with the use of variable_del() which removes the row from the variables table. After this the system is vulnerable to the 0 rows found locking issue.

My proposal is, that instead of deleting the row, just set it to NULL. This will prevent the gap-lock (or is it next-key lock?) at the expense of adding more data to the variable table. Since the entire variable table is stored in the cache anyways, the actual implications of this can be eliminated by not storing NULL values in the variable cache.

Quick core-hack for the variable_del() function:

function variable_del($name) {
  variable_set($name, NULL);
}

The AutoSlave module, contains a more complete patch for this: http://drupalcode.org/project/autoslave.git/blob_plain/refs/heads/7.x-1.x:/patches/deadlock-mitigation-7.22.patch

As a side note, changing the isolation level to READ-COMMITTED will also fix this problem.