Is is safe to use @@IDENTITY in a transaction?

Question

I was reading this answer about different methods of getting the last identity value entered into a database.

From what I understand, @@IDENTITY is usually a very bad idea because it might return an identity that is not the one you expected--for example an identity value that was recently created by a trigger.

But what if your code is in a transaction?

For example this is a simplified version of a transaction I'm doing (using ColdFusion):

<cftransaction>
    <cfquery name="queryInsertA" datasource="source">
        INSERT INTO tableA (columnName) VALUES (value)
    </cfquery>
    <cfquery name="queryInsertB" datasource="source">
        INSERT INTO tableB (fkey_tableA, columnName) VALUES (@@IDENTITY, value)
    </cfquery>
</cftransaction>

Since, "If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database," does this mean that it would also prevent the isses that can arise when using @@IDENTITY? Or am I misunderstanding the behavior of transactions?

Answer 1

The answer you linked already explains what the main issue is with @@IDENTITY: scope. If your insert triggers another insert, you get an unexpected identity back. Transactions do not change anything.

Answer 2

If you wanted to get the last identity value inserted to a table, use the Ident_current() function.

   Select ident_current ('your table name')

Also you can use scope_identity(), It will bring the identity value of a tablein that particular scope only.

  Select scope_identity()

Answer 3

You don't need @@Identity, nor do you need 2 separate queries. Use the Scope_identity() function for integrity and make it a part of the same connection & query - like so.

<cfquery name="putUser" datasource="#dsn#">
SET NOCOUNT ON
INSERT INTO users(username, email)
VALUES 
('#usersname#','#email#' )
SELECT  SCOPE_IDENTITY() AS newId FROM users
SET NOCOUNT OFF 
</cfquery>

<cfoutput>#putUser.newID#</cfoutput>

This will be totally safe, but like all db transactions it will still be subject to deadlocks so tuning is still important.

CFTRANSACTION is good for multiple DB operations where some CF logic might also be involved, but let the DB locking and transactional system work for you by keeping it together.

Answer 4

You can also use the result attribute of cfquery. If the query performs an INSERT of an identity or auto-increment value for ID, there will be a key named GENERATEDKEY returned in the structure.

<cftransaction>
    <cfquery name="queryInsertA" datasource="source" result="resultA">
        INSERT INTO tableA (columnName) VALUES (value)
    </cfquery>
    <cfquery name="queryInsertB" datasource="source">
        INSERT INTO tableB (fkey_tableA, columnName) VALUES (#resultA.generatedKey#, value)
    </cfquery>
</cftransaction>

Keep in mind this is only CF9 and higher.

Answer 5

You can use Sequence and use that during insert as below:

CREATE SEQUENCE Testseq  
    START WITH 1  
    INCREMENT BY 1 ;

Access the sequence by using below query:

SELECT NEXT VALUE FOR Testseq;

Answer 6

To make it simple :

IF

You know you'r ALL ALONE in the db system, this means, no other user, or process running at the same time, no other transaction running, there is absolutly ZERO activity at the time you use it, and i mean it, ZERO ACTIVITY, then, ok...

ELSE

NO ! If anything like i listed above does occur exactly while your transact is running, you will end up with the wrong identity.

Answer 7

It depends on what else is running at the same time as your transaction is instantiated. If there is a trigger on a table unrelated to the transaction that can insert a new identity value, the transaction scope you are currently in will not protect you.

For example say I create a SPROC that updates Table_A and inserts a record into it. This table has an identity field on it that will increment the ID value in that table each time a new record is inserted. Inside my SPROC I create a transaction and place my insert inside the transaction. After the insert I store the value of @@IDENTITY in a variable inside the same transaction.

Now I also have another table Table_B with it's own identity value but this table is trigger maintained. If I am executing my SPROC to insert a row in Table_A and during this update Table_B is also updated via a trigger, it is possible that when I retrieve the value of @@IDENTITY, it will actually give me the value of the ID created for Table_B rather than Table_A.

You should definitely use Transactions in your stored procedures but you are better off selecting the MAX(ID) of the table you inserted into to retrieve the ID you created rather than @@IDENTITY.