• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • Last Modified:

Record vs Table locking in SQL Server

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
VoodooFrog
Asked:
VoodooFrog
  • 4
  • 3
4 Solutions
 
lahousdenCommented:
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
 
VoodooFrogAuthor Commented:
So would the Jet engine lock many records in the SQL table, or possibly the whole table?
0
 
lahousdenCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
VoodooFrogAuthor Commented:
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
 
lahousdenCommented:
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
 
VoodooFrogAuthor Commented:
where do we get at the with (rowlock)" hint?  I am not sure I follow what you are saying in this.  
0
 
lahousdenCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now