lastInsertId concurrency Problem (same ID for different inserts)

Yii 1.1
1

Hey there, I’m not very good at SQL (we use mssql), but we now have our application in use for a company where lot’s of users work at the same time.
From time to time I noticed db errors. We create a new object and save it, and debugging messages showed that the lastInsertId function in CMssqlSqlsrvPdoAdapter doesn’t work as expected when I have multiple sessions creating objects in the same table at the same time (<0.5s~ apart). It returns the same id.
Is there something I’m missing on the framework site, or is it a sql server configuration thing?

Greetings

2

Here’s how you’re getting ID: https://github.com/yiisoft/yii/blob/master/framework/db/schema/mssql/CMssqlPdoAdapter.php#L28. Since MSSQL doesnt’ support sequences, it’s possible to have concurrency problems.

3

Thanks for the reply. I’m not sure I understand it correctly. We use the sqlsrv driver, so the class I debugged into was CMssqlSqlsrvPdoAdapter and not CMssqlPdoAdapter.

if(!$sequence)
  return parent::lastInsertId();
return parent::lastInsertId($sequence);

And sequence is the Tablename. The problem occurs on the same table and I can easily recreate the problem with an asynchrounous loop that creates a couple of ARs and tries to save them.
Is there anything I can do to guarantee the correct ID ?

4

Right. https://github.com/yiisoft/yii/blob/master/framework/db/schema/mssql/CMssqlSqlsrvPdoAdapter.php#L33 then and there’s a sequence…

There were reports of this behavior (bugs): https://support.microsoft.com/en-us/help/3011465/fix-sequence-object-generates-duplicate-sequence-values-when-sql-serve I’ve googled for “mssql sequence concurrency” so maybe you’ll find more details on the issue.

5

Thanks again, but I think I found the solution.

     $sqlsrvVer = phpversion('pdo_sqlsrv') ? intval(array_shift($parts)) : 0;
         if(!$sequence || $sqlsrvVer >= 5) {
             return parent::lastInsertId();

We’ve downloaded our pdo driver directly from Microsoft. They use another versioning, and the newest one is Version 3.x.
I have now downloaded it from PECL, Version 5.3, and when calling the function without the sequence, it works as expected… Strange.