Jet OLEDB:事务提交模式和Jet OLEDB:用户提交同步有什么区别? [英] What's the difference between Jet OLEDB:Transaction Commit Mode and Jet OLEDB:User Commit Sync?

问题描述

相对而言，两个Jet/OLE DB参数都是相对的有据可查我无法理解这两个连接参数之间的区别:

第一个:

Jet OLEDB:事务提交模式 (DBPROP_JETOLEDB_TXNCOMMITMODE)

指示Jet是否将数据写入到 同步或异步磁盘 提交交易时.

第二个:

Jet OLEDB:用户提交同步 (DBPROP_JETOLEDB_USERCOMMITSYNC)

指示是否进行了更改 交易中写入 同步或异步模式.

有什么区别?何时使用哪个?

解决方案

这很长，所以这是简短的答案:

不要设置任何一个.这两个选项的默认设置可能是正确的.第一个是事务提交模式，它控制Jet的隐式事务，并在显式事务之外应用，并设置为YES(异步).第二个控件控制Jet在显式事务期间如何与其临时数据库进行交互，并将其设置为NO(同步).我无法想到您想要在此处覆盖默认值的情况.但是，您可能需要显式设置它们，以防万一您在Jet数据库引擎设置已更改为默认值的环境中运行.

现在，详细的解释:

我已经遍历了许多与Jet有关的资源，以查看是否可以找到这里的情况.这两个OLEDB常数似乎映射到顶级DAO DBEngine对象的SetOptionEnum的这两个成员上(详细信息

UserCommitSync:当设置的值为是"时，Microwsoft Jet将等待提交完成.其他任何值都意味着Microsoft Jet将异步执行提交.

现在，这只是对您已经说过的内容的重述.令人沮丧的是，第一个的默认值为NO，而第二个的默认值为YES.如果他们确实控制着同一件事，那么您希望他们具有相同的价值，否则价值冲突会成为问题.

但是密钥实际上是名字，它反映了Jet关于在事务内部和外部提交数据写入方式的历史.在Jet 3.0之前，Jet默认在显式事务之外默认同步更新，但是从Jet 3.0开始，引入了IMPLICIT事务，并且默认情况下使用它们(在Jet 3.5中有警告，请参见下文).因此，这两个选项之一适用于事务的外部提交(dbImplicitCommitSync)，另一个适用于事务的内部提交(dbUserCommitSync).我终于在《 Jet数据库引擎程序员指南》(第607-8页)中找到了这些内容的详细说明:

UserCommitSynch UserCommitSynch设置确定 是否将更改作为 明确的交易...被写入 同步模式下的数据库，或 异步模式.默认值为...，是，它指定 异步模式.它不是 建议您更改此值 因为在同步模式下 不保证信息已经被 在代码之前写入磁盘 继续执行下一个命令.

ImplicitCommitSync 默认情况下，当 执行添加， 删除或更新以外的记录 显式交易，Microsoft Jet 自动执行内部 称为隐式交易 暂时保存的交易 数据在其内存缓存中，然后 以后将数据作为大块写入 磁盘. ImplicitCommitSync设置 确定是否由 使用隐式交易是 同步写入数据库 模式或异步模式.默认值 值...是否，表示 这些更改将写入 数据库处于异步模式；这 提供最佳性能.如果你 希望隐式交易成为 同步写入数据库 模式下，将值更改为是".如果 你改变价值...你得到 行为类似于Microsoft Jet 版本2.x和更早版本 没有使用显式交易. 但是，这样做也会损害 性能相当大，所以它不是 建议您更改值

注意:不再需要使用 明确交易以改善 Microsoft Jet的性能.一种 使用Microsoft的数据库应用程序 Jet 3.5应该使用显式 仅在以下情况下进行交易 可能需要回滚 变化. Micosoft Jet现在可以 自动执行隐式 交易以提高绩效 无论何时添加，删除或更改 记录.但是，隐式 SQL DML语句的事务 在Microsoft Jet中被删除 3.5 ...请参阅删除SQL DML语句的隐式事务" 在本章后面.

该部分:

删除SQL DML语句的隐式事务 即使有Microsoft的所有工作 Jet 3.0消除了交易 为了获得更好的性能， SQL DML语句仍然放置 在隐式交易中.在 Microsoft Jet 3.5，SQL DML语句 不放在隐式中 交易.这实质上 提高运行SQL时的性能 影响很多的DML语句 数据记录.

尽管此更改提供了 实质性的性能改善， 它也对 SQL DML语句的行为.什么时候 使用Microsoft Jet 3.0及更早版本 使用隐式的版本 SQL DML语句的事务， SQL DML语句回滚(如果有) 声明的一部分不是 完全的.使用Microsoft Jet时 3.5，可能有一些由SQL DML提交的记录 声明而其他人则没有.一个 例如，当 已超过Microsoft Jet缓存.这 缓存中的数据被写入磁盘 下一组记录是 修改并放置在缓存中. 因此，如果连接是 终止，有可能一些 的记录已保存到磁盘，但是 其他人没有.这是一样的 使用DAO循环例程的行为 用显式更新数据 Microsoft Jet 3.0中进行事务处理.如果 你想避免这种行为，你 需要添加显式交易 围绕SQL DML语句进行定义 一套工作，你必须牺牲 性能提升.

困惑了吗?我当然是.

对我来说，关键点似乎是dbUserCommitSync似乎控制着Jet写入它用于暂存EXPLICIT事务的TEMPORARY数据库的方式，而dbImplicitCommitSync与Jet在显式事务之外使用其隐式事务的位置有关.换句话说，dbUserCommitSync在BeginTrans/CommitTrans循环内控制引擎的行为，而dbImplicitCommitSync在显式事务之外控制Jet在异步/同步方面的行为.

现在，关于删除隐式事务"部分:我的理解是，当您在事务外部遍历记录集时隐式事务适用于更新，但不再适用于事务外部的SQL UPDATE语句.可以合理地认为，提高逐行更新性能的优化将是好的，并且对于SQL批处理更新实际上并没有太大帮助，而SQL批处理更新已经相当快了(相对而言). /p>

还请注意，两种方法都可以做到这一点，这使得DoCmd.RunSQL可以进行不完整的更新.也就是说，如果使用DoCmd.RunSQL执行，则在CurrentDB.Execute strSQL上失败的SQL命令dbFailOnError可以运行完成.如果您关闭DoCmd.SetWarnings，则不会得到错误报告，也没有机会回滚到初始状态(或者，如果您被告知错误并决定要提交，则无论如何) ).

所以，我认为这是通过Access UI执行的SQL默认情况下包装在事务中(这是您获得确认提示的方式)，但是如果关闭提示并出现错误，则会得到应用的不完整更新.这与DBEngine设置无关-这与Access UI执行SQL的方式有关(并且可以选择将其关闭/打开).

这与DAO中的更新形成对比，DAO中的更新都包装在从Jet 3.0开始的隐式事务中，但是从Jet 3.5开始，仅顺序更新被包装在隐式事务中-批处理SQL命令(INSERT/UPDATE/DELETE)不是.

至少，这是我的读物.

因此，关于您实际问题中的问题，在设置OLEDB连接时，您可以根据自己的操作为该连接设置Jet DBEngine的选项.在我看来，默认的Jet DBEngine设置是正确的，不应更改-您要使用隐式事务进行编辑，即要遍历记录集并一次更新一行(在显式事务之外) .另一方面，您可以将整个内容包装在事务中并获得相同的结果，因此，实际上，这仅适用于以下情况:您正在移动记录集并进行更新并且未使用显式事务，并且默认设置似乎对我来说很正确.

在我看来，另一种设置UserCommitSync也是您绝对希望保留的，因为在我看来，这适用于Jet在显式事务期间与其临时数据库进行交互的方式.在我看来，将其设置为异步似乎很危险，因为在提交数据时，您基本上不知道操作的状态.

Althoug both Jet/OLE DB parameters are relativly well documented I fail to understand the difference between these two connection parameters:

The first one:

Jet OLEDB:Transaction Commit Mode (DBPROP_JETOLEDB_TXNCOMMITMODE)

Indicates whether Jet writes data to disk synchronously or asynchronously when a transaction is committed.

The second one:

Jet OLEDB:User Commit Sync (DBPROP_JETOLEDB_USERCOMMITSYNC)

Indicates whether changes that were made in transactions are written in synchronous or asynchronous mode.

What's the difference? When to use which?

解决方案

This is very long, so here's the short answer:

Don't set either of these. The default settings for these two options are likely to be correct. The first, Transaction Commit Mode, controls Jet's implicit transactions, and applies outside of explicit transactions, and is set to YES (asynchronous). The second controls how Jet interacts with its temporary database during an explicit transaction and is set to NO (synchronous). I can't think of a situation where you'd want to override the defaults here. However, you might want to set them explicitly just in case you're running in an environment where the Jet database engine settings have been altered from their defaults.

Now, the long explanation:

I have waded through a lot of Jet-related resources to see if I can find out what the situation here is. The two OLEDB constants seem to map onto these two members of the SetOptionEnum of the top-level DAO DBEngine object (details here for those who don't have the Access help file available):

  dbImplicitCommitSync 
  dbUserCommitSync 

These options are there for overriding the default registry settings for the Jet database engine at runtime for any particular connection, or for permanently altering the stored settings for it in the registry. If you look in the Registry for HLKM\Software\Microsoft\Jet\X.X\ you'll find that under the key there for the Jet version you're using there are keys, of which two are these:

  ImplicitCommitSync
  UserCommitSync

The Jet 3.5 Database Engine Programmer's Guide defines these:

• ImplicitCommitSync: A value of Yes indicates that Microsoft Jet will wait for commits to finish. A value other than Yes means that Microsoft Jet will perform commits asynchronously.

• UserCommitSync: When the setting has a value of Yes, Microwsoft Jet will wait for commits to finish. Any other value means that Microsoft Jet will perform commits asynchronously.

Now, this is just a restatement of what you'd already said. The frustrating thing is that the first has a default value of NO while the second defaults to YES. If they really were controlling the same thing, you'd expect them to have the same value, or that conflicting values would be a problem.

But the key actually turns out to be in the name, and it reflects the history of Jet in regard to how data writes are committed within and outside of transactions. Before Jet 3.0, Jet defaulted to synchronous updates outside of explicit transactions, but starting with Jet 3.0, IMPLICIT transactions were introduced, and were used by default (with caveats in Jet 3.5 -- see below). So, one of these two options applies to commits OUTSIDE of transactions (dbImplicitCommitSync) and the other for commits INSIDE of transactions (dbUserCommitSync). I finally located a verbose explanation of these in the Jet Database Engine Programmer's Guide (p. 607-8):

UserCommitSynch The UserCommitSynch setting determines whether changes made as part of an explicit transaction...are written to the database in synchronous mode or asynchronous mode. The default value...is Yes, which specifies asynchronous mode. It is not recommended that you change this value because in synchronous mode, there is no guarantee that information has been written to disk before your code proceeds to the next command.

ImplicitCommitSync By default, when performing operations that add, delete, or update records outside of explicit transactions, Microsoft Jet automatically performs internal transactions called implicit transactions that temporarily save data in its memory cache, and then later write the data as a chunk to the disk. The ImplicitCommitSync setting determines whether changes made by using implicit transactions are written to the database in synchronus mode or asynchronous mode. The default value...is No, which specifies that these changes are written to the database in asynchronous mode; this provides the best performance. If you want implicit transactions to be written to the database in synchronous mode, change the value...to Yes. If you change the value...you get behavior similar to Microsoft Jet versions 2.x and earlier when you weren't using explicit transactions. However, doing so can also impair performance considerably, so it is not recommended that you change the value of this setting.

Note: There is no longer a need to use explicit transactions to improve the performance of Microsoft Jet. A database application using Microsoft Jet 3.5 should use explicit transactions only in situations where there may be a need to roll back changes. Micosoft Jet can now automatically perform implicit transactions to improve performance whenever it adds, deletes or changes records. However, implicit transactions for SQL DML statements were removed in Microsoft Jet 3.5...see "Removal of Implicit Transactions for SQL DML Statements" later in this chapter.

That section:

Removal of Implicit Transactions for SQL DML Statements Even with all the work in Microsoft Jet 3.0 to eliminate transactions in order to obtain better performance, SQL DML statements were still placed in an implicit transaction. In Microsoft Jet 3.5, SQL DML statements are not placed in an implicit transaction. This substantially improves performance when running SQL DML statements that affect many records of data.

Although this change provides a substantial performance improvement, it also introduces a change to the behavior of SQL DML statements. When using Microsoft Jet 3.0 and previous versions that use implicit transactions for SQL DML statements, an SQL DML statement rolls back if any part of the statement is not completed. When using Microsoft Jet 3.5, it is possible to have some of the records committed by SQL DML statement while others are not. An example of this would be when the Microsoft Jet cache is exceeded. The data in the cache is written to disk and the next set of records is modified and placed in the cache. Therefore, if the connection is terminated, it is possible that some of the records were saved to disk, but others were not. This is the same behavior as using DAO looping routines to update data withoug an explicit transaction in Microsoft Jet 3.0. If you want to avoid this behavior, you need to add explicit transactions around the SQL DML statement to define a set of work and you must sacrifice the performance gains.

Confused yet? I certainly am.

The key point to me seems to me to be that dbUserCommitSync seems to control the way Jet writes to the TEMPORARY database it uses for staging EXPLICIT transactions, while dbImplicitCommitSync relates to where Jet uses its implicit transactions OUTSIDE of an explicit transaction. In other words, dbUserCommitSync controls the behavior of the engine while inside a BeginTrans/CommitTrans loop, while dbImplicitCommitSync controls how Jet behaves in regard to asynch/synch outside of explicit transactions.

Now, as to the "Removal of Implicit Transactions" section: my reading is that implicit transactions apply to updates when you're looping through a recordset outside of a transaction, but no longer apply to a SQL UPDATE statement outside a transaction. It stands to reason that an optimization that improves the performance of row-by-row updates would be good and wouldn't actually help so much with a SQL batch update, which is already going to be pretty darned fast (relatively speaking).

Also note that the fact that it is possible to do it both ways is what enables DoCmd.RunSQL to make incomplete updates. That is, a SQL command that would fail with CurrentDB.Execute strSQL, dbFailOnError, can run to completion if executed with DoCmd.RunSQL. If you turn off DoCmd.SetWarnings, you don't get a report of an error, and you don't get the chance to roll back to the initial state (or, if you are informed of the errors and decide to commit, anyway).

So, what I think is going on is that SQL executed through the Access UI is wrapped in a transaction by default (that's how you get a confirmation prompt), but if you turn off the prompts and there's an error, you get the incomplete updates applied. This has nothing to do with the DBEngine settings -- it's a matter of the way the Access UI executes SQL (and there's an option to turn it off/on).

This contrasts to updates in DAO, which were all wrapped in the implicit transactions starting with Jet 3.0, but starting with Jet 3.5, only sequential updates were wrapped in the implicit transactions -- batch SQL commands (INSERT/UPDATE/DELETE) are not.

At least, that's my reading.

So, in regard to the issue in your actual question, in setting up your OLEDB connection, you'd set the options for the Jet DBEngine for that connection according to what you were doing. It seems to me that the default Jet DBEngine settings are correct and shouldn't be altered -- you want to use implicit transactions for edits where you're walking through a recordset and updating one row at a time (outside of an explicit transaction). On the other hand, you can wrap the whole thing in a transaction and get the same result, so really, this only applies to cases where you're walking a recordset and updating and have not used an explicit transaction, and the default setting seems quite correct to me.

The other setting, UserCommitSync, seems to me to be something you'd definitely want to leave alone as well, as it seems to me to apply to the way Jet interacts with its temp database during an explicit transaction. Setting it to asynchronous would seem to me to be quite dangerous as you'd basically not know the state of the operation at the point that you committed the data.

这篇关于Jet OLEDB:事务提交模式和Jet OLEDB:用户提交同步有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！