Solved

Record vs Table locking in SQL Server

Posted on 2007-12-06
7
709 Views
Last Modified: 2013-11-28
We are using Access 2007 as a front end and MS SQL Server as a back end. We experienced a locking error. How can we be sure that bound forms in Access are not locking more than the record being worked with?
0
Comment
Question by:VoodooFrog
[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
  • 4
  • 3
7 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 500 total points
ID: 20422132
You can't be sure of that - in fact with Access you can be pretty sure that the reverse is true.  If you are running into concurrency problems (and especially if you have more than one or two users wanting to access the data concurrently) then it is time to move away from access to an enterprise-ready front-end technology, such as ASP.NET, C# or even (dare I say it?) VB.NET.
0
 

Author Comment

by:VoodooFrog
ID: 20427624
So would the Jet engine lock many records in the SQL table, or possibly the whole table?
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 500 total points
ID: 20430963
I am not sufficiently knowledgable in Jet and Access to know whether it is the Jet engine itself or MS-Access' use of it that is responsible, but our experience with Access front-ends is that they cause too many locks to be held for too long - whether this is because of page or table locks (or simply too many rowlocks) I cannot say...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:VoodooFrog
ID: 20438233
How would we go about controlling the locking directly in SQL -- even if we were not using Access?  is there a way to direct SQL server to lock only single records (or page) vs the whole table when accessing it?  
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 500 total points
ID: 20439124
There are a couple of locking hints you can use if you are running into concurrency problems.  One is "with (rowlock)" and the other is "with (nolock)".  Look into the use of the "with (nolock)" hint for tables that hardly ever change, such as dimension or static lookup tables, particularly when querying them for drop-down lists or reporting.  You should be careful, however, using the hint on transactional tables that come under any kind of steady modification load, since the hint can result in "dirty reads".  The "with (rowlock)" hint, on the other hand, is totally safe and may improve concurrency in situations where you know you are dealing with small numbers (or small percentages) of rows from a table.  SQL will always obey a legal use of "with (nolock)" but I think it sometimes ignores "with (rowlock)" and goes for page or table locks if it believes it truly knows better.
We have been migrating our applications away from MS-Access front-ends for the last 6 or 7 years - to VB at the outset and more recently to VB.NET and ASP.NET - so it is a long time since I have really encountered the behaviours of MS-Access and their implications on concurrency first-hand.  But as I recall, MS-Access would do things like hold locks on the current table while it was waiting for user input! - and not always only on the particular row that was being displayed!  Needless to say this is a disastrous strategy for systems where any reasonable level of concurrent access is needed.  
0
 

Author Comment

by:VoodooFrog
ID: 20441445
where do we get at the with (rowlock)" hint?  I am not sure I follow what you are saying in this.  
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 500 total points
ID: 20442365
You include the hints in your SQL statements. E.g., for "with (rowlock)":

Select c.*
from Clients as c with (rowlock)
where c.ClientID = 2034

And for the "with (nolock)" hint:

select i.*, t.TypeName
from invoices as i with (rowlock)
join invoice_type as t with (nolock) on t.InvTypeID = i.InvTypeID
where i.ClientID = 2034

here we are expecting the Invoice_Type table to remain pretty much unchanged all the time, so we can dismiss locking against it for this query.

Note the hint comes after the table alias (if present) and before the join condition (if present).
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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