April 8, 2010 at 2:33 am
Hello
In our current project we are interfacing with a third party data provider. They need to insert data in a table of ours. This inserting can be frequent every 1 min, every 5min, every 30, depends on the amount of new data they need to provide. The use the isolation level read committed. On our end we have an application, windows service, that calls a webservice every 2 minutes to see if there is new data in this table. Our isolation level is repeatable read. We retrieve the records and update a column on these rows.
Now the problem is that sometimes this third party provider needs to insert a lot of data, let's say 5000 records. They do this per transaction (5rows per transaction), but they don't close the connection. They do one transaction and then the next untill all records are inserted. This caused issues for our process, we receive a timeout.
If this goes on for a long time the database get's completely unstable. For instance, they maybe stopped, but the table somehow still stays unavailable. When I try to do a select on the table, I get several records but at a certain moment I don't get any response anymore. It just says retrieving data but nothing comes anymore until I get a timeout exception.
Only solution is to restart the database and then I see the other records.
How can we solve this. What is the ideal isolation level setting in this scenario?
April 8, 2010 at 6:09 am
I don't think it's (only) a matter of choosing the appropriate isolation level, but, according to your description of the problem, looks like there's some commit/rollback instruction missing on the writing app.
Are you aware that cycling the SQL service will roll back the uncommited transactions?
Have you tried looking at the connections and their active transactions?
-- Gianluca Sartori
April 8, 2010 at 6:21 am
Thank you for your response,
How can I do this?
April 8, 2010 at 6:39 am
When your reader app is blocked, open the Activity Monitor and try to identify the connection used by the writer app.
I don't remember if SQL 2005 SSMS Activity Monitor displays the "Open Transactions" column, but it should be there. If there's an active transaction even when the writer app has finished writing, there's something wrong with that program (probably a COMMIT is missing).
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply