的MS Access（MDB）并发 [英] MS Access (MDB) concurrency

问题描述

对于一个小项目，我需要利用具有很轻的要求，一个简单的数据库：几张桌子，不超过几千条记录更多总，2或3的用户。我工作在.NET环境。

作为一个数据库服务器（甚至是那些实施例preSS版本）似乎是一个巨大矫枉过正在这种情况下，一个非常简单的MDB数据库可以做为大多数的要求。不过，我，关心并发性。我的想法是把网络共享的.mdb文件，并让用户访问他们的基于.NET的客户端文件。该数据库主要是针对只读操作，但用户偶尔会需要更新/删除记录的为好。如果这是不可能的时候（由于分贝被锁定或其他），我可以保持更新在客户端上，并在稍后的时间处理它们。

这个问题本身随之而来以下几点：

• 如何为并发读取MDB处理？
• 如何并发更新/删除MDB处理？
• 在有锁的概念以及如何利用它在.NET应用程序？
• 是放置在网络共享好可怕的想法MDB文件？

由于我使用.NET的，我也很想知道我怎么能检测到任何并发问题，并采取适当的行动。也就是说，我要赶，你会建议采取什么行动​​哪个异常？

修改：这可能是问题的我不好描述，但是大多数答案似乎提醒去为一个完全成熟的数据库服务器。我明白了分歧，有一个服务器安装的利益，并已在事实上实行对MSSQL和Oracle的项目相当数量。在这个问题上，但是，我只关注访问和并发性问题，所以请不要提出一个数据库服务器。

感谢您的帮助。

解决方案

这是一个老问题，但从来没有人真正回答了这个问题。这里有几个问题：

1. 如何为并发读取MDB处理？
2. 如何并发更新/删除MDB处理？
3. 在有锁的概念以及如何利用它在.NET应用程序？
4. 是放置在网络共享好可怕的想法MDB文件？

前两个问题基本上可以回答一个解释。这里的一个关键的一点：我给这里的答案是特定于喷气多边开发银行（及其变体），并不能完全适用于新的文件格式介绍，首先是A2007，即ACCDB格式。我还没有充分挖掘去除喷气ULS的所带来的影响ACE和一些下面的意见可能承担喷气ULS引擎盖下方。对于很多事情，不过，您也可以替换LACCDB文件的LDB文件，其结果将是一样的。

1-2）并发读取/更新/删除

Jet数据库引擎通常被称为中，一个文件服务器的数据库不存在服务器端妖管理I / O与服务器上的数据文件。这意味着，使用一个Jet MDB的所有客户端都直接读取文件。<​​/ P>

这当然是一个灾难，如果有没有内置的处理并发访问文件中的一些机制。

喷气机使用记录锁定的文件，在这里，如果您的MDB是MyFile.MDB的记录锁定的文件将在同一个文件夹中，被称为MyFile.LDB。必要的六味地黄丸的文件记录了什么喷气ULS用户有MDB文件打开了，那么工作站用户从连接，所有的信息，谈判并发问题。

现在，那些谁削减他们的牙齿上的客户机/服务器的数据库引擎，这似乎是原始的和危险的，但在Jet数据库引擎的开发时间，其目的是用作桌面数据库引擎的小型工作组，并且它的竞争与其他桌面数据库引擎喜欢的xBase和悖论，两者使用类似的锁定文件从多个客户管理并发使用的数据文件。

在一个Jet数据库文件，锁应用无论是在数据页（这在喷气4分别提高到4K，而在喷气3.x和之前，他们是2K），或在创纪录的水平，如果数据表是最初创建到使用记录级锁定。在喷射4的初期，记录级锁定被发现的许多是相当缓慢的，使用悲观锁定特别是当，所以很多开发商的访问从来没有使用过任何东西，但页级锁（@大卫芬顿举手！）。

事实上，使用乐观锁时，你避免大部分的并发问题，将配备悲观锁定。

一些注意事项：

1. 从DAO，记录级锁定是不可用的，你永远只能获得页级锁。

2. 从DAO中，有许多选项来控制的乐观/悲观锁定，尤其是OpenRecordset方法的LockEdits参数，但也具有一定的OpenRecordset选项参数指定的设置中进行交互（例如，选项​​dbReadOnly不能与LockEdits使用）。除了锁定，也有获得一致/不一致更新的选项，而这一切可以交易（例如，一个uncomitted事务中的变化不会是可见的其他用户进行交互，因此不会与他们发生冲突，但它可以把只读所涉及的表）锁。

从ADO / OLEDB，这些喷气并发控制结构将被映射到的ADO / OLEDB找到了相关的功能和参数。由于我使用Jet仅从访问，我与它只有通过DAO进行交互，所以我不能对你如何使用ADO / OLEDB控制这些提醒，但问题是，Jet数据库引擎提供的记录锁控制访问它时，以编程方式（而不是通过访问UI） - 它只是更复杂

3）锁和.NET

我不能提供任何的建议是，比其他你可能会使用OLEDB作为数据接口，但问题是，锁定功能/控制是有在数据库引擎本身，所以有可能的方式通过OLEDB控制它。它可能不是pretty的，不过，因为它在我看来，OLEDB是围绕客户机/服务器体系结构，以及李连杰的基于文件的锁定可能不会映射到，在一个优雅的方式。

在网络共享

4）MDB

Jet是最轻微的打嗝的任何网络连接非常敏感。正因为如此，低带宽网络可以提高喷气数据库的漏洞，通过慢速连接打开。

这是因为在数据库文件的主要组块具有跨线被拉到在本地计算机的RAM进行处理。现在，很多人错误地声称，整个MDB文件，通过线路拉，或者说整个表跨线拉出。这不是真的。相反，喷气首先请求索引（和请求不超过必要履行查询），然后从该结果确定究竟需要哪些数据页然后再换只有这些页面。这是令人惊讶的高效，快速。

此外，李连杰做了一些非常聪明的缓存，可以意味着第一数据请求可能需要一段时间，但对于同一​​数据的后续请求发生，因为缓存几乎瞬间。

现在，如果你还没有收录你的表不错，你可能最终拉动整个表，做一个全表扫描。同样，如果你在客户端功能的基础标准，不属于Jet的SQL方言的一部分，你可能最终拉动全表（排序的，比方说，替换（MyField的，A，Z）是可能的原因全表扫描）。但是，这种事情将是低效与客户端/服务器架构，也因此它只是常识性的架构设计，索引正确的事情，并要小心使用UDF的或非喷气兼容的功能。一般情况下，这是有效的客户机/服务器相同的事情会是有效的与Jet（主要的不同之处在于与Jet你最好用，以避免重新创建LDB文件的开销，持久连接这是显著）。

另外要避免试图跨越WiFi连接到使用Jet数据。我们都知道不可靠的WiFi是，它只是自找麻烦试图跨越WiFi连接使用Jet数据。

底线：

如果您使用的是MDB作为数据存储从Web服务器提供数据服务，你应该把数据尽量靠近Web服务器的RAM越好。这意味着，在可能的情况，即附加到物理网络服务器磁盘卷上。当这是不可能的，你想有一个快速，可靠的LAN连接。 GB局域网中的数据中心是常见的pretty的这些天，我会很舒服的跨越那种连接与Jet数据的工作。

有关共同使用，例如，运行VB.NET桌面应用程序共享单个的Jet MDB的数据存储多个客户端工作站，这是pretty的安全有可靠的文件服务器上的数据文件。如果可能的话，建议你把在未提供多种用途的机器你的Jet MDB文件是一个好主意（例如，运行Exchange，SQL Server和充当文件服务器和打印服务器的域控制器可能不是最好的位置） 。如Exchange应用程序可以与严重的文件服务器功能的干扰，而且我通常建议从来没有把一个服务器是多任务为Exchange服务器上的MDB文件，除非它是极低的音量。

其他注意事项：

1. 从未尝试在复制的文件系统分配一个MDB，除非所有用户都使用相同的复制品。也就是说，如果你有两个服务器复制它们之间的文件，甚至不考虑从两个服务器编辑MDB文件。这将损坏该文件几乎是立刻。

2. 我会建议不要在任何非本机Windows文件系统通过本地微软SMB网络存储提供任何MDB。这意味着，没有Novell公司，没有Linux，没有SAMBA。关键的原因是，有明显来自喷气低级别挂钩插入到是不是100％复制其他文件systsm Windows文件系统中的一些低级别的锁定功能。现在，我很保守这一点，许多主管访问开发者已经报告了正确配置的Novell文件服务器的优异成绩（通常我们需要有一些记录锁定的调整，尽管这可能是不太相关的，这些天 - 我不甚至不知道是否存在Novell的更多！），和炽烈的性能与运行Samba的基于Linux的文件服务器。我很谨慎这一点，并会建议任何客户端反对（这包括各种SAN设备，为好，因为不是他们中的很多都是基于Windows的）。

3. 我绝不会在出于同样的原因的任何虚拟文件系统上运行它们。不过，我已经得到了谁已经运行下的Parallels在Mac空气她的单用户访问应用程序，几年来没有一个单一的问题，一个客户端。但它的单用户，所以锁定的问题将是相对较小的。

我不知道这是否回答您的问题或没有。这一切都基于我13年的经常使用的Jet作为Jet的Access开发的唯一出版的书和学习，Jet数据库引擎程序员指南（用于Jet 3.5只）。我还没有提供任何真正的引用，但是如果有人需要的东西的一些细节我已经说过了，我会如果我可以做研究。

For a small project I need to utilize a simple database with very light requirements: few tables, no more than few thousands of records in total, 2 or 3 users. I am working in .NET environment.

As a database server (even those Express editions) seems like a huge overkill in this case, a very simple MDB database could do for most of the requirements. I am however, concerned about concurrency. My idea is to place the .mdb file on a network share and let users access this file from their .NET-based clients. The db is mostly aimed at read-only operations but users will occasionally need to update/delete records as well. If this will not be possible at the time (due to the db being locked or whatever), I can hold the updates on the client and process them at a later time.

The question itself goes along these points:

• How are concurrent reads handled in MDB?
• How are concurrent updates/deletes handled in MDB?
• Is there a concept of locks and how can I leverage it in a .NET app?
• Is placing the MDB file on a network share good or horrible idea?

As I am working in .NET, I would also love to know how can I detect any concurrency problems and take appropriate action. I.e., which exception should I catch and what action would you recommend to take?

EDIT: It may be my bad description of the problem, but most answers seem to advise going for a full blown DB server. I do understand the differences and benefits of having a server installation and have in fact implemented a fair number of projects on MSSQL and Oracle. In this question, however, I am only concerned with Access and its concurrency issues, so please do not suggest a db server.

Thanks for your help.

解决方案

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

1. How are concurrent reads handled in MDB?
2. How are concurrent updates/deletes handled in MDB?
3. Is there a concept of locks and how can I leverage it in a .NET app?
4. Is placing the MDB file on a network share good or horrible idea?

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.

1-2) Concurrent reads/updates/deletes

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 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.

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.

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.

Some caveats:

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

2. 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).

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.

3) Locks and .NET

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.

4) MDB on a network share

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.

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.

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.

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).

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.

The bottom line:

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.

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.

Other considerations:

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.

2. 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).

3. 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.

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.

这篇关于的MS Access（MDB）并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！