lastInsertId concurrency Problem (same ID for different inserts)

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?


Here’s how you’re getting ID: Since MSSQL doesnt’ support sequences, it’s possible to have concurrency problems.

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.

  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 ?

Right. then and there’s a sequence…

There were reports of this behavior (bugs): I’ve googled for “mssql sequence concurrency” so maybe you’ll find more details on the issue.

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.