locked
One table blocks write RRS feed

  • Question

  • greetings,

    we have 30 users with a  Windows application front end (Access) to SQL Server Express.  Been in use a long time.  Approx 40 tables.

    have been making a variety of design changes to the front (Access) to adapt to business changes; nothing super complicated - just some if/then code in events so users will get fields entered correctly as part of their normal work plus at the same time we recently moved from Access .mdb to .accdb as the front file format was old - just background and history - probably not all that relevant.

    Within the last month, one table alone will go into block mode where it won't let anyone write to it.  All other tables are fine.  Plus you can look up data of that table fine when it is in that mode - so it isn't corrupt or locked.  But if you edit/enter - it results in an error message that essentially says it can't write and then the users app/front end locks up and that 1 user has to restart their app.

    When this happens - users can look at data and just refrain from writing.  Then we get everyone to close out of the app.  And it fixes itself.  

    Happens every 3 business days approx.     

    So my question is of course what state is a table in that it blocks all writing to it?  

    At this point I have no SSMS type stats - am just asking generically.  I think if I knew the concept of when a table would go into this state I might be able to work backwards and figure out what a user might be doing to bring it on.  For instance I wonder if a user has the front end form open to this table and then is inactive for an extremely long period of time or perhaps has begun an edit but didn't complete it so the cursor is still in a record.... something along these lines....

    We haven't yet had the patience to close out 1 by 1 to isolate the trouble maker - which maybe we will need to try and do.  It is one of the more active tables and so I will guess there can be ~5 users writing into this table at the same time.

    Sunday, October 8, 2017 3:07 PM

Answers

  • SQL Server may acquire a table-level shared lock due to ensure read consistency for the duration of a query. I'm not saying this is necessarily your specific problem but a possibility, knowing nothing about your application and the queries it runs.

    The self-deadlock would probably have the symptom of an application hang for a specific user, wich could block other user's write queries depending on the locks held.

    It's always a bad idea to leave a transaction open while waiting for user input. The best practice is an optimistic concurrency technique to avoid holding locks longer than absolutely necessary.

    I think your best bet will be to capture the information I suggested while the problem is occurring.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, October 8, 2017 9:09 PM

All replies

  • So my question is of course what state is a table in that it blocks all writing to it?  

    That would be a lock held by a session due to either a long-running transaction or query that is incompatible with the lock needed write queries. To identify the culprit while the problem is occurring, run sp_who2 to identify the head of the blocking chain and examine the query and locks held by that session.

    The are many different scenarios that can cause blocking like this, such as a table-level shared lock held by a SELECT query where the client that is slow to retrieve results or a client has deadlocked with itself. The solution could be query/index tuning or fixing the application bug.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Sunday, October 8, 2017 6:11 PM
    Sunday, October 8, 2017 3:24 PM
  • thanks for input DG.  

    table-level shared lock held by a SELECT query where the client that is slow to retrieve results

    .... don't know anything about a "table-level shared lock" - this would not have been explicitly designed -is that a managed option?; the user's form/screen' record source is a Select query for the 1 specific record they are working on.  It seems fast, and as per my original post - even when the write is blocked this is still working.  No one is mentioning a long hang time awaiting for the data to appear on screen.

    a client has deadlocked with itself

    ..... am guessing this would have other symptoms - not sure how I could tell this state otherwise....don't think is issue

    query/index tuning 

    **** hadn't considered looking at the index parameter - only has 10 fields and there are 2 being looked up as criteria in the Select query - User ID and Version value - - will check their index property

    application bug

    *** well I'm assuming the application is the cause - but why it works for days just fine - some sequence of events must occur that I'm unaware - or something is accumulating though I don't know what that would be....

    Of course there is a whole reporting side to this app - my focus has been on data input/edit as that is when the problem is seen, and this is where event code has been added.  But there are reports being run that would include this table.  No problem is seen with these however they are of course based upon Select queries.

    My first guess is an unfinished write - where the person started editing a record and then went off to a meeting or telephone call - - - but this should only affect that 1 record and not the whole table.   Blocking the entire table from a write seems pretty severe...so not sure how this is coming about.

    Will have to see if the IT manager can run sp_who2 .  As front end developer I'm not familiar with it but not sure he is either.  Not a lot of SQL Server skills in-house.

    Sunday, October 8, 2017 5:07 PM
  • SQL Server may acquire a table-level shared lock due to ensure read consistency for the duration of a query. I'm not saying this is necessarily your specific problem but a possibility, knowing nothing about your application and the queries it runs.

    The self-deadlock would probably have the symptom of an application hang for a specific user, wich could block other user's write queries depending on the locks held.

    It's always a bad idea to leave a transaction open while waiting for user input. The best practice is an optimistic concurrency technique to avoid holding locks longer than absolutely necessary.

    I think your best bet will be to capture the information I suggested while the problem is occurring.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, October 8, 2017 9:09 PM
  • thanks again DG - your advice put me on the right road.  Found what looks like a particularly useful site: http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting
    has example: Using sp_who2 to help identify blocking queries        Am going to follow on this as the issue re-surfaces.  Already have identified a potential culprit in some code existing in another form/screen that is writing to the form that is causing the problem.   
    Monday, October 9, 2017 1:46 PM