none
Locking Issue - t-sql in Access 2010 RRS feed

  • Question

  • Fellow Accessors, I am running into a 3624 error, very randomly.

    1 - Access 2010

    2- GUI Access DB with no table and NO LINKED TABLES. It only connects to backend when it needs to and  then closes connection.

    3 - Backend Access Db with all of the tables and queries.

    4 - Client DB setting is Record Locking selected, Shared on opening, Record Lock is No LOCK.

    5 - All VBA code is using DAO.

    I can see where it is occuring and it is 'RANDOMLY" occuring on a Insert. Here is the code:

    set mydb = Opendatabase(networksharelocation,False)

    mysql = "Insert into StatusHistory (ID, status_code) Select TOP 1 ID, 6 from Statushistory where ......."

    mydb.Execute myssql

    Someone obviously has something locked. I am a SQL DBA but do know the inner workings of Access. So, my question to you is even though the settings are Record Locking, can another t-SQL query do a page lock in Access even though I have Access settings as they are? I was expecting for single row logic with where clauses, it should just grab the row and not the page.

    Notice that I am doing an Insert - not an update nor delete. But I am reading the same table I am going to insert to and I just cannot believe that this should be causing a problem.  Another query would have to be causing this.

    Thanks,

    MG




    • Edited by mg30 Friday, September 23, 2016 4:29 PM
    Friday, September 23, 2016 4:24 PM

Answers

  • Well, I should point out that a page lock and a record lock are in THIS context the same thing!

    The access data engine can ONLY lock a page during an update. When you turn on row locking, what the ACE engine does is FAKE the record locking by simply PADDING the size of the record to = a full page!

    Since access is now forcing the record to be the same size as a page, then a page lock thus only contains one record and then “presto” we now have row locking without really having row locking.

    I should point out that the result of the above feature can often result in MASSIVE bloating and file increasing for given data updates.

    For example, I have a sample database of xx Megs in size. I run this SIMPLE code:

       Dim rst        As DAO.Recordset

       Dim i          As Long

       Dim t          As Single

      

       Set rst = CurrentDb.OpenRecordset("contacts")

      

       t = Timer

       Me.Text1 = "working..."

       DoEvents

       Do While rst.EOF = False

          rst.Edit

          i = i + 1

          rst!City = "ci " & i

          rst.Update

          rst.MoveNext           

       Loop

       t = Timer - t

       debug.print "done in " & t & " seconds"

       Beep

    AFTER running above code, the database is really MUCH the same size. (6.7 megs). If I turn on row locking, then the above grows by a WHOPPING 120 megs in size! So file goes from 6.7 megs to 126 megs!

    So row locking really does not exist – if you turn the feature on then access just pads all records to the size of one page and continues using page locking.

    But to answer your question? No difference between using a sql update or a recordset update does not change the page locking used. (and we now know that row locking and page locking are the SAME thing in the given context of this discussion).

    edit: the real lesson here is that turning on row locking can result in MASSIVE file bloating and increasing of size for simple update code. The reason of course is the row locking setting forces Access to EXPAND and pad the record to = a page size.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada


    • Edited by Albert D. Kallal Tuesday, September 27, 2016 7:58 PM
    • Marked as answer by mg30 Wednesday, September 28, 2016 1:38 AM
    Tuesday, September 27, 2016 7:56 PM

All replies

  • AFAIK, Access doesn't have real row-level locking since it's a shared file-based database.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, September 23, 2016 4:30 PM
  • Ok. thanks David

    MG

    Friday, September 23, 2016 5:03 PM
  • I don't know if it will make any difference, but you can try setting the locking in the BE.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, September 23, 2016 6:40 PM
  • HI Bill,

    IF BE means backend, I did. The locking is identical on both.

    Thx

    MG

    Friday, September 23, 2016 8:33 PM
  • Yes BE = back End.

    The locking could be due to the fact that you are running a SELECT on the same table you are inserting into. Is it necessary to do a SELECT TOP? Maybe you can get the ID and put it into a variable, close the recordset and then run your insert.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Monday, September 26, 2016 6:23 PM
  • Hi Bill,

    Thanks. We thought of that too but the locking is happening in other locations which are not doing Select like my example but direct inserts with variables. This was just one of the examples. The real problem we believe lies in the network latency tied to other people inserting. I am used to SQL where this type of things does not have the same issues and can be handle via row locking not page locking as well as isolation options.

    We are going to put retry logic in the error handler that only retries for certain errors.

    MG

    Monday, September 26, 2016 7:56 PM
  • It sounds like you are going in the right direction. It just might be network issues.

    And by the way, Access does do row locking.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 26, 2016 8:18 PM
  • HI Bill,

    Many thanks. One more question though. Others have told me that that it does NOT do row locking.  Below screen shot I found on another forum about this that indicates when it will escalate to page locks.

    Access 2010 Help:

    Open databases by using record-level locking
     
    Makes record-level locking the default for the currently open database.
    Clearing this check box makes page-level locking the default for the
    open database. The choice you make applies to data in forms, datasheets, and code that uses a recordset object to loop through records. This option does not apply to action queries or code that performs bulk operations by using SQL statements.

    this makes me believe ANYTIME I uses t-sql with db.Execute "....."  It will give me a page lock.

    So, If I convert my t-sql EXECUTE to recordset logic (rs.update)  would this go to record locking?

    Thanks, MG

    Tuesday, September 27, 2016 11:42 AM
  • >So, If I convert my t-sql EXECUTE to recordset logic (rs.update)  would this go to record locking?

    Perhaps.  But it might be no harder also convert your back-end to SQL Server, and in VBA continue to use DAO but with ODBC, or switch to ADO and OleDB.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 27, 2016 11:52 AM
  • HI David,

    Totally agree. I am a SQL DBA and already have been pushing this towards a SQL solution. Unfortuantly that will be down the road.  I in the past couple of decacdes, I also have built a couple of solutions with Access front end communicateing to SQL with oledb. Works well.

    So, you think changing the t-sql to recordset updates "might or might not" fix this?

    MG

    Tuesday, September 27, 2016 11:55 AM
  • >you think changing the t-sql to recordset updates "might or might not" fix this?

    Yes and will require code changes on the same order of magnitude as migrating to SQL Server which _will_ fix it.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 27, 2016 2:23 PM
  • Agreed
    Tuesday, September 27, 2016 2:55 PM
  • HI Bill,

    Many thanks. One more question though. Others have told me that that it does NOT do row locking.  Below screen shot I found on another forum about this that indicates when it will escalate to page locks.

    Access 2010 Help:

    Open databases by using record-level locking
     
    Makes record-level locking the default for the currently open database.
    Clearing this check box makes page-level locking the default for the
    open database. The choice you make applies to data in forms, datasheets, and code that uses a recordset object to loop through records. This option does not apply to action queries or code that performs bulk operations by using SQL statements.

    this makes me believe ANYTIME I uses t-sql with db.Execute "....."  It will give me a page lock.

    So, If I convert my t-sql EXECUTE to recordset logic (rs.update)  would this go to record locking?

    Thanks, MG

    I don't know if a recordset would lock the entire table, but I've never seen a recordset insert fail. Again, I recommend getting the values into variables and then running an insert. I think the SELECT is where your problem is.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, September 27, 2016 4:18 PM
  • thx Bill

    MG

    Tuesday, September 27, 2016 6:32 PM
  • Well, I should point out that a page lock and a record lock are in THIS context the same thing!

    The access data engine can ONLY lock a page during an update. When you turn on row locking, what the ACE engine does is FAKE the record locking by simply PADDING the size of the record to = a full page!

    Since access is now forcing the record to be the same size as a page, then a page lock thus only contains one record and then “presto” we now have row locking without really having row locking.

    I should point out that the result of the above feature can often result in MASSIVE bloating and file increasing for given data updates.

    For example, I have a sample database of xx Megs in size. I run this SIMPLE code:

       Dim rst        As DAO.Recordset

       Dim i          As Long

       Dim t          As Single

      

       Set rst = CurrentDb.OpenRecordset("contacts")

      

       t = Timer

       Me.Text1 = "working..."

       DoEvents

       Do While rst.EOF = False

          rst.Edit

          i = i + 1

          rst!City = "ci " & i

          rst.Update

          rst.MoveNext           

       Loop

       t = Timer - t

       debug.print "done in " & t & " seconds"

       Beep

    AFTER running above code, the database is really MUCH the same size. (6.7 megs). If I turn on row locking, then the above grows by a WHOPPING 120 megs in size! So file goes from 6.7 megs to 126 megs!

    So row locking really does not exist – if you turn the feature on then access just pads all records to the size of one page and continues using page locking.

    But to answer your question? No difference between using a sql update or a recordset update does not change the page locking used. (and we now know that row locking and page locking are the SAME thing in the given context of this discussion).

    edit: the real lesson here is that turning on row locking can result in MASSIVE file bloating and increasing of size for simple update code. The reason of course is the row locking setting forces Access to EXPAND and pad the record to = a page size.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada


    • Edited by Albert D. Kallal Tuesday, September 27, 2016 7:58 PM
    • Marked as answer by mg30 Wednesday, September 28, 2016 1:38 AM
    Tuesday, September 27, 2016 7:56 PM
  • HI Albert,

    Many thanks. Well, now it comes to inserts. Since the record does not exist yet, I would interpret that the Access engine would have to lock a page with data in order to make the insert?

    Thks MG.

    Tuesday, September 27, 2016 9:23 PM
  • It depends on if the page is new or not for that incoming new record.

    Not a bad question!

    The answer is interesting.

    Page locking is NOT used when adding these new records. And the reason is that since the new record does not YET exist in the database, then no need to lock the NEW page that is currently being filled up when adding those new records – the page not written out until its full of records but that don’t matter because the pages not yet saved so who cares since then the records don’t exist at this point in time (until the page is actually written to disk).

    What this means is that adding (or appending ) new records into a NEW page means these new records are not EXPANDED to a page size and thus that huge bloat I talked about does not occur.

    So for a record being edited, the expand occurs to one page size. And for starting inserts into an existing page, then again the record padding occurs. However, the instant we are adding new un-used pages, then records are not expanded in that new page since they don’t yet exist and thus don’t need to be locked and thus no need to be expanded.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Tuesday, September 27, 2016 9:32 PM
  • Many Thanks Albert.

    MG

    Wednesday, September 28, 2016 1:38 AM