Solved

Access Passthrough query locking hints

Posted on 2011-09-29
1
480 Views
Last Modified: 2012-05-12
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.

Thanks
0
Comment
Question by:RDLFC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 36817388
1. Correct to my understanding.

2. No or at least thatS what BOL says.

3. They are equivalent although NOLOCK will be deprecated in a future release of SQL Server and you should use READUNCOMMITTED
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question