Currently, the MySQL sequence manager db_next_id() locks the entire table. This is unnecessary, even without transactions. Here's a patch for 5.1, though it will probably not qualify for 5.1's core due to its reliance on MySQL 4's ON DUPLICATE UPDATE. The patch may apply seamlessly to 6.x-dev because only the line numbers change. As an additional benefit to InnoDB users, this will only lock one row of {sequences}. LAST_INSERT_ID is maintained per connection, so the code below functions atomically.

The updated MySQL sequencer:

function db_next_id($name) {
  $name = db_prefix_tables($name);  
  db_query('INSERT INTO {sequences} VALUES ("%s", %d) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1)', $name, 1);
  $id = db_result(db_query('SELECT LAST_INSERT_ID()'));
  if ($id == 0) {
    $id = 1;
  }
  return $id;
}

No schema changes are necessary.

CommentFileSizeAuthor
drupal-5.1-sequence.patch1.8 KBdavid strauss

Comments

david strauss’s picture

david strauss
he/him
commented

A few pieces of MySQL trivia on why this works:

  • LAST_INSERT_ID() returns 0 if there was no insert ID generated in the previous query. For this patch, this occurs whenever the sequence doesn't already exist in the system.
  • LAST_INSERT_ID(x) stores x to the LAST_INSERT_ID connection variable and returns x.
  • ON DUPLICATE KEY UPDATE runs the UPDATE if the key already exists in the table and the INSERT INTO would fail.

So, I'm actually using LAST_INSERT_ID() backwards: it's 0 if I insert a row and >=1 if I increment (update) an existing sequence.

davea’s picture

davea commented

This would help InnoDB performance by no longer using the *LOCK TABLES* function and relying on the row-level locking that InnoDB provides.

Otherwise, running Drupal on MySQL database using the InnoDB engine would not reap any benefit in this context.

Crell’s picture

Crell commented
Status: Needs review » Closed (duplicate)

http://drupal.org/node/55516 - Already done and awaiting Dries' pondering.