Page 1 of 1
Job Hanging while updating sql server table
Posted: Mon Dec 16, 2013 11:34 am
by DSRajesh
Hi All,
I am trying to select and update the same table in the job and while running the job it is keep on running.
I am trying to update 3 different tables with 3 different odbc connector stages .
job design is like this:
odbc -->Transformer-->odbc
in the first odbc using select statement and in the second odbc using update statement on same table.
I ran with out any locks till last week..while i run today i am able to see the same in development.
Looking forward for any suggestions on this.
Posted: Mon Dec 16, 2013 11:57 am
by chulett
Is this a parallel job? I only asked because you posted in the Server forum.
Posted: Mon Dec 16, 2013 1:00 pm
by DSRajesh
sorry,this is the parallel job
Posted: Mon Dec 16, 2013 1:46 pm
by chulett
And so off we go!
Can you specify what kind of locks you are seeing? Posting the actual error messages should help, I would think.
Posted: Tue Dec 17, 2013 6:21 am
by DSRajesh
there is no specific error message but job hangs and running contuously.
do we need to change any setting in odbc connector stage.
currently we have record count as 2000,array size as 2000.isolation level as read commited at source stage.
Looking farward for the suggestions on this
Posted: Tue Dec 17, 2013 8:37 am
by chulett
Well... classic first question would be, if you run this on a single node does the problem go away? Secondly, have you had your DBA monitor the session when it is hung?
Posted: Tue Dec 17, 2013 9:44 am
by asorrell
What kind of database are you accessing? Can the DBA monitor for usage on the file? Typically it isn't the job that is hanging it up, it is someone else accessing the table that is preventing the job from continuing,
Posted: Wed Dec 18, 2013 6:53 am
by ArndW
The subject line indicates SQL Server. With sufficient access rights, you can see the individual threads and locks from the SQL Server Management Studio program.
Posted: Wed Dec 18, 2013 9:19 am
by DSRajesh
yes,There are locks on one of the table.To avoid the locks is there any configuration changes in odbc connector need to change to avoid these locks.
Posted: Wed Dec 18, 2013 9:28 am
by chulett
Where are the locks coming from? If it is from the job itself, have you tried running on a single node as previously noted? That would eliminate the target as the source of the locks but would still lock if they are being generated on the source side.
Posted: Wed Dec 18, 2013 2:07 pm
by asorrell
However, assuming the job itself is not the problem and the record lock is from something external, setting Isolation Level to "Read Uncommitted" should work.
If the entire table is locked, then you are probably out of luck, since Read Uncommitted only allows you to "get around" record level locks. Note that this will allow you to read "dirty" data, so use with caution.