Transaction isolation in JET?

Question

MSDN describes the JET transaction isolation for its OLEDB provider as follows:

Jet supports five levels of nesting in transactions. The only supported mode for transactions is Read Committed. Setting lesser levels of transactional separation implies Read Committed. Setting higher levels will cause StartTransaction to fail.

Jet supports only single-phase commit.

MSDN describes Read Committed as follows:

Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

---

My questions are:

1. What is single-phase commit? What consequence does this have for transactions and isolation?

2. Would the Read Committed isolation level as described above be suitable for my requirements here?

3. What is the best way to achieve a Serializable transaction isolation using Jet?

Answer 1

By question number:

1. Single-phase commit is used where all of your data is in one database -- the activity of the transaction is committed atomically and you're done. If you have a logical transaction which needs to be spread across multiple storage engines (like a relational database for metadata and some sort of document store for a big blob) you can use a transaction manager to coordinate the activities so that the work is persisted in both or neither, if both products support two phase commit. They are just telling you that they don't support two-phase commit, so the product is not suitable for distributed transactions.

2. Yes, if you check the condition in the UPDATE statement itself; otherwise you might have problems.

3. They seem to be suggesting that you can't.

As an aside, I worked for decades as a consultant in quite a variety of environments. More than once I was engaged to migrate people off of Jet because of performance problems. In one case a simple "star" type query was running for two minutes because it was joining on the client rather than letting the database do it. As a direct query against the database it was sub-second. In another case there was a report which took 72 hours to run through Jet, which took 2 minutes when run directly against the database. If it generally works OK for you, you might be able to deal with such situations by using stored procedures where Jet is causing performance pain.