October 30, 2009 at 1:28 am
I have a table called "CompanyHistory"
I have 2 jobs
1. populatehistory - Writing the history of the company into one table
2. calculatehistory - Another job accessing the same table to read
once i write data into companyhistory i need to do dome calculation after that using calculatehistory. The calcuatehistory takes the data for computation from a queue
Question
1. If the 2 jobs run simultaneously it does write and read on the same table will i be getting any deadlocks and the calculate will not happen
2. Is there any other problem i will face
It will be helpful for my coding
October 30, 2009 at 3:13 pm
goodwill_guy (10/30/2009)
I have a table called "CompanyHistory"I have 2 jobs
1. populatehistory - Writing the history of the company into one table
2. calculatehistory - Another job accessing the same table to read
once i write data into companyhistory i need to do dome calculation after that using calculatehistory. The calcuatehistory takes the data for computation from a queue
Question
1. If the 2 jobs run simultaneously it does write and read on the same table will i be getting any deadlocks and the calculate will not happen
2. Is there any other problem i will face
It will be helpful for my coding
Both questions rely heavily on the logic on your program/job. You said you need to do some calculations on the newly inserted data. you also say the jobs may be running at the same time. This would mean you need a way to make sure the reading job will ONLY run after the insert job. Or if your reading job is already running, your writing job will wait, but then you'll have to recalculate the data afterwards anyway.
but again, this all really depends on what you need.
October 30, 2009 at 6:38 pm
As long as your second job is PURELY reads, then you shouldn't get deadlocks. However, it WILL be blocked by the first (updating) job while the first is running. That blocking can be mitigated if you set the second job to have all NOLOCKs or READ UNCOMMITTED isolation level. But those do carry other consequences such as the potential for invalid data states.
Actually, if the second job were to kick off first, it could actually block the updating job too come to think of it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply