7

We're using MS Access as the GUI for one of our systems, but we've run into an issue where Access is holding locks on the underlying tables or rows, which prevents SQL server from running any update queries on this data. This is problematic because while our Access frontend only requires read only access to this data, we have systems in place that are refreshing the data at regular intervals. These refresh operations fail (or are delayed indefinitely) due to Access already holding locks on the data.

This problem is illustrated by opening the Access frontend and using the sys.dm_tran_locks DMV to show locks on the data. The steps I take to reproduce the problem are:

  1. Open the Access frontend. This shows a scrollable form with several thousand records
  2. Use SQL server DMVs to show locks on the data. This shows 5 "object" type locks with request mode of "IS" (Intent shared). Using sys.dm_exec_requests shows the command status as "suspended" and the wait type as "ASYNC_NETWORK_IO". These locks are held as long as the user has the Access frontend open, and prevent any update/delete/truncate operations on the tables involved. Now if the user scrolls to the end of the record set in Access, the locks are released!

The second issue occurs when the user clicks through to show a single record in the frontend. When a single record is displayed onscreen, the SQL server DMVs show these locks: 3x object, 1x key, 1x page. The key is a shared lock, others are intent shared. Again, command status is suspended and wait type is ASYNC_NETWORK_IO. And these locks are held as long as the user is viewing the record

We need to stop access from holding these locks on an indefinite basis. Unfortunately MS Access is not part of my skill set so I don't know what needs to be done to fix this.

Trent
  • 1,089
  • 3
  • 12
  • 24
  • 2
    "Open the Access frontend. This shows a scrollable form with several thousand records" I think this will cause a problem no matter what front end you use. You could consider a disconnected recordset if you really must have several thousand records in a continuous form or datasheet. – Fionnuala Aug 19 '12 at 18:18

2 Answers2

5

I didn't solve this problem, but a colleague did. What was done is that instead of creating linked tables to SQL Server tables he created linked tables to views. The views looked like this:

CREATE VIEW dbo.acc_tblMyTable
AS
  SELECT * FROM tblMyTable WITH (NOLOCK)

No locking, and as a bonus Access treated the data as read-only.

Make sure you understand what can happen when you use NOLOCK, however.

Unfortunately MS Access is not part of my skill set so I don't know what needs to be done to fix this.

Get rid of Access :)

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
  • NOLOCK is not viable in this situation due to the drawbacks outlined in that article. Is pulling the data from the view (or maybe a stored procedure) enough to prevent Access from acquiring persistent locks on it? I don't want to suppress all locks - just need Access to exhibit the sensible behaviour of releasing the locks when it's finished reading the records. – Trent Aug 19 '12 at 14:14
  • 1
    What do you suggest instead of Access? If the problem is not sorted, it is just as likely to occur with any other front-end. You might just as well suggest get rid of SQL Server :) – Fionnuala Aug 19 '12 at 18:10
  • @Trent I'm don't know and I'm out of ideas, sorry. – ta.speot.is Aug 20 '12 at 04:18
  • FWIW, this doesn't work for me and [others](http://stackoverflow.com/questions/36315902/access-linked-tables-continuous-locking-when-viewing) at all. Backend: SQL Server 2008 R2 Express, Frontend: Access 2010. Created the view with `WITH (NOLOCK)`, linked it from Access. The linked view is not read-only, and when opened in Access, it creates suspended "ASYNC_NETWORK_IO" processes just the same as when opening the table. – Andre Mar 31 '16 at 09:48
  • @ta-speot-is: I'm facing the same issue and read the suggested post on NOLOCK. I was wondering, 5 years after having used this solution, have you had any problems? Inaccurate access reports or transient sql error log corruption or other problems? – Jim B Oct 27 '17 at 16:33
  • 1
    @JimB It’s been fine for us and I’m surprised by reports from others it doesn’t work. We still use it but we’re also outgrowing Access so we are converting these queries to ODBC Passthrough that all begin with `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED`. As for inaccurate reports you’re kind of asking for it with `WITH (NOLOCK)` but it comes down to just how much concurrency you have in your system. – ta.speot.is Oct 28 '17 at 01:08
-2

Been developing applications that use SQL Server as the backend for years mostly .NET. Never ran into the locking (blocking) issues you are discussing. And a properly designed database should be using SQL Servers' default row level locking on update.

It is Access that is the issue. Since once upon a time it had an internal database that it had full control of it continues to think that is what is has and the behavior is what it thinks is correct. Effectively it has end run the SQL Server to do what it thinks is correct. Not really a good thing since Access is a file based product and a less than production ready one at that. Good for phone books or recipes and that is about all. Doesn't scale either.

Joshua Stein
  • 9