Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Record Locking

Author  Topic  haefeli Starting Member 6 Posts Posted - 2001-04-30 : 11:06:02 I would like to review the topic of record locking and transaction integrity in general. A typical application is, you have some content and would like to insert it into a table only if the value doesn't already exist. In any case, return the id number of the record where it is located. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- needed? BEGIN TRANSACTION SELECT @id = id ... WHERE content=@content if @id = 0 -- if doesn't exist already begin INSERT ... set @id = @@IDENTITY end COMMIT TRANSACTION return @id -- for example Putting the SELECT within the TRANSACTION block (should) ensure that no one else could have inserted the same record between the SELECT and the INSERT. Question is, is SET TRANSACTION ISOLATION LEVEL REPEATABLE READ necessary to gaurantee that there will be no collision and that the data is clean? Various angles on this would be appreciated.