|
|
|
date: Fri, 14 Dec 2007 12:32:01 -0800,
group: microsoft.public.sqlserver.odbc
back
Re: Locking problem with Access 2002 and SQL 2000
If you use NO LOCK, then you can get dirty reads. However, if the data
is static and this doesn't matter, then views could solve your
problem. If dirty reads are unacceptable, then create stored
procedures that return result sets. The results are read-only, so once
the data has been fetched, no locks are being held.
-Mary
On Fri, 21 Dec 2007 03:20:01 -0800, Bertrand
wrote:
>Thank you very much for the answer! Just to clarify - you suggest that we
>deny direct access to the base table and use views and stored proc instead.
>Views however will no solve the locking issue so should the views then be
>created WITH (NOLOCK)? Is this however not equivalent to setting the
>isolation level to READ UNCOMMITTED? Until this point we have assumed that
>this would not be a problem because the database is a dataware house where
>the replication updates has some delay anyway!?
>
>Rgds
>Bertrand
>
>"Mary Chipman [MSFT]" wrote:
>
>> If users have permissions to create links to your tables whenever they
>> choose, then you have no way of controlling them. You need to revoke
>> permissions on the base tables and control the way they access data
>> through views or stored procedures. Changing the default isolation
>> level will just screw up your database.
>>
>> -Mary
>>
>> On Fri, 14 Dec 2007 12:32:01 -0800, Bertrand
>> wrote:
>>
>> >We have a datawarehouse on SQL 2000 were users are free to query using any
>> >tools available. I have discovered that users who use Access Linked Tables
>> >and Queries hold Shared Locks on the SQL tables while they browse data. This
>> >subsequently prevents the replication task from inserting records into the
>> >table. The replication holds an Exclusive Lock which prevents subsequent new
>> >queries from querying the table. All in all, the Access user blocks queries
>> >until he or she closes the table or query window.
>> >
>> >The question is: is there any easy way to alter this behaviour in Access? Is
>> >it possible to change the ODBC or set something on the server that changes
>> >the default isolation level to read uncommitted? Or do I have to use pass
>> >through queries with (NOLOCK)????
>> >
>> >Rgds
>> >Bertrand
>>
date: Mon, 24 Dec 2007 15:01:20 -0500
author: Mary Chipman [MSFT]
|
|
