php – Why does PDO :: lastInsertId return the wrong ID for frequent database insertions?

Question:

There is a web application (Win7x64, MS Sql Server 2012, Apache 2.4, PHP 5.6, Yii 1.1). About a hundred users are constantly filling out one table. If different users have inserted a row at the same time, the first is returned the ID of the inserted row of the second, and further actions, for example, saving the history of changes, are performed with the wrong ID. Yii uses PDO, in particular the lastInsertId method to get an ID, but I have not found a description of such a problem on the Internet.

For the purity of the experiment, I created two new tables, Collision and CollisionHistory:

CREATE TABLE [dbo].[Collision](
[id] [int] IDENTITY(1,1) NOT NULL,
[uuid] [varchar](50) NULL,
CONSTRAINT [PK_Collision] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
)

CREATE TABLE [dbo].[CollisionHistory](
[id] [int] IDENTITY(1,1) NOT NULL,
[collision_id] [int] NULL,
[uuid] [varchar](50) NULL,
 CONSTRAINT [PK_CollisionHistory] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
)

Based on them in the gii model generation. Add the code to the Collision model:

public function afterSave(){
    parent::afterSave();
    $history = new CollisionHistory();
    $history->collision_id = $this->id;
    $history->uuid = $this->uuid;
    $history->save(false);
}

Writing a controller

class CollisionController extends CController
{

    public function actionTest()
    {
        for ($i = 0; $i < 1000; $i++) {
            $model = new Collision();
            $model->uuid = $this->UUIDv4();
            $model->save(false);
        }
    }

    private function UUIDv4()
    {
        return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0, 0xffff), mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0x0fff) | 0x4000,
            mt_rand(0, 0x3fff) | 0x8000,
            mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
        );
    }

}  

Then on a count of three with a colleague, we run actionTest each from their own computer. As a result, there are 419 repetitions of collision_id in the CollisionHistory table for 2000 inserts in the Collision table.

Why is this happening and how to avoid it?

Answer:

I ran the test on pure PDO, changed several versions, but the result is the same. PDO :: lastinsertid method returns the most recent id for a table, like the MSSQL IDENT_CURRENT () function

That is, if between the insertion and receipt of the id there was an insert in another session, then the id from the second session is returned.

I solved my problem by creating a stored procedure for inserts into the most frequently used table, in which the output parameter is filled with the SCOPE_IDENTITY () function.

By the way, when using an output parameter in a stored procedure, do not forget about the SET NOCOUNT ON statement, which disables the display of the number of rows affected by the procedure instead of the output parameter.

Scroll to Top