locked
MS Access passthrough select query causing page lock in SQL Server RRS feed

  • Question

  • I'm working with a split Access application where we're currently migrating the back-end to SQL Server, which we hope will help us to make the application more scalable in a multi-user scenario. In this context I 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 http://stackoverflow.com/questions/12026199/ms-access-holds-locks-on-table-rows-indefinitely, but use of NOLOCK seems to be generally discouraged?)
     - 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? 


    • Edited by Maxbromo Wednesday, February 22, 2017 7:49 AM NoLock hint does work for me after all
    Wednesday, February 22, 2017 4:13 AM

All replies

  • This issue “usually” occurs when you filling a combo box.

    You don’t mention if a combo box is involved in pulling data from that table (this 9 out of 10 times is the issue).

    If you use + add the (NOLOCK) hint in the sql select, then that PT query is NOT the source of the table lock. (I suspect you are thinking this is the PT query – it likely is not).

    So if you have a combo box, change it to a view (and include the (NOLOCK) hint. Now base the combo box on that view (or have the combo box SQL select on that view)

    So I am rather hard pressed to think that when you include the (NOLOCK) hint that the lock(s) still remain. This suggests that the source of the table lock is occurring elsewhere (ie: not the result of the PT query).

    I would test again with (NOLOCK) hint as this will avoid/fix this issue.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, February 22, 2017 6:23 AM
  • Hi Albert,

    thanks for the reply - you are right the (NOLOCK) hint does work, I messed up originally and hadn't implement the hint correctly in my SQL statement.

    There is no combo box involved, to isolate the issue I'm working with a very basic dummy access file which contains nothing except the passthrough query to the SQL Server backend and a continuous form with 1 textbox linked to this passthrough query.

    What is your opinion on the "dangers" of NOLOCK? E.g. as described here:
    https://blogs.msdn.microsoft.com/davidlean/2009/04/05/sql-server-nolock-hint-other-poor-ideas/

    In our actual production frontend we have several hundred data analysis forms and reports. Would you suggest adding NOLOCK hints to all the record sources, queries etc?

    I am still curious as to why the normal query without the NOLOCK is even causing the lock in the first place, i.e. why doesn't the dynaset work as advertised and only load small packets of data to minimize read locks on the server. Do you think this could be different in other versions of Access and/or SQL Server?

    Regards, Max

    Wednesday, February 22, 2017 7:47 AM
  • This is/can be a “pesky” issue.

    I actually been looking for an “easy” way to reproduce this locking issue! It actually is hard to re-produce! It seems you found it!! – that’s gives you a gold medal prize here! ;-)

    I wonder how is the “one” text box getting data from the PT query? I am curious since this hints that some “join” or expression pulling the one value from the query (PT or not).

    Is the PT run for each expression/row in that continues form? As far as I can tell this occurs when Access “halts” the data flow from SQL server in a way that SQL starts a query – but can’t finish.

    As for that link + warning + dangers of (NOLOCK)? Honestly, I would not worry. However, for general reports etc., I don’t recommend just some widespread adoption of (NOLOCK). You rare need it.

    However, I also wonder if the bound form + linked table has no PK? (and that PK is not necessary displayed or bound in that form – but “just” the fact of the table not having a PK when you linked the table may well be the issue here). The reason is SQL indexing system can’t grab the “row” then often the query engine is faced having to do a full table scan – and that can cause a table lock – one that sticks because not all data is pulled in one shot. So with a PK column, then indexing is used – no full table scan occurs when grabbing chunks.

    So I would as a “general” rule not shotgun the (NOLOCK) hint everywhere and every time. It rare comes into play. It is only when you have some form pulling data into a combo box (or your roundabout way of pulling data into one text box from a PT query that causes this issue).

    A simple index on that PT table (a PK key) may well cause this to go away. So this explains that table lock as opposed to say some row lock when grabbing a “bit” of the table in chunks (if SQL can't index grab a bit - it goes into full table scan mode - that causes locks).

    And no, the behaviour is not different in any version of SQL server or Access in this regards – it been around for a long time spawned over many past versions of Access and many past versions of SQL server (so it not version specific to my knowledge).

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Wednesday, February 22, 2017 8:12 AM
  • Hi Albert,

    haha well a gold medal for finding the issue is good, so does that mean you have been able to reproduce the problem? Anyway I would prefer a gold medal for solving the issue ;).

    I am not using any expressions or anything in the form or the textbox. The query SQL is simply "SELECT * FROM tbl_A". The record source of the form is set to the name of the query, and the control source of the textbox is set to one of the fields of the table.

    The table has one unique clustered PK index, plus several additional non-unique, non-clustered indexes.

    The table does have a couple of associated CLR triggers (after update, delete and insert). I will try to remove them and see if this is the source of the issue.

    Regards, Max


    Edit: no, dropping the triggers from this table does not change the locking behavior.
    • Edited by Maxbromo Wednesday, February 22, 2017 8:32 AM Add comment re dropping triggers
    Wednesday, February 22, 2017 8:23 AM
  • First, thanks for the trigger test - it was a long shot, but it shows good approach and testing on your part.

    >so does that mean you have been able to reproduce the problem?

    Well, a simple working form that always locks the table **is** hard to re-produce. So no, I don’t have a 100 row table and form that “always” creates such locks. So what I saying is yes, over the years I seen this issue “many” times, but the issue is still hard to re-produce on demand. It is often an “intermittent” problem and as we all know intermittent problems are very hard to always reproduce on demand.

    So this is just a plane Jane continues form? And the form’s data source is based on a simple PT query that you saved in the query builder?

    Keep in mind that a PT query is read only in Access. Perhaps the big detail (like huge and rather massive) here is your using ADO record sets from that PT query and THEN binding the form to that PT query via that ADO recorded you created in VBA?.

    So if this is just a plane Jane continues form based on a standard PT query, then you should not be seeing such locks.

    And assuming a standard PT query (a saved query as PT), then this means your form and data is read only. And thus any triggers (including your mentioned .net CLR code) should not be an issue here (and your follow up does verify this).

    I am curious if there are any other forms bound to that table? A simple PT query saved and then basing a form on that query should not cause such a lock unless “other” things are going on such as users editing data from that table. This is especially the case with such a small table.

    I thus think some “big” detail is being left out here.

    And if you are using ADO reocrdsets (that massive detail we missing here), you likely find it best to create a view (server side) and link to that view from Access. This means you don’t write any T-sql store proc code, and you don’t have to write any code Access/VBA side. The result is a form with read/write ability, and you not had to write any code client/server side (and you get the same performance as a PT query).

    As I noted, I don’t recommend (NOLOCK) for everything since this type of locking is RATHER HARD to re-produce. Since this is hard to re-produce, I am assuming there is some important detail being left out here.

    Anyway, the (NOLOCK) hint will fix this, but with such a rather small table, I still would like to “nail” down the detail that causes the lock – since with such a small table then I have a “easy” demo and something to play with that allows one to “easy” cause the locks, but more important easy test different cases.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, February 22, 2017 11:39 PM
  • Hi Albert,

    thanks again for taking the time to try to solve this!

    First of all, switching "READ_COMMITTED_SNAPSHOT" ON in the SQL Server DB has made the locks disappear, and I feel this is an acceptable solution for us (although I still have to study in detail which drawbacks this option has). Despite this I'm still interested in understanding what was causing the locks in the first place (with the default read_committed behavior in SQL Server) because as you say, it shouldn't really be happening.

    I have a few years' experience with Access but am just starting out with SQL Server and passthrough queries (as evidenced by messing up the NOLOCK hint). So there may be something obvious with the PT query I'm missing, but I don't know what. I'm not using any ADO recordsets, in fact there's no VBA code at all on this form as I'm really trying to create the most basic test scenario. I tried to attach screenshots but it won't let me as my account is not verified.

    To be honest this specific table has ca 8500 rows, but I have tested with multiple other tables with between 50 and 2 million rows. The 50 row table works fine with no locks. The next larger one has 196 records and already causes a lock. Even for the largest table, the lock immediately disappears as soon as I click the "last record" button (of course for 2 million records it takes a little while to load).

    Regards, Max

    PS: You said versions will probably not have anything to do with things, but just for completeness, I am running SQL Server 2008 R2 and Access 2007. I've tried with Access 2016 frontend, same result.

    Thursday, February 23, 2017 11:54 AM
  • Albert,

    I just experienced this issue, definitely in filling a combo box. Changed the source query to a passthrough and it went away.

    One thing, though. I could easily see the issue on the client's system, however, could not duplicate it in my dev environment, even across the network.
    I am using the same native client 10.0 driver as client's machines.

    I was wondering if there is some ODBC configuration somewhere that might have an effect on how Access queries the table.

    Any idea?

    Thursday, February 23, 2017 8:44 PM
  • Well done!

    I am close to suggesting that your read_commited setting is a SOLUTION for the general Access community.

    That out of the box setting is a "performance" optimized setting that good numbers of people change to reduce locking issues.  Given this fixes the issue and "many" recommend that setting be set to "ON", then this is a far better solution then using (NOLOCK) hints in the actual SQL query(s).

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thursday, February 23, 2017 9:20 PM
  • While the PT query “seems” to fix this, it is not a “general” fix. Likely the PT query runs a bit faster, or causes the SQL to pull the data in a slightly different way (likely has a better chance of pulling all rows). So force filling the form, or force filling the combo box does seem to fix this issue. In other words if you “fill” the object with all data in the request, then the query is done – and no locking issue.

    While PT quires are read only in Access, you see in fact that the original poster was using a PT query – yet they encountered this locking issue.

    So I can (and have) confirmed that “just” the act of changing over to a PT query is not a fix. If that table grows, or loads increase on the server – it still possible that locks will occur. I also seen this with macro or docmd.runSQL UPDATE statements - that seems to really kick this issue up a notch. I always use currentdb.Execute. 

    >however, could not duplicate it in my dev environment, even across the network.

    That’s the problem! As noted the problem centers around when Access “stops” the flow of data – but the query needed/used a lock during the query. This “mostly” occurs with a combo box, but as the poster shows they were using a continues form.

    What happens is SQL server can FLIP over to a table lock if “many” row locks are being required on the table. So internal thresholds exist as to when such table locking occurs (as opposed to row locks). Because of this “load” issue – it often hard to re-produce on test beds since you don’t have multiple users and thus that “flip” over to a table lock when “many” row locks exist occurs less often. So test vs production is EXACTLY the same issue I have – it hard to reproduce without several people working at the same time. And with only "one" user stopping the flow of data - we don't (much) see the lock issue - it tends to occur with multiple users.


    >I was wondering if there is some ODBC configuration somewhere that might have an effect on how Access queries the table.

    I don’t think client ODBC config can change this (so no to this).

    The poster does suggest and hint that setting/changing the default of READ_COMMITTED_SNAPSHOT to “ON” will fix this. This setting/feature I believe arrived in SQL 2005. Many people think that SQL should ship with this setting “ON”. (SQL 2005 forward ships with this setting “off” for increased performance).

    This read committed setting may well explain why some never see this issue, and others “often” see the issue.

    This read committed setting is an “out of the box” optimized performance setting (set to OFF).

    I am close to accepting that this setting likely is a “general” fix for this issue and the access community. And for many it is a recommend setting for SQL server anyway.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, February 23, 2017 9:26 PM
  • Thanks Albert.

    This system is using SQL Server Express, so is likely getting excessive table locks due to memory limitations.

    Thursday, February 23, 2017 9:42 PM
  • Just to confirm: setting READ_COMMITTED_SNAPSHOT on the server prevents any read operations from causing any locks, so you avoid the deadlocking issue which were my issue. The cost is increased tempDB usage in SQL Server.

    Nail on head here from Albert:
    "So force filling the form, or force filling the combo box does seem to fix this issue. In other words if you “fill” the object with all data in the request, then the query is done – and no locking issue."

    Initially I thought a dynaset (which by design keeps on loading small packets of data in the background rather than trying to get the whole load of data in one go) should only lock the server in those little instances when it is actually reading data. But thinking about data consistencies, it makes sense that a dynaset connection by default has to keep a lock on the table until it has finished loading all data. Otherwise if the data in the table changes between the loading of subsequent "packets" for the dynaset, the next packet can be inconsistent with the previous ones, rows can be missed or double-counted and so on. Basically similar to the well-known issues with NOLOCK and Read_uncommitted queries if the SQL Server data page is restructured while the read is running.

    So my conclusion is: the dynaset is probably running as intended, it's just badly documented in the msdn article I quote in my original post. I.e. dynasets do NOT minimize the read locks on the server, quite the opposite in fact! There seem to be only two ways around this:

    1) to force access to complete the read request by selecting only few rows or doing some kind of "move last".
    2) to avoid read locks altogether by using NOLOCK hints or the read_committed_snapshot option

    Cheers, Max

    Friday, February 24, 2017 2:42 AM
  • Running 64 bit Office 365 with Access on the front end and Sql Server 2017 standard edition on the back end. I encountered this locking bug with a passthrough query using DAO to a stored procedure in Sql Server. In my code, I am binding the recordset of the form to the results of this passthrough query with this:

    set me.form.recordset = RST

    where RST is a DAO recordset from a Sql server passthrough query. This is about as "read only" as you can get and still after working without ever causing any locking behavior, a small change of adding two columns in the source table suddenly caused MS Access to place a lock on the entire table when this read-only query was run.

    I did not test WITH (NOLOCK) or setting database wide READ_COMMITTED_SNAPSHOT, but I did change the form's recordset type from dynaset to static and that appears to have solved the problem. So, a solution that might work for folks that doesn't involve any database-wide changes could be to change to a snapshot recordset.  That said, I also set the database to Read_Committed_Snapshot true for a belt and suspenders approach. I don't see any performance difference on casual testing.

    Tuesday, July 21, 2020 2:35 AM