Access Passthrough query locking hints
Posted on 2011-09-29
I have been doing a bit of reading on this subject of table hints and passthrough queries and I am requested some clarification on the matter.
I have created an access database with a SQL backend and access frontend. My connections with the SQL backend are via passthrough queries. I have a SQL table that all users search based on criteria entered on a form and a triggered select statement in a passthrough query to retrieve records. It is okay if dirty records are returned for the searches.
Can someone please confirm if my understanding is correct.
1. I read that if I use 'with nolock' when I reference a table the pass through query will only
apply a shared read lock and this should not prevent other users from searching or
updating the table at the same time. Is the correct?
2. If another user is currently updating the table. Will the select query with ' with nolock' have
to wait for the update or will it return all records as at the time of the request even if the
records are dirty.
3. What is the difference between using 'with nolock' and 'with readuncommitted' and which
one is better to use.