Problem with concurrent insert and update commands

Problem with concurrent insert and update commands

• Dan Rux

  "you could push the 2 datasets into raw files and then process those 2 files seperately"
  Yes, I could... But then I lose the gain of using the data flow.
  
  I have managed to avoid the deadlock situation by doing:
  1) Removing the table lock option in the ole db destination
  2) Changing the db access mode in the ole db destination from fastload to openrowset.
  
  The first one seems a bit unsafe, but what about the second one The only negative with it, is that performs much slower than the using fastload. But are there any other drawbacks
  
  

• Dmitry Sychov

  I have had similar problems where I had multiple operations on the same table in the data flow.
  
  I would try setting the batch size to around 4,500 - Leave the Table Lock off, set your access mode back to fastload.  If you are inserting more than ~5000 rows the bulk-load does an auto-escalate of your locks and will block other components.
  
  Here is Ashvini's blog with more detail:  http://blogs.msdn.com/ashvinis/archive/2005/08/15/451964.aspx
  
  Let me know if this helps.
  
  Evan Black
  
  
  
  
  

• Peter Gloor

  Now, this is my explanation of what's maybe going on:
  
  I have got one ole db source which extracts rows to a splitter which sends some rows to a ole db destination (bulk inserting) and some others to an ole db command executing update statements.
  
  I think the problem is that the update-command gets suspended to wait for the bulk insert to finish. That would normally be ok, but somehow the source stops producing rows! ! Maybe because the pipeline is full (could that be ) since the update command doesn't consume any rows
  
  In that case it is like this: The bulk insert waits for new rows from source. The source waits for the update command to consume its rows. The update command waits for the bulk insert to finish... =)
  
  Hrm...
  

• Shobha69358

  OK, its dead easy. sp_who2 is particularly useful. Just type "sp_who2" in a query window in management studio. It will show you all the running processes(or SPID - server process ID) and the BlkBy column will contain, for every process that is blocked, the SPID of the process that is blocking it.
  
  Here's a simple test to demo the use of spwho2:
  1. In a query window execute:
  

  create table test (col int)

  begin tran

  insert into test values (1)

  
  2. In ANOTHER query window execute
  

  update test set col=2

  
  3. In ANOTHER window execute
  
  

  sp_who2

  
  it will show that the UPDATE statement in the second window is being blocked the INSERT in the first window.
  [N.B. To come out of all this type ROLLBACK in the first wndow and execute it]
  
  I am guessing that the same thing is basically happening in your data-flow.
  
  -Jamie
  
  
  

• Adi Arbel

  Hello everybody.

  I think, recently I have experienced the same situation. In my package I had to sort the data flow using two conditional splits. Filtered rows (from both splits) had to be written in the same table of the SQL server. When I ran it with smaller number of rows (below 10), everything was good. As soon as the number of rows grew up (in the area of 300 to 400), the execution got stuck.

  As it was mentioned above, the solution is a table lock - just remove this option from the "OLE DB Destination" component. But I would like to know, what happens, when both "OLE DB Destination" components try to write in the same table. Will the data (of the "looser of the race") be lost If no, will the "looser" slow down the execution by waiting for the possibility to write

  Any ideas

  Regards from Hamburg,

  A.G.

  
  

• Foss

  Thanks Jamie... I think I found something weird there!
  
  Result from sp_who2:
  ...
  53 SUSPENDED  IBITEC\samuelj SAMUELJ-W2K3-VM . NorthwindDW BULK INSERT 0 1 10/07 14:35:08 Microsoft SQL Server   53    0
  ...
  56 SUSPENDED  IBITEC\samuelj SAMUELJ-W2K3-VM 53   NorthwindDW UPDATE 0 0 10/07 14:35:08 Microsoft SQL Server  56    0
  ...
  
  Hrm Two suspended proceses  After some testing, I'm quite sure that these two are the ones that my data transformation task is using.
  
  What to do next =)
  
  

• RSoh

  I have come across this a couple of times, My work around was to keep the 'Bulk Insert' and drop the updates to a RAW file and process them seperately. If performance is not an issue change the 'Nulk Insert; to a simple 'Insert'
  
  

• Mr. Emu

  That sounds strange. There's no reason for them BOTH to be suspended. They're not blocking each other - that would be a deadlock which SQL Server would pick up.
  
  In fact it doesn't even look as though the insert is blocked so i don't know why it could be suspended. You probably need a DBA from here on in and I aint a DBA I'm afraid
  
  -Jamie
  
  
  

• vamsib

  Are you inserting into SQL Server Try executing sp_lock and sp_who2 to see if there's any locking issues!.
  
  -Jamie
  

  
  
  

• Er Hazmo

  Yeah, I probably do! =)
  But thanks for your help Jamie!
  
  I'll think about redesigning the data flow task!
  
  

• Cindy Meister

  Samuel L wrote:

  Yeah, I probably do! =) But thanks for your help Jamie! I'll think about redesigning the data flow task!

  
  
  In the interim you could push the 2 datasets into raw files and then process those 2 files seperately.
  
  -Jamie
  
  
  

• sbatati

  "Are you inserting into SQL Server " 
  Yes!
  "Try executing sp_lock and sp_who2 to see if there's any locking issues!"
  Ok, don't know anything about those things... =)
  How do I use those commands
  
  Thanks for your help Jamie!
  
  

• JoeKickass

  If you have disabled table lock, then both destinations should succeed and no data should be lost. It may take longer, though, since you have multiple destinations writing to the same table at the same time.
  
  
• Problem with concurrent insert and update commands