Get last_insert_id with singleton pattern

Question

I was wondering about a thing when using the singleton pattern on a database connection class.

As I understand it, the singleton pattern prevents the creation of more then 1 object of a given class that uses the pattern.

Lets say I need the id from a row I just inserted which I get via the mysqli::$insert_id. What if another use of the connection object was used to insert a row at the same time, might that result in a chance of returning a different id then the one expected or is it certain always to return the right id?

Sorry for the newbie question, I have just been wondering whether there were a tiny chance on a multiuser application that getting the id this way might be inconsistent.

Thanks in advance.

http://stackoverflow.com/a/4596323/889949 – DaveRandom Apr 22 '13 at 10:14 — DaveRandom, Apr 22 '13 at 10:14

BlitZ · Answer 1 · 2013-04-22T10:31:20.977

As of MySQL:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

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.

Let's implement some testing:

<?php
header('Content-Type: text/plain');

$con1 = new mysqli('localhost', 'root', '1', 'new_schema');
$con2 = new mysqli('localhost', 'root', '1', 'new_schema');

$sql = 'DROP TABLE IF EXISTS `x`';

$con1->query($sql);

$sql = <<<SQL
CREATE TABLE `x`(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `value` VARCHAR(20)
)ENGINE=InnoDB;
SQL;

$con1->query($sql);

$sql = <<<SQL
INSERT INTO `x` (`id`, `value`)
VALUES (NULL, NULL);
SQL;

$con1->query($sql);
$con2->query($sql);

echo 'con1 == con2 is ',  $con1 == $con2  ? 'true' : 'false', PHP_EOL;
echo 'con1 === con2 is ', $con1 === $con2 ? 'true' : 'false', PHP_EOL;

echo '1: ', $con1->insert_id, PHP_EOL;
echo '2: ', $con2->insert_id, PHP_EOL;

$con1->close();
$con2->close();
?>

Shows:

con1 == con2 is true
con1 === con2 is false
1: 1
2: 2

Test subjects: PHP 5.4.5, MySQL 5.6.10.

As i see it, you make 2 connections here and they are different as they only share their destinations but otherwise is unique. If Singleton actually worked (just read the link to the Singleton pattern in the comment to my post) it would always be the same connection object that was shared amongst different users which would pose the possibility of getting the id of another user, would it not? — Rasmus, Apr 22 '13 at 11:41
**@Rasmus**, it would be shared around different objects, but not users (unless it's [`persistent`](http://php.net/manual/en/mysqli.persistconns.php)). Enough would be to create one connection by yourself and watch it carefully. There is a similar [solution](http://stackoverflow.com/questions/16055803/object-oriented-php-and-database-wrappers/16056018#16056018), I've posted before. And, yes, `mysqli::insert_id`s are connection-dependent and reliable. — BlitZ, Apr 22 '13 at 11:48

Get last_insert_id with singleton pattern

1 Answers1