Change LOCK in db_next_id (MySQL)

Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB.

SELECT ... FOR UPDATE uses row-level locking which requires MySQL 4 as far as I know. The syntax is ANSI SQL though. Are you using MySQL 3 or MySQL 4?

I wrote a mail to drupal-devel about making MySQL 4 a requirement. If we agree to drop support for MySQL 3, the proposed patch might improve performance too (although it will not be noticable).

From MySQL Reference Manual, SELECT Syntax:

If you use FOR UPDATE on a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction.

Meaning FOR UPDATE clause is allowed on any engine, but it only works on InnoDB; MyISAM just ignores that, and no locking occurs.

I found a useful tip in Locking Reads SELECT ... FOR UPDATE page:

In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

Tried to modify db_next_id(), diff attached, but not sure how to test if that works. Just an idea.

misread my own quote, missed LAST_INSERT_ID in the query. Updated diff.

LAST_INSERT_ID only works with auto_incremented columns.

well, as a separate task we might consider throwing out our sequqnces implementation and just using DB provided seqences. Both pgsql and mysql provide these. mssql does too in case we ever revive support for it. Further, PHP 5.1 brings us PDO (a native DB access layer) that handles sequences automatically: http://www.php.net/manual/en/ref.pdo.php

I agree with Moshe that we should use the database's autoincrement feature, and not our own sequences.

As he said, the two engines we support already do it, as well as the potential third engine as well.

I disagree somewhat with the PDO thing, since it is PHP5 only, and hence not suitable for the time being. In the future, maybe, but not now.

LOCK's don't play well with multi-master replication (which we're using). MySQL 5 has support for safe auto_increment.

I would be in support of having every table use its own auto_increment, but here's what we have done for now:

function db_next_id($name) {
  $name = db_prefix_tables($name);
  db_query("INSERT INTO {sequences} (name) VALUES ('%s')", $name);
  $id = db_result(db_query('SELECT last_insert_id()'));
  db_query("DELETE FROM {sequences} WHERE name='%s' AND id < %d", $name, $id);
  return $id;
}

AND...

ALTER TABLE sequences DROP PRIMARY KEY;
ALTER TABLE sequences ADD PRIMARY KEY (name, id);
ALTER TABLE sequences MODIFY COLUMN id INT AUTO_INCREMENT;