none
SSIS package exclusive lock on source table

    Question

  • I have created a package that copies data from one table to another table on a different server. When I run the package, most of the time it runs for a while and then hangs.  I am using the method found in this website: http://blogs.msdn.com/b/jorgepc/archive/2010/12/07/synchronize-two-tables-using-sql-server-integration-services-ssis-part-i-of-ii.aspx for synchronizing the tables.  In BIDS as I am watching the execution of the package all objects turn Yellow in the Data Flow window and the counts do increase till it hangs.  Then the counts stop.

    I have run the SQL Server "Resource Locking Statistics by Objects" report on the source server/database and it reports there are two locks on that table by my userid at the time I am running the package (a page lock and a object lock).  All I am doing with the table is reading it.  In the package the OLE BD Source(SQL Server Native Client 10.0) I am using a query "select * from table where id between ? and ?".  "id" is an identity column and a clustered index.

    This package does work once in a while.  There was never any problems in the Development environment.  This is occurring in Production where another application is writing data to the table all day long.  The Development environment did not have another app running at the same time.

    The package also copies 6 other tables with no problem.  The problem table is larger (more columns) than the other tables.

    Any ideas of what the problem might be?

    Fred


    Fred Schmid



    • Edited by CRdesigner Monday, January 13, 2014 5:15 PM
    Monday, January 13, 2014 2:48 PM

Answers

  • "OLE DB Source - Dest Table - CI Results"(top right component) and "OLE DB Destination - CI Results"(bottom component) are the same table.  The one that is different is "OLE DB Source - Source Table - CI Results"(top left component).


    Fred Schmid

    You're often going to have trouble streaming data into the same table you are simultaneously selecting from, especially if the row count is high. In this situation I employ a staging table or raw file for the destination and the then have an extra step to dump this into the destination table.
    • Marked as answer by CRdesigner Friday, January 24, 2014 4:34 PM
    Tuesday, January 21, 2014 10:49 PM

All replies

  • You are most likely experienced a deadlocking, use the SQL Porfiler with the Dedlock template.


    Arthur My Blog

    Monday, January 13, 2014 9:43 PM
  • I am not a DBA so I have not used SQL Profiler before.  I did not find a template called Dedlock.

    I did figure out how to create a new trace and select some Deadlock events, but I did not see anything that would help me.

    Is there a way to tell the Dataflow to restart if it times out?

    I tried switching the Merge Join in my Dataflow to a Lookup which helps.  It runs a lot faster when it works.

    I am now trying replacing the problem table Dataflow with an Execute SQL task using the INSERT statement.

    Now I am having problems with the other tables.


    Fred Schmid

    Wednesday, January 15, 2014 7:24 PM
  • "At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab"...See Analyze Deadlocks with SQL Server Profiler

    you cannot restart if times out, it is likely to stall again. But you can use check pointing to restart from the moment of failure.

    Tell us more about how you designed the package I trust we may help you eliminate this issue.


    Arthur My Blog

    Wednesday, January 15, 2014 8:05 PM
  • I set the Profiler trace as you said. I ran my package.  Looking at the deadlock.xdl file it says "Failed to initialize deadlock control. There is an error in XML document (0.0). Root element is missing."  What does that mean?

    When the package stopped going further (the last Dataflow to process stayed Yellow and the counts, that increase when it is running, stop increasing) I did not see anything helpful (to me) in Profiler so I checked the Resource Locking Statistics by Objects report and the only lock was on System Resources, SHARED_TRANSACTION_WORKSPACE and not the table the last Dataflow was running against.

    My dataflow looks like this

                                                       |

    The Merge Join joins on an indexed field.

    The Conditional Split selects all Nulls from the Dest Table as New Records.

    I populate a new field in the Dest table with Add IdEpoch.

    There was never any problems in the Test environment.  It never stopped.

    The production environment has an application that is writing records to the table non stop all day long.  This is a manufacturing environment and this application is logging statistical data in the database.  The source database is SQL Server 2008 r2 Express edition.

    Thanks for your help.


    Fred Schmid



    • Edited by CRdesigner Thursday, January 16, 2014 3:56 PM
    Thursday, January 16, 2014 3:36 PM
  • no image

    Arthur My Blog

    Thursday, January 16, 2014 3:41 PM
  • Sorry,  I tried to paste instead of insert the image.  It is there now.

    Fred Schmid

    Thursday, January 16, 2014 4:08 PM
  • Yellow at CI Results?

    It must be a wait.

    How did you configure it?

    Use the bulk load "fast load", not table option to push the data thru. Keep TabLock


    Arthur My Blog

    Thursday, January 16, 2014 4:30 PM
  • I assume you mean the OLE DB Destination object.  Yes, the Data access mode is "Table or view - fast load".  Rows per batch is empty.  I kept all the default settings.

    AccessMode - OpenRowset Using Fastload

    AlwaysUseDefaultCodePage - False

    CommandTimeout - 0

    DefaultCode Page - 1252

    FastLoadKeepIdentity - False

    FastLoadKeepNulls - False

    FastLoadMaxInsertCommitSize - 2147483647

    FastLoadOptions - TABLOCK, CHECK_CONSTRAINTS


    Fred Schmid


    • Edited by CRdesigner Thursday, January 16, 2014 4:51 PM
    Thursday, January 16, 2014 4:50 PM
  • Are the queries in the two OLEDB sources identical and use the same source DB?


    Arthur My Blog

    Tuesday, January 21, 2014 4:31 PM
  • The queries are basically the same.  The only difference is in the WHERE clause where they select a range.  The source DBs are not on the same server (which also are in different locations - cities) and the databases have different names but the table names are the same.  The source table which is also the destination table has 2 more columns than the other source table.  Since the destination table is an archive of the source table, the additional fields are an id (identity) field and idepoch to keep track of a range of logids (which is the source table's identity field)

    Fred Schmid

    Tuesday, January 21, 2014 6:40 PM
  • so, in short, all three are not the same tables, right?

    And when you run the package the bottom component (OLEDB destination) turns yellow where there are no errors seen, correct?

    If you place the Data Viewer, do you see records coming in? How many? Same count each time?


    Arthur My Blog

    Tuesday, January 21, 2014 6:46 PM
  • "OLE DB Source - Dest Table - CI Results"(top right component) and "OLE DB Destination - CI Results"(bottom component) are the same table.  The one that is different is "OLE DB Source - Source Table - CI Results"(top left component).

    Yes, all the components turn yellow. 

    The counts coming into the "OLE DB Destination - CI Results"(bottom component) are usually less than a 100 and it varies every time I run it because new records are being added to "OLE DB Source - Source Table - CI Results" all the time by the end user application.

    I run the same sql query from the SSIS package source components in a SSMS query window with the same WHERE clause and the counts in the SSIS package are about half of the results in SSMS.  It looks like it stops about half way through the dataset.

    When I was testing this, my test source database was in the same building as the destination server database but different servers.  The only difference was the end user application is running on the production source database.


    Fred Schmid

    Tuesday, January 21, 2014 8:08 PM
  • "OLE DB Source - Dest Table - CI Results"(top right component) and "OLE DB Destination - CI Results"(bottom component) are the same table.  The one that is different is "OLE DB Source - Source Table - CI Results"(top left component).


    Fred Schmid

    You're often going to have trouble streaming data into the same table you are simultaneously selecting from, especially if the row count is high. In this situation I employ a staging table or raw file for the destination and the then have an extra step to dump this into the destination table.
    • Marked as answer by CRdesigner Friday, January 24, 2014 4:34 PM
    Tuesday, January 21, 2014 10:49 PM
  • I'm confused.

    If i'm reading correctly you're saying that the locking is on the source table-ci results which you only read from. Is that correct?

    But (my personal) experience and logic points to the problem being with your destination - ci results src and ci results destination being the same table.

    Have you tried replacing the destination with a dummy object like a union all and trying that in production? If it zooms through and turns green then you know your problem is on the destination not the "source table-ci results"

    Reply back with your findings and we'll be able to help you fix the part that's causing you issues.

    What you're doing is a fairly standard merge & it's fairly easy to set up. You say that you're limiting the data in your source-ci results oledb source using an id range. Are you doing the same in your dest table-ci results oledb source?


    Jakub @ Adelaide, Australia

    Wednesday, January 22, 2014 4:15 AM
  • I employed a staging table (as you suggested) for the destination and then added another Dataflow component to copy the staging table to "OLE DB Destination - CI Results".  This has been running for the last several hours (once every hour) with no errors.  Everything seems to be working.

    Thanks for your help.


    Fred Schmid


    • Edited by CRdesigner Friday, January 24, 2014 4:44 PM
    Friday, January 24, 2014 4:41 PM
  • I believe it is probably locking both source and destination tables so when it attempts to write to it later, it hangs.  Now with using the staging tables instead of the destination tables, it can complete the first copy before the last copy to the destination tables. Replacing the bottom destination table with a staging table solved the lock problem.

    Thanks,


    Fred Schmid

    Friday, January 24, 2014 4:50 PM