Lock problem with linked views in access 97

[TiagoM]

Hi,

I have an access 97 database on wich many users update and insert
information. Recently I link some views of a SQL Server 2005 database and I
have triggers of update and insert in these triggers, so the user can modify
information through the views.

The problem is: each time a user insert/update one record in a view all the
tables used in that view stay locked...even if the update or insert ends. I'm
missing something?


Thanks.

 

[Stefan Hoffmann]

hi,

I have an access 97 database on wich many users update and insert
information. Recently I link some views of a SQL Server 2005 database and I
have triggers of update and insert in these triggers, so the user can modify
information through the views.

Huh, you don't need triggers on views to update or insert data if these
views are key preserving.
You only need to create an artifical primary key on the linked table in
Access to do so.

The problem is: each time a user insert/update one record in a view all the
tables used in that view stay locked...even if the update or insert ends. I'm
missing something?

Sounds like lock escalation. In these case you need to trouble shoot it
using SQL Profiler to nail down the triggering SQL and use sp_who,
sp_who2 and sp_lock to get.



mfG
--> stefan <--

 

[Tom van Stiphout]

On Wed, 1 Jul 2009 05:40:01 -0700, TiagoM

You may want to run SQL Server Profiler to see what's going on in more
detail. "select * from sys.dm_tran_locks" may help as well.

-Tom.
Microsoft Access MVP