February 11, 2009 at 6:12 am
Hey all,
I have this situation that I am trying to figure out but am having trouble with it. I am creating a process that is performing some inserts and updates on data on one server (lets call it Target). This table (lets call it LOOKUP) has a Primary Key identity (1,1). It is taking in unique combinations of fields and then assigning the Primary Key to the data.
I need to use LOOKUP on another server (lets call it Target2) which has production data to do a historical update. I will need to insert into Target2.LOOKUP and update the data accordingly. So, as I insert new records the PK value will increase.
I need to be able to propegate back the new values i just created and also prevent another user from inserting into Target.LOOKUP until after my newly created ID that I moved from Target2.LOOKUP have been added. This way my PK and the associated values are always in sync.
What I am trying to do is the following:
1. Begin transaction on Target SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2. Issue an XLOCK on Target.LOOKUP and pull the data from Target to Target2.
3. Insert new data into Target2.LOOKUP
4. Move the new values created in Target2.LOOKUP to Target.LOOKUP
5. Commit transaction
My problem is that will this is running, I am issueing an insert statement to Target.LOOKUP to add new values. My lock and Isolation level is great, it sits and waits. My problem is that when my process is done, it doesnt insert the new records from Target2.LOOKUP into Target.LOOKUP. Is thought that if I issues the lock and the isolation level that I could still insert into the table.
If someone can help that would be awesome. I even have a screenshot of my SSIS package that is performing all the actions if that can help.
Thanks in advance!!
February 11, 2009 at 10:41 am
You are looking for DISTRIBUTED TRANSACTIONS in a way that is not appropriate.
You should re-think your solution.
-Noel
* Noel
February 12, 2009 at 12:00 pm
[font="Verdana"]Wow, that's a lot of work.
Why not just insert into the same table on the same server? Once you've done the insert, then pull back the results.
Or am I missing something?
[/font]
February 12, 2009 at 12:39 pm
Hey all,
With a little more testing i got all of this work perfectly. I can lock the table on Target so that nothing can be inserted until the process on Target2 is completed and reinserted back into the Target table.
Pretty cool actually.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply