G
Guest
Most of the postings related to concurrency problems seem related to multiple
users accessing a single database. We are only running on a single machine
but we do have several processes which access the database simultaneously.
We are using a Microsoft Access database (Access 2000 file format) with 43
tables. We have developed 2 VB GUI applications that run simultaneously on
one Windows XP machine. Each app uses one ADODB Connection (Microsoft
ActiveX Data Objects 2.8 Library) with Microsoft.Jet.OLEDB.4.0 Data Provider.
There are also several .NET processes running as Windows services, typically
3 or 4 of them, all referencing a VB.NET DLL containing most of the code to
access the database. The services themselves are written in C#. Database
access is done through .NET ODBC API with "Driver={Microsoft Access Driver
(*.mdb)}". The VB.NET DLL implements a shared connection pool that creates
new connections as needed by multiple threads executing in a process.
Typically there are 2 or 3 of these connections open per process (service).
A transaction is used in one area of the code to insert records into three
tables (rolled back if failed). The default transaction IsolationLevel of
ReadCommitted (shared locks) is used. If this transaction fails with a
"locked" exception, it is rolled back and tried again up to five times, with
an exponential backoff and retry period starting at 100 milliseconds. One
other commonly-used insert statement also uses similar retry logic, but most
other database accesses do not retry upon failure.
Intermittently, we see "currently locked" errors that the process does not
seem to be able to recover from, no matter how many retries and even
continues with subsequent attempts to access the database. They typically
affect all processes in the application, and our only known recourse is to
restart all processes (or reboot the machine).
Any ideas on alternatives to the "reboot" solution?
users accessing a single database. We are only running on a single machine
but we do have several processes which access the database simultaneously.
We are using a Microsoft Access database (Access 2000 file format) with 43
tables. We have developed 2 VB GUI applications that run simultaneously on
one Windows XP machine. Each app uses one ADODB Connection (Microsoft
ActiveX Data Objects 2.8 Library) with Microsoft.Jet.OLEDB.4.0 Data Provider.
There are also several .NET processes running as Windows services, typically
3 or 4 of them, all referencing a VB.NET DLL containing most of the code to
access the database. The services themselves are written in C#. Database
access is done through .NET ODBC API with "Driver={Microsoft Access Driver
(*.mdb)}". The VB.NET DLL implements a shared connection pool that creates
new connections as needed by multiple threads executing in a process.
Typically there are 2 or 3 of these connections open per process (service).
A transaction is used in one area of the code to insert records into three
tables (rolled back if failed). The default transaction IsolationLevel of
ReadCommitted (shared locks) is used. If this transaction fails with a
"locked" exception, it is rolled back and tried again up to five times, with
an exponential backoff and retry period starting at 100 milliseconds. One
other commonly-used insert statement also uses similar retry logic, but most
other database accesses do not retry upon failure.
Intermittently, we see "currently locked" errors that the process does not
seem to be able to recover from, no matter how many retries and even
continues with subsequent attempts to access the database. They typically
affect all processes in the application, and our only known recourse is to
restart all processes (or reboot the machine).
Any ideas on alternatives to the "reboot" solution?