I have a Microsoft Word application with an SQL Server Back end. The application has about 100 users that use about 50 forms to update and select information from tables in the SQL database. One of the tables continues to lock up on us. The table will be completely locked until our DBA kills the session that locked. The DBA told us that one session is blocking the other session. Does anyone know how to protect against this locking issue?
1 Answers
The question was answered in the comments, I'm answering here so it doesn't fill the Unanswered feed. dluck, you should probably post your own answer to the question and accept it.
Generate an estimated execution plan and use it to determine if you need indexes on your tables. Likewise, using WITH(NOLOCK) introduces some risk in the form of potentially skipped rows on deletion but should be safe for the most part. Using a combination of indexes and WITH(NOLOCK) clauses should prevent DB blocks.
Related