10/19/2023 0 Comments Linked table msaccess sql deadlock![]() 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). 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! -) Do you think this couldīe different in other versions of Access and/or SQL Server? why doesn't the dynaset work as advertised and only load small packets of data to minimize read locks on the server. ![]() I am still curious as to why the normal query without the NOLOCK is even causing the lock in the first place, i.e. Would you suggest adding NOLOCK hints to all the record sources, queries etc? In our actual production frontend we have several hundred data analysis forms and reports. What is your opinion on the "dangers" of NOLOCK? E.g. 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. 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. What is the best way to get around this issue? 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)Īll 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. pulling the data from the server into temp tables on the client side and basing the forms on those temp tables using a disconnected ADO recordset as recordsources for my forms using NOLOCK hint (as suggested here, but use of NOLOCK seems to be generally discouraged?) I have seen several possible workarounds suggested, e.g. This allows other users to modify data without having to wait as long as is necessary for locks to clear."Ĭan anyone explain why the dynaset is not acting as expected? "Because they work with only a few rows at a time, dynasets minimize the duration that read locks are held on the server. 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. Based on the description of dynasets as e.g. As soon as I click the "last record" button in Access to scroll to the end of the query or theįorm, the lock is cleared and my other processes can update the table again.Īs far as I can see my form and/or query properties are set to their defaults (recordset type = dynaset, record locking = no locks). ![]() Looking at the view sys.dm_tran_locks in SQL Server shows it is an IS lock on a data page. Same thing happens if I open a continuous form which has this passthrough If I try to run an update query or something like that on the table it leads to a deadlock. 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īound), then I get a lock on that table in SQL Server for as long as I keep the query open. I have a passthrough query of the simple form "SELECT * FROM tbl_A". ![]() Locking behavior caused by even simple passthrough select queries. In this context I have encountered what I think is strange 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |