September 19, 2008 at 3:28 am
We currently have an old Access application which is linked to a SQL Server 2000 database. We are currently working on replacing it, but it has just started causing us problems with blocking locks. Ideally I don't want to spend too much time fixing this application, since it's replacement is coming soon.
What's happening is that there's a search screen which comes up with a text box in which users can type part of a company name to restrict the list by. If the text box is empty, then the list shows about 6800 records and waits for user to do something else.
While the search screen is in this state, there is an "intent shared" lock on the table, and if anybody tries to make an update, then they get blocked.
Although the application has worked this way for many years, the blocking locks were a very rare occurrence (problem one in every 6 months if that) before yesterday, but yesterday it was happening all day.
In the short term I've told all the users never to leave the search screen up like this for any length of time.
I've had a few ideas on changing the program so that it only ever returns the top 20 (or something) in the search - since users never need to see more than that, they are usually just getting their coffee when this occurs having just opened the search screen.
I'd like to understand what might cause it now though - if it is doing it now, why didn't it do it before? What could have changed to make this start happening?
I've run a profiler trace to check for lock escalation, and there has been none.
Thanks,
Rachel.
September 19, 2008 at 9:23 am
Is the Access application using pass through queries? If not, Access has a bad habit of trying to grab the whole table and perform the query in its own engine. If it is already using pass through queries, you can check the transaction isolation level of the connection, or try using the NOLOCK hint on the SELECT query. This would allow "dirty reads" though, meaning that a user could potentially see uncommitted data from a separate user's transaction.
September 23, 2008 at 10:26 am
Thanks for your help.
I haven't actually tried any of your suggestions, but very bizarrely everything had sorted itself out the following day.
I have decided to put my efforts into working on the replacement rather than fixing the existing one, but I will keep your suggestions ready in case it happens again.
Thanks,
Rachel.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply