Microsoft Access and Record Level Locking [modified]
-
I am working with Microsoft Access MDBs (2003 Format) on a project and have a situation where two threads can be inserting records in the same table at the same time. I am using OleDB to open the Access MDB, create commands and transactions, and insert the records. The connection string to open the MDB is essentially the following:
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Data Source = SOMENAME.MDB"Even though I am trying to use record locking with this connection string, and the MDB is certainly shared, we consistently get "Could not update. Currently locked." errors when the two threads are writing to the same table at the same time (and the two threads are using separate OleDBConnections). I have tried using and not using transactions, and the error is the same. Can anyone tell me what else I may need to do to avoid the "Could not update" problem I am experiencing? Thank you...
modified on Wednesday, April 8, 2009 4:38 PM
-
I am working with Microsoft Access MDBs (2003 Format) on a project and have a situation where two threads can be inserting records in the same table at the same time. I am using OleDB to open the Access MDB, create commands and transactions, and insert the records. The connection string to open the MDB is essentially the following:
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Data Source = SOMENAME.MDB"Even though I am trying to use record locking with this connection string, and the MDB is certainly shared, we consistently get "Could not update. Currently locked." errors when the two threads are writing to the same table at the same time (and the two threads are using separate OleDBConnections). I have tried using and not using transactions, and the error is the same. Can anyone tell me what else I may need to do to avoid the "Could not update" problem I am experiencing? Thank you...
modified on Wednesday, April 8, 2009 4:38 PM
AFAIK Access has PAGE locking, not record locking. I don't know if it still applies but MS used to state that Access is NOT a multiuser database and therefore record locking is not supported. Obviously the page is bigger than 1 record and your multithreaded app is screwing it up. I used earlier versions with multiple users connected to the same data database with no real locking problems, but that is very different to a threaded application (which was not available on the desktop in those days).
Never underestimate the power of human stupidity RAH
-
I am working with Microsoft Access MDBs (2003 Format) on a project and have a situation where two threads can be inserting records in the same table at the same time. I am using OleDB to open the Access MDB, create commands and transactions, and insert the records. The connection string to open the MDB is essentially the following:
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Data Source = SOMENAME.MDB"Even though I am trying to use record locking with this connection string, and the MDB is certainly shared, we consistently get "Could not update. Currently locked." errors when the two threads are writing to the same table at the same time (and the two threads are using separate OleDBConnections). I have tried using and not using transactions, and the error is the same. Can anyone tell me what else I may need to do to avoid the "Could not update" problem I am experiencing? Thank you...
modified on Wednesday, April 8, 2009 4:38 PM
Assuming the two update threads belong to a single process, I would delegate all DB modifications to one specialized thread, and probably give it an input queue. Or choose another database (SQL Server Express). The alternative, assuming DB load is low, is providing retry loops for every modification, with an appropriate sleep value of course. :)
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
-
AFAIK Access has PAGE locking, not record locking. I don't know if it still applies but MS used to state that Access is NOT a multiuser database and therefore record locking is not supported. Obviously the page is bigger than 1 record and your multithreaded app is screwing it up. I used earlier versions with multiple users connected to the same data database with no real locking problems, but that is very different to a threaded application (which was not available on the desktop in those days).
Never underestimate the power of human stupidity RAH
Thanks for the reply. FWIW, the earliest versions of Access only supported page locking, but the Access 2003 format, at least in theory, is supposed to support record-level locking. If you happen to have such a version of an Access MDB, under its Tools/Options/Advanced tab you can see that there is an option to open databases using record-level locking. And, in the connection string I am using the parameter "Jet OLEDB:Database Locking Mode=1" is supposed to be for record level locking (Mode=0 would be page locking). I agree that the multi-threading seems to be using page locking, so I am curious if anyone has had any success with Access MDBs under a similar, but certainly not recommended, scenario. Unfortunately using SQL Express is not currently an option on this project; users may have to suffer with slower non-threaded performance if I can't figure something out.
-
Assuming the two update threads belong to a single process, I would delegate all DB modifications to one specialized thread, and probably give it an input queue. Or choose another database (SQL Server Express). The alternative, assuming DB load is low, is providing retry loops for every modification, with an appropriate sleep value of course. :)
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
Luc Pattyn wrote:
I would delegate all DB modifications to one specialized thread, and probably give it an input queue
Thanks for the reply. Delegating all DB modifications to one thread with an input queue is an interesting thought. I'll probably give that a try if I can not figure out why this version of an Access MDB, which is supposed to support record-level locking, does not seem to at least in this multi-threaded app. Unfortunately SQL Server Express is not an option, and a retry loop could get real ugly in this app.
-
Thanks for the reply. FWIW, the earliest versions of Access only supported page locking, but the Access 2003 format, at least in theory, is supposed to support record-level locking. If you happen to have such a version of an Access MDB, under its Tools/Options/Advanced tab you can see that there is an option to open databases using record-level locking. And, in the connection string I am using the parameter "Jet OLEDB:Database Locking Mode=1" is supposed to be for record level locking (Mode=0 would be page locking). I agree that the multi-threading seems to be using page locking, so I am curious if anyone has had any success with Access MDBs under a similar, but certainly not recommended, scenario. Unfortunately using SQL Express is not currently an option on this project; users may have to suffer with slower non-threaded performance if I can't figure something out.
I presume you have jumped MS directly with the problem and got no respose? It's been has 15 yrs since I worked with Access. :-O
Never underestimate the power of human stupidity RAH
-
I presume you have jumped MS directly with the problem and got no respose? It's been has 15 yrs since I worked with Access. :-O
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
I presume you have jumped MS directly with the problem and got no respose?
We don't have direct MS support; and their knowledge base (and other groups) has not yet shed any light on what I am seeing. However, I continue to hold out hope that I am missing something painfully obvious that will soon become clear. OTOH, I may just have to travel down another road.
Mycroft Holmes wrote:
It's been has 15 yrs since I worked with Access.
Lucky you. :)