Access isn’t a relational database This is a great example of a myth/legend that is both true and false; it all depends on how you define relational. Ted Codd produced one of the first attempts to define exactly what the term relational Database Management System (DBMS) means. Since Dr. Edgar Codd is regarded as ‘the Father of the Relational Database’, most …
I one met a PR guy, big player, worked with Sybase and likes many years ago and he told me that it was actually him and some guy at Sybase that came up with RDBMS tag - it was the oly way that SYbase would make it into Gartners Magic Quadrant - or whatever they call that tripe these days.
Could be lying of course. But equally it might go some of the way to explain why nobody really knows what 'relational' means.
Actually, I think that one meaning of "relational" is rather well defined - by Codd, Date et al - and is to do with set theory.
The problem is that lots of people ignore this and come up with various less precise and less useful meanings - and, as you say, often for marketing purposes. Was it Ashton Tate that decided that "relational" referred to the fact that you could relate two different files open at the same time?
Access version 1.1 had transactions, not just Access (Jet) 4.0. I know that, not because I used 1.1, but because the slight differences with 2.0 were noted. Jet 4.0 introduced the ADO interface. ADO must be used instead of Jet transactions against Sql Server, but only because Jet 4.0 broke the existing interface. A misguided 'optimisation' puts different parts of the transaction onto different server connections when using the native ODBC connections.
As a matter of interest, the transaction file is technically a jet database file, which puts the same file size limit on transactions.
A more serious limitation is that Jet actions and transactions are not 'atomic' or 'durable' (and hence not ACID). Jet actions and transactions are not atomic because of various misguided optimisations introduced in Jet 3: they are not durable because the database primitive provided by the operating system was disconnected when Win32 disk caching was introduced.
(david)
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:
BeginTrans P50
CommitTrans P76
Rollback P406
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.
Log files.
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.
It's only a personal opinion, but I've always thought that recovery to the point of failure (ie, not taking the database back in time and not requiring manual resubmission of successfully completed transactions after failure) is an important business-oriented goal for a DBMS. So, while ACID isn't actually required for basic transaction support, I think that not offering it in a DBMS claiming to support transactions is potentially dangerous.
However, with loosly coupled composite services these days, perhaps recovery to the point of failyre is moot and we must now always assume a need for compensating transactions to undo the consequences of failure...
Well, “SQL now supports”. Somehow we've morphed from "Access supports transactions" to "SQL supports transactions", but I'll leave it at that.
A historical note: All through the 90's, whenever anyone bitched that 'Access doesn't use standard SQL' you could bet that they where refering to the Access/Jet (ANSI 92) JOIN syntax. Only as other products also became standards complient did minor features (such as the date delimiter) start to become the focus of complaints.
The more interesting point is that Access 2/Jet 2.5, running on Windows 3.11/DOS 6, was ACID (when run in writeback mode).
INSERT, UPDATE, SELELCT or DELETE statements were Atomic, and disk writes could be flushed immediately to disk.
Unfortunately, the same is not true with Jet 3.5/4.0, running on Windows 95+. You can't run Jet transactions against Sql Server, and Jet transactions against an MDB database are not Atomic (even when using transactions) and not Durable (even with dao.dbForceOSFlush). Only ADO transactions are true transactions, and only when using a Server database. Access using ADO against a Server database is a standard configuration, but it does not use Jet at all: Jet transactions are broken.
Changing the subject slightly, from 'Relational Database' to 'Relational Database Management System' it's easy to see that with Access 2.0, some one had made an honest attempt to build a RDMS (A Management System contained in and implemented as a Relational Database).
Running inside the Access environment, you got all of Access BASIC (including transactions) as SQL language extensions, (yes, you could run a transaction from an SQL statement), or alternatively you got all of SQL as a language extension to Access BASIC: who ever said that the relational database management language had to be SQL?
Looking at it from VB3, the database language extensions (including transactions) were 'outside' the database, which violates the fundamental precept of a 'Relational Database Management System', but that was a matter of style rather than substance: the transactions were database operations implemented by the database system, using a database management language and interface, not something the programmer had any direct control of.
When Access 2.0 came out, there really was no common usage for Data Definition Language (DDL) or Transactions. Is the new standard DDL better than using Access BASIC? Everyone agrees that it is. Still, I think that the original Access development team had a d'd good try at making a RDMS, and I think their product stacked up well against the competition at the time.
Much as I love Access and I think it is a relational database, it has never passed the ACID test.
The DURABLE bit in the ACID definition means that once a transaction is committed it must survive even in case of hardware or software failures. Access doesn't have a mechanism for ensuring durability, like mirroring the database or transaction logs that record every transaction and that can be written rapidly to another media on another machine.
Hang on a minute, the irony flag has just gone high. The original article is about the myth that Access is not relational. In discussing that idea, it makes the point that database people argue interminably about detail and never agree because they don’t listen to each other and never change their minds. It even illustrates the point by having two mythical characters, in a pub, argue. One of those characters says that Access doesn’t support transactions, the other says that it does, but only in Jet version 4.0. This is done, as I understand it, simply to illustrate that database people are argumentative. And what happens? A whole load of database people start arguing, not about whether Access is relational or not, but about which version of Jet supported transactions.
Actually, I’m not sure if this is irony or recursion, but it doesn’t matter; someone is sure to put me right.
Access/Jet/MDB is a 'file system' database system. That is, the database primiatives are provided by the OS 'file system' subsystem. It is durable to the extent that the OS subsystem is durable. In the normal sense of the word, your file system is durable. You don't expect your documents and transactions to be lost after they are saved. Historically, Access also achieved durability by reason of it's extreme portability. Back up your transactions overnight (using other features of your file subsystem), and a naive user could fire up the database system in 10 minutes (including the time to find the backup). Try that with the transaction file and SQL Server 6.5.
My guess is that the authors of the two comments “The ACID test” and “durable” are using different definitions of the word ‘durable’. I agree wholeheartedly with the general definition of the word as discussed in the comment “durable”. Durable means, it endures. If most people update a database in Access, they can reasonably expect that database to be available later that day or even the next. So, in that sense, Access transactions are durable.
However we can then ask “How durable is durable? How long is it supposed to endure? What level of failure, both hardware and software, should a transaction be able to withstand in order to be considered durable in the context of the ACID definition?” For this we need to turn to the person who spent decades working on transaction theory and ACID properties, Jim Gray.
www.regdeveloper.co.uk/2006/05/30/jim_gray/
A useful book here is:
Jim Gray, Andreas Reuter: Transaction Processing: Concepts and Techniques. Morgan Kaufmann 1993, ISBN 1-55860-190-2
http://books.theregister.co.uk/catalog/browse.asp?isbn=1558601902
This is the classic work on transaction theory. In it Gray discusses what is meant by durable in an ACID context. His description isn’t a general one based around the fact that, on any given day, a single machine is unlikely to fail. He is talking about a much more rigorous definition; much closer to that given in the comment headed “The ACID test”.
Of course, it is still a matter for debate. If we ship the log files off a server every 30 seconds and the machine crashes and burns, we could lose up to 29.99999 seconds worth of transactions. Does that meet the criteria for durability? Is once every 5 seconds enough? What if we mirror the entire database to another machine in the same room? The transactions can now survive one machine failing, but what if the room burns down? If we simultaneously mirror to ten different off-site machines; are the transactions now durable?
Ultimately I think that Access transactions are perfectly durable in the normal use of the term, but Access doesn’t have the additional mechanisms necessary to provide durability as the term is typically used in the ACID sense.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
