Hi,
I have a blocking situation occurring that I am having trouble sorting out. It occurs when using MS DTC and using SSIS as the transaction co-ordinator. I suppose you might argue that this should be in the SSIS forum but as the blocking is occurring in SQL Server I thought I'd try here as well.
I have 2 data-flows in SSIS. One of them INSERTs to mytable, the other UPDATEs mytable. The UPDATE is getting blocked by the INSERT which holds a lock on the table (I can see this thru sp_lock). Now although this is from SSIS all you really need to know is:
-These 2 operations occur using seperate connections
-I am running the 2 operations under the same MS DTC transaction
-mytable has a PK on it. If I remove the PK then my observations so far suggest that the blocking problem disappears
-The default isolation level on the DTC transaction is Serializable. I have tried various others and got the same problem
-The locks are held by SPID=-2 (which, as far as I am aware, is DTC)
Also note that:
-I can put the 2 operations in the same data-flow which means they occur under the same connection
Now, I'm not too hot on transactions and DTC etc... Is there anything I can do here to stop my blocking problem? Or am I just scuppered?
Feel free to ask questions and I'll answer qwith as much detail as I can. I'd appreciate any advice anyone has in resolving this.
-Jamie
P.S. Here's the thread I posted on the SSIS forum about this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154834&SiteID=1
It seems as though page level locking is occurring. Does anyone have any advice as to how to go about eradicating this? I understand that row-level locking is possible now in SQL Server. In theory this would solve my problem because I am not updating records that I am inserting.-Jamie
