Using MS Jet with .mdb databases in ASP web site

[Peter Hers]

Hi

Looking for some general advice: I have taken over support of a large and complex web site, which uses multiple Access databases. At present it makes use of the MS Jet database engine (Provider=Microsoft.Jet.OLEDB.4.0). (as well as MySQL, used in a Snitz forum).

After solving many specific problems I am left with a nasty situation, where certain database calls appear to cause the Jet engine to freeze, and everything halts until pages time out and the blockage is cleared. After such a timeout everything runs nicely until the next incident.

These incidents are random. There is some evidence that this occurs when multiple pages employ the recordset.RecordCount method at the same time.

Questions:

Is this a known problem? Where can I find information on similar problems?

Should I move the entire site away from the Jet database engine to another provider?

[ChrisPadgham]

The jet engine is single threaded so if one process is holding a lock everything else will wait. It is quite OK where there is not a great number of concurrent users but it becomes problematic with the number of concurrent users rises.

[Peter Hers]

Chris
Thanks for the reply. Yes, I'm aware of how the locks work, and all coding takes that into account. Locks in Jet are either at page or row level, and a lock applied by one process should not hold up other processes unless they require the same page or row. This should only become a problem if the coding introduces the possibility of a "deadly embrace" between two or more processes which apply locks and then attempt further requests while the prior locks are still in effect.

[ChrisPadgham]

There are some things that lock the whole database, recordcount not being one of them, relinking of tables, drop tables etc. Perhaps the recordcount is a red herring and not the cause of this problem.