Problem with Identity property in case of concurrent access
- Hi All,This is basically about the Identity attribute of a field and its behavior during concurrent access.Say, there is a field called 'col' in a table 'TAB', I want to make this integer column as IDENTITY, so it will take incremental values when new records are inserted. But in our application, since we are using a clustered environment and multiple MDBs accessing the table and making entries. I suspect that the IDENTITY set on the field might have some issues, for example when one thread tries to insert a new row, another one can also try and there might be a clash in that case.Can you explain if SQL Server handles it, for example, it could have a mechanism, where it allows only one thread or connection to make an entry, and after that thread is done, it could allow the next one to occupy the table and make an insert, in this way it can be safe.Please confirm whether we can just make the inserts into the table with all the other values explicitly and that SQL Server will take care and make sure that we do not land up in problems, even when we have multiple threads inserting into the Table, from our application we will have multiple threads initiated to make inserts into this table.Thanks a lot.Vicson.
-
To my knowledge you should not have any problems. I would suggest you put the SQL code into a stored procedure (obviously) and as long as you have your begin tran and commit tran statements in place you should be fine.
If the phone doesn't ring...It's me.
-
SQL should indeed be able to handle sorting out generating the identities in this scenario. What you should be aware of is the different methods of returning the generated identity to the client. Check Books Online and make sure you understand the SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT().
--------------------
Colt 45 - the original point and click interface
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply