SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

by

If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or “rolled back”, they would be blocked by the existing transaction using the auto-number. For example, consider my psuedo code below with table A using an auto-number field for the ID column:

User 1
------------
begin transaction
insert into A ...
insert into B ...
update C ...
insert into D ...
commit


User 2
-----------
begin transaction
insert into A ...
insert into B ...
commit

If user 2’s transaction starts a millisecond after user 1’s, then their insert into table A would have to wait for user 1’s entire transaction to complete just to see if the auto-number from the first insert into A was used.

This is a feature, not a bug. I would recommend using another scheme to generate auto-numbers if you need them to be tightly sequential.

More Related Contents:

  1. Using “GO” within a transaction
  2. How do parameterized queries help against SQL injection?
  3. store image in database or in a system file? [closed]
  4. How can I solve a connection pool problem between ASP.NET and SQL Server?
  5. SQL Server – transactions roll back on error?
  6. .NET Entity Framework and transactions
  7. Login failed for user ‘DOMAIN\MACHINENAME$’
  8. SELECT * FROM X WHERE id IN (…) with Dapper ORM
  9. Bulk Insert to Oracle using .NET
  10. Parameterized Queries with LIKE and IN conditions
  11. Does End Using close an open SQL Connection
  12. Does this code prevent SQL injection?
  13. Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)
  14. How do I write LINQ’s .Skip(1000).Take(100) in pure SQL?
  15. How can you name the Dataset’s Tables you return in a stored proc?
  16. How can I create database tables from XSD files?
  17. How can I avoid SQL injection attacks in my ASP.NET application?
  18. Why is some sql query much slower when used with SqlCommand?
  19. Is it possible to send a collection of ID’s as a ADO.NET SQL parameter?
  20. How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?
  21. How do I convert a string into safe SQL String?
  22. Implementing transactions over multiple databases
  23. Nested/Child TransactionScope Rollback
  24. Regular expression to find all table names in a query
  25. How to return a page of results from SQL?
  26. Using a variable for table name in ‘From’ clause in SQL Server 2008
  27. Is the LIKE operator case-sensitive with SQL Server?
  28. What does Html.HiddenFor do?
  29. Cross Join without duplicate combinations
  30. .NET 4 fails to install because SECUREREPAIR fails to CreateContentHash of file SetupResources.dll: for computing hash Error: 997

Leave a Comment