Blocking when using MS DTC

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




Answer this question

Blocking when using MS DTC

  • chairuou

    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


  • Blocking when using MS DTC