MS Access (MDB) 并发

这是一个老问题，但没有人真正回答过它.以下是问题:

This is an old question, but nobody has ever actually answered it. Here are the questions:

1. MDB 中如何处理并发读取?
2. 在 MDB 中如何处理并发更新/删除?
3. 是否有锁的概念以及如何在 .NET 应用中利用它?
4. 将 MDB 文件放在网络共享上是好主意还是坏主意?

前两个问题基本上可以用一个解释来回答.这里有一个关键警告:我在这里给出的答案特定于 Jet MDB(及其变体)，并不完全适用于从 A2007 开始引入的新文件格式，即 ACCDB 格式.我还没有完全探讨从 ACE 中移除 Jet ULS 的影响，下面的一些评论可能假设 Jet ULS 位于引擎盖下方.但是，对于很多事情，您可以将LACDBB 文件"替换为LACDBB 文件".用于LDB 文件"结果是一样的.

The first two questions can basically be answered with one explanation. One key caveat here: the answers I'm giving here are specific to Jet MDBs (and their variants) and do not completely apply to the new file format introduced starting with A2007, i.e., ACCDB format. I have not fully explored the implications of the removal of Jet ULS from the ACE and some of the comments below may assume Jet ULS below the hood. For a lot of things, though, you can substitute "LACCDB file" for "LDB file" and the results will be the same.

Jet 数据库引擎通常被称为文件服务器".数据库，因为没有服务器端恶魔管理服务器上的数据文件的 I/O.这意味着所有使用 Jet MDB 的客户端都直接读取文件.

The Jet database engine is often referred to as a "file server" database in that there is no server-side demon managing I/O with the data files on the server. What this means is that all clients using a Jet MDB are reading the file directly.

当然，如果没有一些内置机制来处理对文件的并发访问，那当然会导致灾难.

That is, of course, a recipe for disaster if there's not some mechanism built in for handling concurrent access to the file.

Jet 使用记录锁定文件，如果您的 MDB 是MyFile.MDB"记录锁定文件将位于同一文件夹中并称为MyFile.LDB".LDB 文件记录了哪些 Jet ULS 用户打开了 MDB 文件，该用户从哪个工作站连接，以及协商并发问题所需的所有信息.

Jet uses a record-locking file, where if your MDB is "MyFile.MDB" the record locking file will be in the same folder and called "MyFile.LDB". The LDB file records what Jet ULS users have the MDB file open, what workstation that user is connected from, and all the information necessary for negotiating concurrency issues.

现在，对于那些切入客户端/服务器数据库引擎的人来说，这可能看起来很原始和危险，但在开发 Jet 数据库引擎时，其目的是用作小型工作组的桌面数据库引擎，并且它正在与其他桌面数据库引擎(如 xBase 和 Paradox)竞争，这两种引擎都使用类似的锁定文件来管理来自多个客户端的数据文件的并发使用.

Now, to those who cut their teeth on client/server database engines, this may seem primitive and dangerous, but at the time the Jet database engine was developed, its purpose was to be used as a desktop database engine for small workgroups, and it was competing with other desktop db engines like xBase and Paradox, both of which used analogous locking files to manage concurrent use of data files from multiple clients.

在 Jet 数据库文件中，锁定应用于数据页(在 Jet 4 中增加到 4K，而在 Jet 3.x 和之前，它们是 2K)，或者如果数据表是在记录级别上最初创建以使用记录级锁定.在 Jet 4 的早期，许多人发现记录级锁定非常慢，特别是在使用悲观锁定时，因此许多 Access 开发人员除了页面级锁定之外从不使用任何东西(@David Fenton 举手！).

Within a Jet database file, locks are applied either on data pages (which in Jet 4 were increased to 4K, whereas in Jet 3.x and before, they were 2K), or at the record level if the data table was originally created to use record-level locking. In the early days of Jet 4, record-level locking was found by many to be quite slow, particularly when using pessimistic locking, so a lot of Access developers never used anything but page-level locking (@David Fenton raises hand!).

事实上，使用乐观锁可以避免悲观锁带来的大部分并发问题.

In fact, when using optimistic locking, you avoid most of the concurrency issues that would come with pessimistic locking.

一些注意事项:

1. 在 DAO 中，记录级锁定不可用，您只能获得页面级锁定.

1. from DAO, record-level locking is unavailable, and you only ever get page-level locking.

来自 DAO，有许多用于控制乐观/悲观锁定的选项，特别是 OpenRecordset 方法的 LockEdits 参数，但也与 OpenRecordset Options 参数中指定的某些设置交互(例如，选项 dbReadOnly 不能与 LockEdits 一起使用).除了锁定之外，还有一致性/不一致更新的选项，所有这些都可以与事务交互(例如，未提交事务中的更改不会对其他用户可见，因此不会与他们发生冲突，但它可以对涉及的表设置只读锁).

from DAO, there are a number of options for controlling optimistic/pessimistic locking, in particular the LockEdits argument of the OpenRecordset method, but that also interacts with certain of the setting specified in the OpenRecordset Options argument (e.g., Option dbReadOnly cannot be used with LockEdits). In addition to locking, there are also options for consistent/inconsistent updates, and all of this can interact with transactions (e.g., changes within an uncomitted transaction are not going to be visible to other users and thus will not conflict with them, but it can put read-only locks on the tables involved).

从 ADO/OLEDB 中，这些 Jet 并发控制结构将被映射到 ADO/OLEDB 中的相关函数和参数上.因为我只从 Access 使用 Jet，所以我只通过 DAO 与它交互，所以我不能就如何使用 ADO/OLEDB 控制这些提供建议，但重点是 Jet 数据库引擎在访问它时提供对记录锁定的控制以编程方式(而不是通过 Access UI)——它只是更复杂.

From ADO/OLEDB, these Jet concurrency control structures are going to be mapped onto the relevant functions and arguments found in ADO/OLEDB. Since I use Jet only from Access, I interact with it only via DAO, so I can't advise on how you control these with ADO/OLEDB, but the point is that the Jet database engine offers control of your record locking when accessing it programmatically (as opposed to through the Access UI) -- it's just more complicated.

我不能在这里提供任何建议，除了您可能会使用 OLEDB 作为您的数据接口，但关键是锁定功能/控制存在于数据库引擎本身中，因此可能有一种方法可以通过 OLEDB 控制它.不过，这可能不太好，因为在我看来 OLEDB 是围绕客户端/服务器架构设计的，而 Jet 基于文件的锁定可能不会以优雅的方式映射到该架构上.

I can't offer any advice here, other than that you'd likely use OLEDB as your data interface, but the point is that the locking functionality/control is there in the db engine itself, so there's likely a way to control it via OLEDB. It may not be pretty, though, as it seems to me that OLEDB is designed around client/server architectures, and Jet's file-based locking may not map onto that in an elegant way.

Jet 对任何网络连接中最轻微的故障都非常敏感.因此，低带宽网络会增加通过慢速连接打开 Jet 数据库的漏洞.

Jet is very sensitive to the slightest hiccup in any network connection. Because of that, low-bandwidth networks can increase the vulnerability of Jet databases open across a slow connection.

这是因为数据库文件的主要块必须通过网络连接到本地计算机的 RAM 进行处理.现在，许多人错误地声称整个 MDB 文件都被拉过线，或者整个表都被拉过线.这不是真的.相反，Jet 首先请求索引(并且请求不超过完成查询所需的请求)，然后根据该结果准确确定需要哪些数据页，然后仅拉取那些页.这是令人惊讶的高效和快速.

This is because major chunks of the database file have to be pulled across the wire to the local computer's RAM for processing. Now, many people erroneously claim that the entire MDB file is pulled across the wire, or that whole tables are pulled across the wire. This is not true. Instead, Jet first requests the indexes (and requests no more than necessary to fulfill the query) and then from that result determines exactly which data pages are needed and then pulls only those pages. This is surprisingly efficient and fast.

此外，Jet 还进行了一些非常智能的缓存，这意味着第一个数据请求可能需要一段时间，但由于缓存的原因，对相同数据的后续请求几乎立即发生.

Also, Jet does some very intelligent caching that can mean that a first data request can take a while, but subsequent requests for the same data happen nearly instantaneously because of caching.

现在，如果你没有很好地索引你的表，你可能最终会拉取整个表并进行全表扫描.同样，如果您基于不属于 Jet 的 SQL 方言的客户端函数的标准，您最终可能会拉出一个完整的表(排序，例如， Replace(MyField, A", Z")导致全表扫描).但是，对于客户端/服务器架构，这种事情也将是低效的，因此正确索引事物并小心使用 UDF 或非 Jet 兼容功能只是常识性的架构设计.一般来说，对客户端/服务器有效的相同事物对 Jet 也有效(主要区别在于，对于 Jet，您最好使用持久连接，以避免重新创建 LDB 文件的开销，这意义重大).

Now, if you haven't indexed your tables well, you may end up pulling the whole table and doing a full table scan. Likewise, if you base criteria on client-side functions that are not part of Jet's SQL dialect, you could end up pulling a full table (sorting on, say, Replace(MyField, "A", "Z") is likely to cause a full table scan). But that kind of thing is going to be inefficient with a client/server architecture, too, so it's just common-sense schema design to index things properly and be careful with using UDFs or non-Jet-compatible functions. In general, the same things that are efficient with client/server are going to be efficient with Jet (the major difference being that with Jet you're better off with a persistent connection in order to avoid the overhead of recreating the LDB file, which is significant).

要避免的另一件事是尝试通过 WiFi 连接使用 Jet 数据.我们都知道 WiFi 是多么不可靠，它只是在尝试通过 WiFi 连接处理 Jet 数据时遇到麻烦.

The other thing to avoid is trying to use Jet data across a WiFi connection. We all know how unreliable WiFi is, and it's just asking for trouble trying to work with Jet data across a WiFi connection.

底线:

如果您将 MDB 用作数据存储以提供来自 Web 服务器的数据，则应将数据尽可能靠近 Web 服务器的 RAM.这意味着在可能的情况下，在连接到物理 Web 服务器的磁盘卷上.在不可能的情况下，您需要快速、可靠的 LAN 连接.如今，数据中心中的 GB LAN 非常普遍，我很乐意通过这种连接处理 Jet 数据.

If you're using an MDB as a data store to serve data from a web server, you should put the data as close to the web server's RAM as possible. That means that where possible, on a disk volume that is attached to the physical web server. Where that's not possible, you want a fast, reliable LAN connection. GB LANs in data centers are pretty common these days and I'd be very comfortable working with Jet data across that kind of connection.

对于共享使用，例如，运行 VB.NET 桌面应用程序的多个客户端工作站共享单个 Jet MDB 作为数据存储，将数据文件放在可靠的文件服务器上是非常安全的.在可能的情况下，最好将 Jet MDB 文件放在不具有多种用途的机器上(例如，运行 Exchange、SQL Server 并充当文件服务器和打印服务器的域控制器可能不是最佳位置).像 Exchange 这样的应用程序可能会严重干扰文件服务器的功能，我通常建议不要将 MDB 文件放在作为 Exchange 服务器的多任务服务器上，除非它的容量非常小.

For shared use, e.g., multiple client workstations running a VB.NET desktop app sharing a single Jet MDB as data store, it's pretty safe to have the data file on a reliable file server. Where possible, it's a good idea to put your Jet MDB files on machines that aren't serving multiple purposes (e.g., your domain controller that is running Exchange, SQL Server and acting as file server and print server may not be the best location). Apps like Exchange can badly interfere with file server functionality, and I'd usually recommend never putting MDB files on a server that is multi-tasking as an Exchange server unless it's extremely low volume.

其他注意事项:

1. 切勿尝试在复制的文件系统上分发 MDB，除非所有用户都使用相同的副本.也就是说，如果您有两台服务器在它们之间复制文件，甚至不要考虑从两台服务器编辑 MDB 文件.这几乎会立即损坏文件.

1. never try to distribute an MDB on a replicated file system, unless all users are using the same replica. That is, if you have two servers replicating files between them, don't even think about editing the MDB file from both servers. This will corrupt the file almost immediately.

我建议不要将任何 MDB 存储在通过本机 Microsoft SMB 网络提供服务的本机 Windows 文件系统之外的任何其他设备上.这意味着没有 Novell、没有 Linux、没有 SAMBA.造成这种情况的关键原因是 Jet 有明显的低级钩子到 Windows 文件系统中的一些低级锁定功能，这些功能不是 100% 复制到其他文件系统上的.现在，我对此非常保守，许多有能力的 Access 开发人员都报告说，使用正确配置的 Novell 文件服务器获得了出色的结果(通常需要进行一些记录锁定调整，尽管现在可能不太相关——我不知道)甚至不知道 Novell 是否已经存在！)，以及运行 SAMBA 的基于 Linux 的文件服务器的惊人性能.我对此持谨慎态度，并会推荐任何客户反对它(这也包括各种 SAN 设备，因为它们中的很多都是基于 Windows 的).

I would recommend against storing any MDB on anything other than a native Windows file system served via native Microsoft SMB networking. This means no Novell, no Linux, no SAMBA. The key reason for this is that there are apparently low-level hooks from Jet into some low-level locking functionality in the Windows file system that are not 100% replicated on other file systsm. Now, I'm very conservative on this, and many competent Access developers have reported excellent results with properly-configured Novell file servers (often there need to be some record-locking adjustments, though that may be less relevant these days -- I don't even know if Novell exists any more!), and blazing performance with Linux-based file servers running SAMBA. I'm cautious on this and would recommend any client against it (this includes various SAN devices, as well, since not a lot of them are Windows-based).

出于同样的原因，我绝不会在任何虚拟化文件系统上运行它们.但是，我有一个客户多年来一直在 Mac Air 上的 Parallels 下运行她的单用户 Access 应用程序，现在没有出现任何问题.但它是单用户的，所以锁定问题相对较小.

I would never run them on any virtualized file system for the same reasons. However, I've got a client who has been running her single-user Access app under Parallels on a Mac Air for several years now without a single problem. But it's single-user, so the locking issues are going to be relatively minor.

我不知道这是否能回答您的问题.这一切都基于我作为 Access 开发人员经常使用 Jet 的 13 年以及对唯一出版的关于 Jet 的书籍 Jet 数据库引擎程序员指南(仅适用于 Jet 3.5)的研究.我没有提供任何真正的引文，但如果有人需要我所说的任何内容的详细信息，我会尽可能地进行研究.

I don't know if that answers your questions or not. It's all based on my 13 years of regular use of Jet as an Access developer and study of the only published book on Jet, the Jet Database Engine Programmers Guide (for Jet 3.5 only). I haven't provided any real citations, but if anybody needs some details on anything I've said, I'll do the research if I can.