SQL last insert in Drupal. Is it really threadsafe?

Question

I have a query that might be executed by several users consecutively. I'm scared that if I run the db_last_insert_id command, some users might not get the last insert id, due to concurrency. But according to: http://api.drupal.org/api/function/db%5Flast%5Finsert%5Fid/6, it sates:

Returns the last insert id. This function is thread safe.

My question is, how is this thread safe? The code is only:

<?php
  function db_last_insert_id($table, $field) {
   return db_result(db_query("SELECT CURRVAL('{". db_escape_table($table) ."}_". db_escape_table($field) ."_seq')"));
  }
?>

I don't see anything about locking tables or nothing?

Answer 1

Using MySQL (as you seem to indicate with the tags on your question), the function db_last_insert_id is defined this way :

function db_last_insert_id($table, $field) {
  return db_result(db_query('SELECT LAST_INSERT_ID()'));
}

in database.mysql-common.inc

And LAST_INSERT_ID() depends on the connection (quoting, emphasis mine) :

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

So, I'd say that this is quite OK for MySQL ;-)

The definition your posted is actually the one used for PostGreSQL :

function db_last_insert_id($table, $field) {
  return db_result(db_query("SELECT CURRVAL('{". db_escape_table($table) ."}_". db_escape_table($field) ."_seq')"));
}

In database.pgsql.inc

From pgsql manual on sequences (quoting ; emphasis mine) :

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

So, I'm guessing this is quite OK too, for PostGreSQL.