A Database Independent "Next ID"

Hi Daryl, I know this is a cross-database post, but is the following okay for SQL Server only?

set nocount on
insert into ...
select @@identity as newid
set nocount off

I've been using that for years on SQL Server...

The @@identity trick in SQLServer is also very dubious and can return very erroneous results. Basically @@identity is a global variable, and returns back the last identity value generated. So take the case where you insert a row, and a trigger fires to populate a child table, or other metadata and that table itself has an identity value. @@identity returns the second identity value, the one caused by the insert from the trigger and not from the parent table. Since the child table could have less rows, it could very easily return a value for an already used key. @@identity is very much not thread safe.

Nat,

Using @@Identity is fine for SQL Server, so long as you don't have triggers that do inserts into other tables that have Identity columns.

Dave,

Any time you have any variable that you want to update consistently across threads, you MUST single-thread changes to the variable. To do so, you MUST have some sort of semaphore to control access across threads; in this case, it's the update lock. The transaction block where you're single threading is blindingly short, and should be complete in 4ms or less, so concurrency really only becomes a problem if you're doing 250+ inserts a second, in which case using something DBMS-specific is probably a good idea.

In SQL Server, @@Identity is not a "trick", it's a well defined feature, and will only give "dubious" or "very erroneous" results if you either (a) have triggers, or (b) don't do the SELECT @@IDENTITY operation before the next identity insert. One popular mistake is to do "SELECT @@IDENTITY FROM SomeTable", which simply does a Cartesian join from the constant returned by @@IDENTITY, and the rows in SomeTable. So, if SomeTable had 100,000 rows in this case, you'd get 100,000 copies of the @@IDENTITY value. And if your last identity insert was actually for SomeOtherTable, you'd get a completely unexpected value.

Also, @@IDENTITY is not a global variable, it's a connection variable. So it doesn't return the last identity value inserted, it returns the last identity value inserted /on the current connection/. Since ColdFusion pools connections on a per-page basis even if you have connection pooling turned off, it's completely safe from that perspective.

If you have triggers, the scope_identity() function is designed to return the proper identity value, like this:

<code>
<cfquery name="q" datasource="Northwind">
   INSERT INTO SomeTable
   VALUES ('foo')
</cfquery>
<cfquery name="q" datasource="Northwind">
   SELECT SCOPE_IDENTITY() AS ID
</cfquery>
</code>

Sam,

A transaction groups queries into a logical group of work, and ensures that all of the *changes* in the transaction either commit, or roll back. However, a transaction doesn't change the isolation level for reads, which by default is "READ COMMITTED". READ COMMITTED will acquire and hold read locks only long enough to read the row(s), and release the lock once the row is read. The next level, REPEATABLE READ will hold locks on any rows that were actually read until the end of the transaction, and SERIALIZABLE will go so far as to prevent inserts between ranges you selected from. But none of these prevent you from reading a value that another thread/connection has read (since read locks can be made concurrently), so the sort of read consistency you're expecting is never enforced. This is why I do the update first, as that will actually block other threads/connections from reading the value I updated until the end of the transaction. (The isolation level READ COMMITTED won't read the row until the transaction is committed.)

Hope that helps... :-)

--Daryl

Interesting timing on this post, Daryl. I discovered something yesterday that seems to have changed in CF although I'm not sure when. I'm developing an app on 7.0.2 w/ MSSQL and discovered that I could do the insert and select scope_identity() as newID within the same cfquery tag set and it works just fine. I remember having to use nocount on/off in the past (as Nat indicated) to pull this off. I'm not sure when this changed, just that it works now without it and in the same cfquery tag.

So:
[cfquery name="insQuery"]
INSERT INTO SomeTable
(stuff, things)
VALUES
("stuff","things")
SELECT SCOPE_IDENTITY() AS newID
[/cfquery]
will leave you with #insQuery.newID# as your new id.

While it seems to work just fine, I'm also not sure if there's a good reason to *not* do it that way so if anyone knows that to be a bad idea for some reason please enlighten. It's all done within the context of a transaction with isolation="SERIALIZABLE" (as the queryname.newID is used to populate child tables and such and performance/scalability isn't an issue in this case) so it all seems pretty safe.

Scott,

SQL Server always seemed to support multiple statements in the same query, but the DataDirect drivers used by CF stopped supporting that a few releases ago. (Any query that began with the word "INSERT" was no longer allowed to return a resultset.) I didn't know that they fixed that-- good to hear.

In any case, for future compatibility reasons, I'd recommend using two CFQUERYs for that block of code. Also, SCOPE_IDENTITY() is guaranteed to be correct, so long as it's called before the next identity insert on the same connection. So, you don't need to bother with a cftransaction when using that; the transaction, in your case, only adds extra db call and locking overhead, and may contribute to deadlocking problems in the future.

Zac,

I really wish that SQL had defined a standard for getting generated sequence numbers. But then, they didn't even define how to format dates and times; that's implementation-dependent. (JDBC and ODBC define a standard for formatting dates and times; it's a responsibility of the JDBC or ODBC driver to convert dates and times in queries to a format palatable to the individual DBMS. That's why the helper function in CF is called "CreateODBCDateTime", as opposed to "CreateSQLDateTime".)

--Daryl

hi daryl

thanks for posting this. i wasn't aware of the
limitations that the transactional approach
to the issue has.

as a matter of fact, your explanation as to what
exactly happens in terms of locks vis-a-vis isolation levels made me realize that i'd really love to get my hands on a succinct summary of the technical basis behind database transactions.

specifically, i'm looking for a text that explains
isolation levels, locking mechanisms, performance
considerations and all that... basically all the
stuff you seem to know so well.

can you recommend a book, or a website that would help?

thanks,
franco