Re. transactions, version 4.0
David’s posting raises several issues which, while they aren’t directly germane to the original direction of the article, are fascinating in their own right (as long as you are interested in databases!) and therefore worth following up.
For information, before we start, the following list shows the appropriate Jet engine for each version of Access:
Access 1.0 Jet 1.0
Access 1.1 Jet 1.1
Access 2.0 Jet 2.0
Access 2.0 Jet 2.5 (Service Pack)
Access 7.0 Jet 3.0
Access 97 Jet 3.5
Access 2000 Jet 4.0
Access 2003 Jet 4.0
The first point for discussion is when Jet started to support transactions. Was it Jet 4.0 as I stated, or 1.1 as David says? Well, it depends on what you consider to be a transaction.
I have a set of original Access 1.0 manuals and on page 117-121 of the ‘Introduction to Programming’ manual we find a description of how to perform transactions. In the ‘Language Reference’ manual we find the following statements supported:
So we can easily argue that transactions have been supported since version 1.0.
But, of course, it isn’t as simple as that (you must have known it couldn’t be….). For a start this is non-standard syntax. Neither BeginTrans no CommitTrans are part of the ANSII SQL standard, although Rollback is. In addition, the SQL standard assumes that transactions start automatically when you issue an INSERT, UPDATE, SELELCT or DELETE statement. Access doesn’t do this, which is why it needs a BeginTrans statement. Having said that, the SQL standard does also support the START TRANSACTION command which is similar to BeginTrans. Then there is the little matter of SAVEPOINT which is also part of the standard, but isn’t supported in Jet 1.0 (or, indeed, in 4.0). And the standard also supports a considerable set of options for COMMIT and ROLLBACK that are not supported by Jet 1.0.
So, does Jet 1.0 support transactions? ‘Yes’ in the sense that you can wrap up several SQL statements into one operation that either succeeds completely or fails completely; ‘No’ in the sense that it does not conform to the definition of transaction as defined in the SQL standard. So ultimately it depends on the definition you apply.
As Jet progressed, while support for transactions improved, it has never reached the stage where the entire standard is fully supported.
In the article I chose 4.0 as the first version that provided enough support for transactions to be worthy of the name. I had two main reasons for this choice. Firstly 4.0 was the first version where Microsoft elected to change to (more or less) the correct syntax for transactions. Secondly, and more importantly, it was at this point that Microsoft itself appeared to concede that support had been poor up until that point.
I quote from a Microsoft document http://support.microsoft.com/kb/275561 called “Description of the new features that are included in Microsoft Jet 4.0”.
“Microsoft Jet SQL now supports invocation and termination (committing or rolling back) of transactions.”
So, a ‘new feature’ of Jet 4.0 is the support for invocation and terminations of transactions. The clear implication being that, prior to 4.0 it did not. I was absolutely certain that if I referred to the support of transactions any earlier than 4.0, a multitude of Reg Developer readers would have pulled me up and pointed out that even Microsoft didn’t consider that transactions were supported prior to 4.0……
Incidentally, in this document Microsoft still acknowledges that support is by no means complete. The very next line reads:
“Note that while ANSI SQL specifies that a new transaction is started automatically following a COMMIT or ROLLBACK, Microsoft Jet does not follow this model. Thus, an additional transaction verb is defined to explicitly start transactions, because Microsoft Jet does not automatically start transactions.”
So, does all of this mean that I am obliquely saying that David is wrong? Absolutely not. When writing the article I thought carefully about it and, in truth, I nearly opted for 1.0 instead of 4.0. If I hadn’t remembered the reference in the 4.0 spec., I probably would have. Exactly where any of us think that Jet starts to support transactions really is simply a matter of opinion.
A log file contains a log of all the transactions (in the standard sense, not just in the Access sense) that take place against a database. Log files are typically not stored on the database server itself but on another machine, a fact which turns out to be very useful. Suppose that we create a backup of the database at midnight. The database runs until 3:00PM then it crashes and burns in a spectacular fashion. The server is destroyed. But we are not downhearted. We restore the backup to a new server and then essentially run the transactions recorded in the log file against the database. Since every transaction is recorded in the log file, we can roll the database forward to any point in time; yea even unto 3:00PM. Wonderful. This is how databases ensure that transactions are durable (the D part of ACID); they survive even if the operational database is lost.
Access logs enough of a transaction to be able to roll it back, but does not provide durability. So, David and I agree totally that Access doesn’t guarantee its transactions pass the ACID (Atomicity, Consistency, Isolation, Durability) test.