Database Blocking is Out of Control

Question

Database Blocking is Out of Control

John-79567

SSCommitted

Points: 1892
More actions
August 2, 2005 at 4:01 pm

#90295

We are experiencing a large number of blocks on a view.
The client application is MS - Access 97.
The client app runs a select against a view. The users have update rights to the view and underlying tables. When ever a user keeps the form open they inadvertantly begin blocking other users. Is this a database issue or application? Any help would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

chrisrock Old Hand Points: 369 More actions · Answer 1

We get a lot of blocking from one of our departments that manipulate data through access 2000. I believe that it's an application issue.

_______________
Convert DTS to SSIS | Database Documentation | SSIS Performance | SSIS Monitoring

David le Quesne SSCertifiable Points: 5229 More actions · Answer 2

I had the same problem with an application written in Borland Delphi. Whenever anyone ran a large report or updated 'child' data on a subsidiary screen I got blocking transactions.

Managed to solve the problem by using table locking hints, specifically WITH (NOLOCK) for reporting queries and WITH (ROWLOCK) for single row updates.

Also found that my application was issuing BEGIN TRANSACTION statements before executing subsidiary screens. Because SQLServer 2000 doesn't nest transactions properly, only releasing the locks when COMMIT statements return it to the outermost transaction, I made sure I forced a commit after every operation that altered the data.

Finally, in some cases (one or two lengthy stored procedures), I had to set implicit transactions on and set the isolation level to read committed. I don't use Access, but understand that you can set the default isolation level of the ODBC client

I still get the odd blocking transaction, but I have a process which alerts me if such a situation arises so that I can find out from the user what they were doing that caused the block.

If it ain't broke, don't fix it...

JP de Jong-202059 SSCommitted Points: 1674 More actions · Answer 3

hi

form in access -> check the AllowEdits, AllowAdditions & AllowDeletions properties and the RecordLocks property

report in access -> check the recordlocks property of the report.

JP

sergioman SSC Enthusiast Points: 166 More actions · Answer 4

Just as an FYI, Access performance full table scans when you have linked tables, which in turn locks the tables.

John-79567 SSCommitted Points: 1892 More actions · Answer 5

thanks for all the replies. I believe we isolated the issue. The developer linked to a view, but neglected to specifiy a unique key or keys.