I am reasonably new to SQL Server.
I am using SQL Server 2008 (no SP) on Windows XP.
I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it
does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist.
Now, this code works fine with the standard TRANSACTION LEVELS.
But I don't know what to do when I am running two instances of the same MERGE statement at the same time.
One execution could create a record in the MASTER which the other process might try and create 5 minutes later.
I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time.
I have looked at:
ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON;
But these don't seem to work with their associated TSQL calls.
I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go.
View Complete Post