2

I'm working with an Access 2007 frontend linked to an SQL Server 2008 R2 backend and have encountered what I think is strange locking behavior caused by even simple passthrough select queries. More concretely:

I have a passthrough query of the simple form "SELECT * FROM tbl_A". If I open this query in access (normal datasheet query view) and the table has sufficiently many records ( roughly > 100 records, I haven't experimented to find the precise bound), then I get a lock on that table in SQL Server for as long as I keep the query open. If I try to run an update query or something like that on the table it leads to a deadlock. Same thing happens if I open a continuous form which has this passthrough query set as the record source. Looking at the view sys.dm_tran_locks in SQL Server shows it is an IS lock on a data page... don't know if that helps. As soon as I click the "last record" button in Access to scroll to the end of the query or the form, the lock is cleared and my other processes can update the table again.

As far as I can see my form and/or query properties are set to their defaults (recordset type = dynaset, record locking = no locks). Based on the description of dynasets as e.g. given here: https://msdn.microsoft.com/en-us/library/bb188204.aspx I would expect it to load only small batches of data as required to fill the viewable area, without locking the server for any extended period of time. As the linked article says:

Because they work with only a few rows at a time, dynasets minimize the duration that read locks are held on the server. This allows other users to modify data without having to wait as long as is necessary for locks to clear.

Can anyone explain why the dynaset is not acting as expected?

I have seen several possible workarounds suggested, e.g.

  • using NOLOCK hint (as suggested here MS Access holds locks on table rows indefinitely, but it seems NOLOCK is discouraged in general?)
  • using a disconnected ADO recordset as recordsources for my forms
  • pulling the data from the server into temp tables on the client side and basing the forms on those temp tables
  • redesign the frontend, add restrictors etc so that forms never pull more than, say 100 records (would be a LOT of work and seems unsafe without having a guarantee that it will never lock with the specified number of records)

All of these suggestions either don't work for me or seem to have many other disadvantages and/or require a major rewrite of the frontend... What is the best way to get around this issue?

Community
  • 1
  • 1
Maxbromo
  • 61
  • 6
  • Does your underlying table in SQL Server have a unique clustered index? Not having one can introduce certain kinds of problems, though not necessarily this one. Do you have the option of using an Access Data Project instead? Then you can use OLEDB instead of ODBC... – mroach Feb 22 '17 at 04:31
  • Have you tried changing your linked table to snapshot mode? It'd be interesting to see if that changes the behaviour. – mroach Feb 22 '17 at 04:42
  • Thanks for the comments @mroach. Yes the table does have a unique clustered index. My issue appears even using a pure passthrough query (no linked table), I'm not sure what you mean by "linked table to snapshot mode". I have tried changing the form "recordset type" to snapshot but that doesn't change anything. – Maxbromo Feb 22 '17 at 05:04
  • Sorry you did say you were using a passthrough query and not a linked table. A linked table is a different thing. – mroach Feb 22 '17 at 05:07
  • to be honest I've never really explored access data projects as an option. We have a large existing application written in normal .accdb access so I would be wary of potentially upsetting the apple cart by migrating to adp unless it's absolutely necessary. Plus adp's are not supported any more in newest access versions? – Maxbromo Feb 22 '17 at 05:08
  • ADP's are gone for 2016 - not a choice. I remain unconvinced that using the (NOLOCK) hint in the PT query does not fix this. I would guess that some combo box on that form exists that is based on the same table. If there is no combo box here, then the (NOLOCK) hint should remove the lock. – Albert D. Kallal Feb 22 '17 at 06:57
  • Thanks Albert you are absolutely correct, I put in the (NOLOCK) hint wrong originally and after fixing it it does prevent the locking. I've edited the question accordingly. What about the generally perceived issues with NOLOCK? Should I really use explicit NOLOCK hints in all queries used in my data analysis forms & reports? – Maxbromo Feb 22 '17 at 07:57

1 Answers1

4

After digging around and experimenting further, I see it like this:

With default settings on the SQL Server, the dynaset from Access does maintain the read lock until it has completely loaded all the data. This is necessary to avoid inconsistent reads, otherwise other transactions could come in and change the order of the data in between subsequent "packets" of the dynaset being pulled, which could lead to duplicate rows being pulled or rows being missed. (similar to what can happen with a NOLOCK query if the SQL Server page is being rebuilt while the query is running).

So there seem only two ways to avoid it:

1) force Access to "complete" the query, by using a snapshot instead of a dynaset or by doing some kind of "move last" operation on the dynaset, or by making sure the select statement returns only so few records that Access will load them all immediately.

2) prevent read locks, either using NOLOCK hints or by setting READ_COMMITTED_SNAPSHOT ON in the SQL Server database.

We are going to go with the READ_COMMITTED_SNAPSHOT ON option as it means nothing has to be changed in the frontend, and anyway I generally prefer not to block write operations when doing a read operation.

Maxbromo
  • 61
  • 6