sql server - Serializing INSERTs -

if answer question depends on dmbs, i'd interested hear answer oracle 11g or higher , sql server 2012.

we have table has foreign key references itself:

create table versions (     id int identity(1,1) not null,     [date] datetime not null,     basedonversion int null  -- foreign key references versions ) 

we have stored procedures insert new records versions table. if these running concurrently, need make sure no 2 versions reference same other version, there must not forks in version hierarchy (unless we're deliberately creating fork):

how should run                            transaction 1 reads current version 17 transaction 1 writes new version 18 based on 17 transaction 2 reads current version 18 transaction 2 writes new version 19 based on 18  how should not run  transaction 1 reads current version 17 transaction 2 reads current version 17 transaction 1 writes new version 18 based on 17 transaction 2 writes new version 19 based on 17 

in second case, have 2 versions based on same version.

so need way serialize 2 transactions. first, thought of using isolation level serializable, isolation levels not have impact on inserts, they're not solution.

another way acquire lock on versions table @ beginning of transactions. work sql server, not oracle, since oracle's lock table in exclusive mode not prevent transaction 2 reading table.

so what's best solution kind of problem?

this known "lost update problem" , has number of solutions. not of them require pessimistic locks.

one way modify "write" method use predicate in clause stop update updating version if current version not same session read.

i.e.

1. transaction 1 reads current version 17
2. transaction 2 reads current version 17
3. transaction 1 writes new version 18 based on 17
4. transaction 2 tries write new version 18, update v=18 v=17 results in "0 records updated", , session handles error, e.g. "another user has modified version, please requery , try again".

i wrote on in 2005 , listed number of approaches:

http://jeffkemponoracle.com/2005/10/21/avoiding-lost-updates-protecting-data-in-a-multi-user-environment/