January 8, 2013 at 10:21 pm
Hi all,
I have a requirement where in I need to select the date column of a table while it will be updated in 10 -15 mins after our ETL is completed. I put the below script as last step of our ETL.
Now i need to get the date from the final table after its update and insert it into another table.
while (1= 1)
BEGIN
IF Exists (select top 1 LastUpdatedDate from dbEBIController..RequestQueue (Nolock)
where RequestTypeID = 14 and Isprocessed = 1 and lastUpdatedDate > dateadd(hh,-2,GETDATE()) Order by RequestID desc )
BEGIN
Print 'DMPropagation in XYZ Server Completed. Proceeding to Next step of collection of Refresh timings.'
-- I will Include the insert Statement here.
Break;
END
CONTINUE
END
My Estimation is the above loop will run for 10-15 mins and get the date. By doing this will I create a deadlock??? If yes, what can I do to avoid it.
Please suggest.
Thanks in advance.
January 9, 2013 at 4:48 am
It's hard to know if it will create a deadlock or not without being able to compare other code to understand where locks might occur. Will it create a blocking situation as opposed to a deadlock? Again, hard to say for absolutely certain. You're using NO_LOCK to try to avoid the shared locks of the read, but a 15 minute insert is very likely to take out a large number of locks and clearly hold them for a long time. This will lead to blocking. And, it could lead to deadlocks, but it's hard to know without knowing how other code accesses that same table at the same time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2013 at 4:59 am
My Bad I might have miscommunicated. My estimate of 15 mins means it would take 10-15mins min for the Post ETL process to come and hit this table that I'm trying to access.
I have no other start point available except start a while loop as soon my ETL is completed.
So now this will be the scenario. My while loop with NoLock will be running continuously for 10-15 mins against a table then only 1 record of the same table is updated by our Post ETL process. Just 4 columns of that record are updated.
Will this create issues. ???
January 9, 2013 at 5:11 am
I must be dense or something as I can't figure out what it is you are trying to do based on your description of the problem.
I can't tell if your ETL process is supposed to be inside the WHILE loop of your psuedo code or if this is doing something (don't know what from what is posted) during the WHILE loop at the same time as your ETL process, or what.
It would help to know what is being done inside the while loop and it would help if you provided a flow chart detailing how things are supposed to be processed.
January 9, 2013 at 5:12 am
I'm still back to hard to say, but, in general, I would avoid holding open a query, on purpose, for 15 minutes under any circumstances. The fundamental concepts of transactions should always be, open as late as possible, close as soon as soon as possible, do as little as possible. I just don't understand why you need to keep the query open for the load process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2013 at 5:20 am
Grant Fritchey (1/9/2013)
I'm still back to hard to say, but, in general, I would avoid holding open a query, on purpose, for 15 minutes under any circumstances. The fundamental concepts of transactions should always be, open as late as possible, close as soon as soon as possible, do as little as possible. I just don't understand why you need to keep the query open for the load process.
I will also agree with this.
January 9, 2013 at 6:57 pm
Yes, I understand and I do know that holding up an open query fro so hold is certainly not good. But as per our design of the Environment I'm restricted to 2 options.
1) either do a while loop or
2) write a update trigger on that table with when Isprocessed column is updated to 1 then push the communication email.
I do not know how to write a update Trigger. I guess I will learn now.
January 10, 2013 at 6:17 am
So when the load is complete, you need to send an email? Couldn't you just add that to the end of the load process? A trigger will work, although I try to avoid triggers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply