Preparing queries for database replication

While looking through drupal and the API documentation (drupaldocs.org) to learn how to create my first module, I noticed a potential database issue. This only relates to sites that use database replication. And it only relates to executing a delete followed by an insert of the same primary key. At the moment it's very very rare this would ever occur but I thought I'd mention it for future-proofing.

There are generally two types of replication (that I know of): transaction-based and replicator-master. For transaction-based the transaction logs are passed around to each server and queries are guaranteed to execute in the order submitted by the application. For master-based replication all queries are sent to a master server, usually balanced across threads, and passed to each slave. The master will usually not guarantee the order of queries (as each query can be passed to any thread). So a delete followed by an insert may end up executing on another database server as an insert followed by a delete. The insert fails on a duplicate key and the delete runs. Some or all of the servers have then lost that record.

I found the following code in includes/bootstrap.inc on drupaldocs.org:

function variable_set($name, $value) {
  global $conf;

  db_query("DELETE FROM {variable} WHERE name = '%s'", $name);
  db_query("INSERT INTO {variable} (name, value) VALUES ('%s', '%s')", $name, serialize($value));
  cache_clear_all('variables');

  $conf[$name] = $value;
} 

If this were run on the systems I use at my job there would be a good chance that the variable would disappear across all database servers. It depends on the systems used and the DBA's settings. It does create more code having to check if a record exists in order to choose between an update and an insert, but it eliminates potential problems on large systems. I haven't looked through all the code and contributed modules to see how common this is and I don't know how many systems out there are set up with master-slave replication, but I would just keep it in mind for the future as new code is written.

If anyone thinks it's important enough I'll write it up as a page in the handbook.