Frequent blocking in SQL
-
We get frequent blocking in the locks during the day when multiple people are using the database. It is an Access 2003 front end with SQL2000 linked tables.
Is it related to the default connection type used with Access...a friend suggests it might be the cursor.
Any help would be appreciated.
Thanks
-
Is it blocking or is the server running out of locks? If you are running out of locks it means that the available memeory is low.
What is the specification of the server - how much physical memory?
If there is blocking can you see which processes are blocking?
In Query QAnalyser run
exec sp_who2
Check the BlkBy column to see which spids are blocking. Is it user activity or a background task?
Sim
-
If you have tables that are read-only, re-link them again but skip the part where you select the fields for the primary key. This will make the table read-only, eliminating the chance of users writting to it (an therefore locking it).
Also, if you have queries that only display data, change the recordset type to "snapshot", so they are not updatable either.
Pay special attention to combo boxes, lists and other selection controls; make sure they are read-only. Users tend to type over them and overwrite their content, and that locks the entire row and/or rows related in the transaction.
Hope this help.
-
It seems to be user activity. In EM I can see the blocking and who is doing it. The "snapshot" idea is a good one. I will see if that helps.
One of the issues is that it worked fine with Access97. 3 months ago, we upgraded to Access2003. Now we are having the problems.
I appreciate all the suggestions and any more you might have.
-
Here are few more tips:
- Did you upgraded your database, or build-it from scratch on 2K3?. Sometimes the wizzard does not do a good job when converting complex forms/queries.
- In EM check what user/process is causing the lock, and review the offending process (query, form, report, macro).
- Check the properties of all the controls bounded to a query or table, subforms and the form itself, and compare them to the 97 version. It may happen that during the conversion one item may been set to "lock all rows" instead of "no locks".
- Review the closing procedures on the forms. Make sure all close all open objects, and if you have conections made on the fly, ensure they close all connections too. Sometimes a call to a function to validate something that does not terminates gracefully may be the offending code.
- Access 97 was good at handling open orphaned connections and closing them (to SQL) when idle. I don't know the thresholds or behaviour of 2K3. Check the doc. in case there is a setting to auto-close orphaned connections.
Let us know if you find the problem.
-
In addition to the excellent suggestions in the previous posts:
Enterprise Manager and SQL Profiler should help you see what queries cause blocking, query duration, and lock lifetime. But if you don't want to wade through Profiler results and want to watch what's happening in real time*, download the free trial version of "Spotlight on SQL Server" from Quest Software (or the equivalent from one of their competitors). It does a great job of helping you see what is going on, and should help you see if connections, open transactions, and/or locks are being held longer than necessary.
* Okay, it isn't technically real-time if you have to wait several seconds for a screen refresh. But it beats the heck out of SQL Profiler.
-
If it is of interest, I have some old (but good, I think) Access VBA code designed for combined error handling and lock processing - if it detects a locking error, it can be set up to retry a transaction a designated number of times after a designated delay - including a slight random amount of time, to avoid 'dueling deadlocks'.
This should only be needed if you have a 'real' locking issue - but the routine also traps and formats ALL the error information available (from the errors collection, not just the err object) so that you can do better debugging.
This was developed for DAO and an Oracle backend, but the transition to SQL Server and/or another access model should not be difficult - you would need to update the list of error numbers that designate locking conditions, for one thing.
-
What error messages are you seeing? Perhaps you are just getting write conflicts due to many people working on the same records. If this is the case, perhaps this will help:
When Access detects that a record has changed since it was last loaded, it will prevent an update. This is because Access always uses optimistic locking. As with other programming environments, pessimistic locking is only possible by coding the locking routines manually, using a record "reservation" table. You may need to ue this technique for tables that are likely to be in conflict frequently.
Aside from a reservation table, there are several things you can do to mitigate this type of problem.
1) Tell users that they must always refresh the form data (F9) before starting an edit.
2) Work as quickly as possible, to ensure that another user is unlikely to change the record while it is being edited.
3) Compose long text fields "offline," before refreshing the record. Then refresh the record and copythe long text data into the record. Save the record ASAP.
2) If a write conflict is reported, the user must decide to overwrite the record, or discard the user's edits.
HTH,
Rich
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply