Comment by biot | Hacker News Reader

Comment by biot | Hacker News Reader

I ran into something similar while working with a Seattle-based email vendor years ago. They had an online survey application which recognized your user information by a special token, created a new entry in a table for your survey, then proceeded to display your information. In launching a high-volume campaign, people started complaining that they were seeing other people's contact information.

They never admitted it, but I'm 100% sure that the issue was that (in SQL Server) they were doing:

  select @@identity

Rather than the correct call of:

  select scope_identity()

The former retrieves the identity of the latest record entered into the table regardless of what SQL statement produced the record (such as another user connecting to the database from a different connection thread); the latter retrieves the identity of the record entered from the current session.

Are you sure about that? I am almost positive that you're wrong, and @@identity is per connection. Otherwise how woul it know which table to return an identity for?

I thought that the difference had to do with what's returned if a trigger does an insert or something like that...

@@identity is per session, but can return other things if you are using triggers (since the trigger will happen before your retrieval of @@identity). One more reason I hate triggers (we also use scope_identity only).

More fun @ http://msdn.microsoft.com/en-us/library/ms187342.aspx

You're right. As mey pointed out via the docs, @@identity is for the current session, but may return unexpected results if a trigger fires as part of the statement(s) or if you are using replication. It's almost never what you actually wanted whereas scope_identity() is. Come to think of it, perhaps the vendor I mentioned was using ident_current('tablename') which returns the last identity generated for the specified table name regardless of session and scope; this would explain the resulting behavior of the app.

Yep, that'd do it!

Reply on news.ycombinator.com