Post reply

Database Blocking is Out of Control

  • 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.

  • August 2, 2005 at 7:26 pm

    #579012

    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

  • August 3, 2005 at 1:04 am

    #579028

    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...

  • August 3, 2005 at 1:35 am

    #579032

    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

  • August 3, 2005 at 10:18 am

    #579217

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

  • August 3, 2005 at 12:28 pm

    #579298

    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.

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

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