Access Sharing database over Network vs Mysql multiple users Something no one mentions and I find important to stress is that Access has a file locking mechanism for sharing the database on a network share which is orders of magnitude more efficient than any network database out there and therefore MySQL is no different.

The reason is the following, a file locking mechanism permits all clients of an Access database to cache somehow the database file and therefore to execute queries on it as if it were a local file. This of course means very little data is sent over the network in the case for instance of a SELECT which returns 5000 records, because the select is actually returned from the cached file.

A traditional client server model such as Mysql instead means that a query for 5000 records will involve a lot of TCP packets between server and clients to return the whole 5000 records from server to client, that will involve a high latency between the query and the completion of the return of results.

A file locking mechanism of course has its limits which usually incur when more than 5 users share a database heavily with a lot of modifications to it, in that case the locking mechanism (which in Access can be very selective up to row locking) will cause more delay than say a client server database which could theorically allow a lot more of concurrency.

I came therefore to the conclusion that if you need to share a database on a LAN with a couple of users therefore Access has few competitors, if instead you need to host a database on the internet or with tens/hundreds of users of course Mysql is better.

I also notice many other databases tried implementing the file locking mechanism but most failed in stability or weren't able to make it work as efficiently as Microsoft with Access which must have spent a good load of time and money on it. SQLite and H2 database locking mechanisms for instance are either very low performance or are highly not recommended for risks of corruption of the database:


  SQLite uses POSIX advisory locks to implement locking on Unix. On
  Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system
  calls. SQLite assumes that these system calls all work as advertised.
  If that is not the case, then database corruption can result.


http://www.sqlite.org/lockingv3.html


  This database file locking mechanism uses native file system lock on
  the database file. No *.lock.db file is created in this case, and no
  background thread is started. This mechanism may not work on all
  systems as expected. [...] This feature is relatively new. When using
  it for production, please ensure your system does in fact lock files
  as expected.


http://h2database.com/html/advanced.html#file_locking_serialized

Can anyone shed some light on how or why is Access so efficient and whether there are any serious competing products which follow the same file locking shared on a network approach and which implement it in an efficient way?
When the own vendor says the following:


  "While Microsoft Jet is consciously (and continually) updated with
  many quality, functional, and performance improvements, it was not
  intended (or architected) for the high-stress performance required by
  24x7 scenarios, ACID transactions, or unlimited users, that is,
  scenarios where there has to be absolute data integrity or very high
  concurrency."
  
  "When you need unlimited users, 24x7 support, and ACID transactions,
  Microsoft strongly recommends that you use Microsoft SQL Server with
  Internet Information Server (IIS). "


I wouldn't have many expectations for integrity and performance for multiple users over a network.