What's the difference between Jet OLEDB:Transaction Commit Mode and Jet OLEDB:User Commit Sync?

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 OL开发者_开发技巧EDB: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.

You'd think that USERCOMMITSYNC=YES would be the option to commit synchronously. And that is the cause of the confusion.

I spent ages googling on this topic because I found that the behavior I was getting with old vb6 applications was not the same as I get in .net oledb/jet4

Now I really should back up what I'm going to say with a link to the actual page(s) I read but I can't find those pages now.

Anyway, I was browsing MSDN website and found a page that described a 'by design' error in Jet3 which transposed the functionality of USERCOMMITSYNC meaning a value of NO gets synchronous commit.

Therefore MS set the default to NO and we get synchronous commit by default. Exactly as described above by David Fenton. A behavior we've all come to accept.

But, the document then went on to explain that the behavior in oledb/Jet4 has been changed. Basically MS fixed their bug and now a setting of USERCOMMITSYNC=YES does what it says.

But did they change the default? I think not because now my explicit transactions are NOT committing synchronously in .Net applications using oledb/jet4.