[development] The dawn of DB locks

[Karoly Negyesi]

Yup I did a similar grep, but I have not did my homework apparently.

There are two typical kinds of locks: one, when doing a variable set which was a SELECT-INSERT. The solution to this is 

db_query("UPDATE {variable} SET value = '%s' WHERE name = '%s'", $serialized_value, $name);
if (!db_affected_rows()) {
  @db_query("INSERT INTO {variable} (name, value) VALUES ('%s', '%s')", $name, $serialized_value);
}

http://drupal.org/node/55516#comment-389837 explains why locks are not necessary.

Files that do stuff like this (I included those that already do an UPDATE-INSERT but wtih a lock which is unnecssary above):

> buddylist/buddylist.module:
> commentapproval/commentapproval.module:
> i18n/i18n.module:
> localizer/system/includes/cache.inc:
> memcache/patches/DRUPAL-5-1-cache-serialize.patch:
> memcache/patches/DRUPAL-5-2-cache-serialize.patch:
> replication/bootstrap.inc.patch:+
> nodeapproval/nodeapproval.module:
> semantic_search/contrib/SONIA/ext/cache/ffcache.sonia.inc: (this one creates the table if needed inside the lock, do this in the install hook instead)
> trackback/trackback.module:

And then we have a set of N-M saves, where you had N items in the database and now you will have M. You can always write a diff save -- drupal_write_record will save the legwork of writing a separate INSERT/UPDATE SQL.

> community_tags/community_tags.module:
> exif/exif.module:

Special cases:

> chatroom/chatroom.module:
When this module goes over to Drupal-6 thus MySQL 4.1 it can revert http://drupal.org/node/70949 . "Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14. "

> media_mover/media_mover_api.module:
Instead of a SELECT first and an UPDATE later you can db_query('UPDATE {media_mover_config_list} SET status = "running", last_start_time = %d, start_time = %d WHERE cid = %d AND status != 'running'', $config['start_time'], time(), $cid); -- I added AND status != 'running' here. Now, check for affected rows. If you have not affected any rows, then there is another running, if you affected, then you can run the SELECT. 

Another UPDATE trick for long running scripts is the usage of an autoincrement field, if $id is the value of it, then UPDATE foo SET whatever = $id ; SELECT * FROM foo WHERE whatever = $id will very nicely take care of your script instance pwning one row without locking anything. This will let several instances of the same script running.

> reptag/reptag_helper.inc:
> reptag/reptag_module.inc:

I was unable to get why this is using locks...

> xbview/xbview_parser.inc:
db_lock_table('xbookmark');db_query("DELETE FROM {xbookmark} ");db_unlock_tables();

I am not getting clearly why you need to lock for one single statement -- but here is a hint: TRUNCATE exists in pgsql and mysql both and will be a lot faster than DELETE FROM.