none
OLEDB and MS Access (2007) and Could not update; Currently locked RRS feed

  • Question

  • First, sorry for the length of this post, but I'm trying to give as much information as possible.  If I am missing information you feel is key, please let me know and i can provide it too.  I am using vb.net (VS2008) and accessing an Access database (2007/.accdb) using OLEDB.   I am running into this "Could not update; currently locked" error which I have searched for and read about in many locations.   In particular, this one Record level locking using OLEDB describes what I am encountering.   It is a problem with Access, but not with SQL Server (2012 Express) for me as well.  However, unlike the post, I am not using a transaction.   And the link to the Microsoft kb article (http://support.microsoft.com/kb/331594/EN-US) is broken and I can't seem to come up with the original kb article in other searches.   But the article title also suggests a transaction is involved, which in my case it is not.

    I have a particular account record from the Customer table (let's say account '01') in use by another function of the program and I am using a Try-catch block of the current function to catch when the contention is encountered.   This is working perfectly.  However, when I move on to the next account ('02'), which is not in use, I also run into the same Could not Update, Currently locked error.   I know this is related to OLEDB because I can stop the code at this "fake" error and go into MS Access directly and edit the second account ('02') without a problem, and I cannot edit account '01' from here.

    I originally had this coded with ADO and an ADODB connection with a client-side cursorlocation.   My connection string is for cn.Provider = "Microsoft.ACE.OLEDB.12.0" and I have specified 

    cn.Properties("Jet OLEDB:Database Locking Mode").Value = 1 '1=row level, 0=page level. 

    In the below code, the Opendbconnection is just a sub that formulates the connection string and opens the connection.

    This is a simplified version of the code as things are printed using rstCustomer but when ready to update, the code used the execute method of the connection object:

    Dim cn as new ADODB.connection
    Dim rstCustomer as new ADODB.recordset
    
    cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    Opendbconnection(cn)
    
    mySQL = "Select acctnum, field1 from Customer"
    rstCustomer.Open(mySQL, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
    
    Do while not rstCustomer.EOF
      mySQL = "Update mytable set field1='xyz' where acctnum= '" & rstCustomer.fields("acctnum").value & "'"
    
      Try
        cn.Execute(mySQL, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)
      Catch
        [handle the contention]
      End Try
    
      rstCustomer.movenext
    Loop
    

    After piecing together the suggestions in kb/331594 from other posts (since I couldn't find the article), I am not interested in the ODBC workaround.   But I tried the suggestion to use a recordset object instead.   So instead of the cn.execute, I updated the recordset directly:

    do while not rstcustomer.eof
      rstCustomer.fields("field1").value = 'xyz'
      rstCustomer.Update
      rstCustomer.movenext
    Loop

    Same problem where account '01' throws the locked error correctly, and account '02' throws the locked error incorrectly.  Incidentally, account '03' does not throw the ghost 'locked' error.   Also incidentally I tried both of these options with a transaction around either the cn.execute or the rstcustomer.Update statements, with no change in results.

    So by now I decided I just had to bite the bullet and convert to ADO.NET, figuring that would solve all my problems...NOT!

    Now I have this inside the rstCustomer loop, hoping (and praying) disposal of the connection might help free the lock.

    Using cn As New OleDb.OleDbConnection
      OpenDBConnection(cn)
                                    
      Using cmdupdate As New OleDb.OleDbCommand
        mySQL = "Update Customer Set field1 = 'xyz' Where Acctnum = '" & rstCustomer.fields("acctnum").value & "'"
                                  
        cmdupdate.Connection = cn
        cmdupdate.CommandText = mySQL
    
        Try                                         
          cmdupdate.ExecuteNonQuery()
        Catch  
          [Handle contention]
        End Try
      End Using
    End Using
    

    SAME RESULTS!   when on account '01', the contention is caught and handled.   Account '02' STILL throws the Currently locked error though I can still go to Access and edit no problem.   Account '03' processes fine.

    The posts on kb/331594 are old.   Do we have no fix for this bug since then?   I don't see any recent posts on this so maybe I am missing something else.   what am I missing?   I feel like I have done my homework, but am just stuck.   Any help out there?  Just let me know if I need to supply more information.

    Thank you!!


    Saturday, July 28, 2018 8:56 PM

All replies

  • Some suggestions

    1. Does the lock (.laccdb) file get deleted after working with data? If not then delete it after closing your app.
    2. Have you tried repair/compact?
    3. I would suggest if possible to create a new MS-Access database, import the data from the current database into the new database as doing this as you might guess, locks don't persist in such an operation.

    Also, how are locks setup, perhaps try no locks.

    Eventually via one of the above should resolve the issue. Once done stick with OleDb data provider.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, July 28, 2018 9:40 PM
    Moderator
  • Hi Karen - thanks for the reply.

    Yes, the .laccdb file gets deleted after I finish the function in the application, even without exiting the application.   I have done a compact and repair.   The original record lock is held from another function of the program and is accomplished with a server-side cursor with pessimistic locking on that 1 record only.   I don't usually open Access directly and can repeat the symptoms without opening Access at all (ie the 2 program functions are the ones contending for the same record).  I have to be able to handle this because of it being a multi-user environment.   However, I opened Access in my testing to see if I could edit neighboring records when the first function locked an account (account '01' in the original post), thereby proving that the first function that was locking the record had only a record lock and not a page lock.   But the second function that is trying to do the update acts like it is responding to a page lock, though I can tell from going into Access and directly editing a neighboring account that there is no page lock.  

    I wouldn't think the Access settings matter if I am not using Access directly, but here they are:

    I can close the application and reopen and reproduce this problem on ANY group of neighboring 2 or 3 (just saw the issue with a 3rd account) accounts in a database with 500 records.

    Thanks

    Saturday, July 28, 2018 10:52 PM
  • Is it possible that there is some kind of lock escalation going on?  My cmdUpdate command is trying to run an Update and running into the locked record.   The Catch block is actually just letting that record be skipped and moving on to the next.   Could there be something about that attempt at an Update that is causing a page lock?
    Saturday, July 28, 2018 11:15 PM
  • Is it possible that there is some kind of lock escalation going on?  My cmdUpdate command is trying to run an Update and running into the locked record.   The Catch block is actually just letting that record be skipped and moving on to the next.   Could there be something about that attempt at an Update that is causing a page lock?

    Unfortunately for you what needs to happen is to reevaluate your program logic as there is no way around the current issue. No matter the database be it MS-Access, Oracle, SQL-Server etc you are going to run into the same problem. Locks placed on records should be momentary, less then a half second at best.

    With that said I have nothing more to offer.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, July 28, 2018 11:26 PM
    Moderator
  • As mentioned, I do not run into the same problem with SQL Server.
    Saturday, July 28, 2018 11:34 PM
  • That is completely understandable as both using different methods and logic for locks. I can tell you I've hit on rare occasions locks in SQL-Server that I had to force connections closed in SSMS but never in code. 

    Have you (if possible) considered using SQL-Server localdb instead of MS-Access?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, July 28, 2018 11:40 PM
    Moderator
  • My understanding is that localdb is not designed for multi-user scenarios.
    Saturday, July 28, 2018 11:53 PM
  • My understanding is that localdb is not designed for multi-user scenarios.
    Yep but when used in a multi user environment (we do this in my job) each user has their own db that gets sync'd with a central database at specific intervals. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, July 29, 2018 12:35 AM
    Moderator
  • Did you try adding a DoEvents statement within your loop? It sounds to me like the locks are not getting sufficient time to release.

    I'm not entirely sure what you are trying to do here with this code. Are you trying to simulate concurrency issues from a single app? I wouldn't recommend doing this. You should be using real world scenarios instead.


    Paul ~~~~ Microsoft MVP (Visual Basic 1994-2018)

    Monday, July 30, 2018 7:02 PM
  • Thanks for the reply.  The lock is being done with pessimistic locking so that if a user is working on an account, nobody else can update that account.  We do not want to ask the user to go back and redo their work as would be needed with optimistic locking.   So the lock is held on the account until the user releases it.   This is an old application and I realize with some work it can be redesigned to make optimistic locking a better option.   But for now, the lock is a valid lock as designed.  When a second user tries to edit the account, they get a notice that they can only view it.  This works perfectly.   The problem is that with row-level locking in effect, an attempt to update a neighboring record comes up with the record locked, when in fact it is not.  I have confirmed this by editing that neighboring record directly in Access.   But through code (ADO or ADO.NET, with or without a transaction), neighboring records are appearing locked.   This is only a problem with the Access provider, and not with SQL Server.
    Monday, July 30, 2018 7:39 PM
  • A few things to keep in mind with respect to Access and lock mode:

    1) The first connection to the database will determine the lock mode, so if you want row level locking then that is what you would want your .NET app to specify. The initial lock mode will persist for the database as long as the corresponding .ldb or .laccdb exists.

    2) Updates using SQL statements (as opposed to Recordsets) implement page-level locking.

    3) Row-level locking is less efficient and will cause database bloat.


    Paul ~~~~ Microsoft MVP (Visual Basic 1994-2018)

    Tuesday, July 31, 2018 3:23 AM