Do Transactions guard against corruption?

[JString]

Hello.

I don't understand much about transactions and I would really like to know
if commit/rollback can help guard against corruption of the back end, and if
so what is the general procedure to make this happen.

Many thanks in advance.

 

[Michel Walsh]

With Jet? I would say that I don't see in what the transactions exposed to
us could help against corruption. At least, if the corruption comes from a
lost of connection, there is not mechanic I am aware of, which time out a
started but uncommitted transaction. On the other hand, if you lost the
connection after you started a transaction, it is unlikely that you will be
able to get back the same connection... to roll it back.... but why will you
roll it back if you can then continue to deal with the database and finally
commit the transaction?


The goal of a transaction is to make a complex operation appears as ONE
operation. As example, you have to append data in tableA, then erase it from
tableB. But observe that, in theory, the system may fall after you append in
tableA but before you erase it from tableB and that could 'invalidate' your
data (as if you send 1 M$ to clientA and fail to debit clientB, you will be
losing 1M$). So, will you start a transaction, append in tableA, delete in
tableB, and commit your transaction. If there is a failure while the
transaction is active, nothing outside the transaction will be influenced by
it. Note that technically, appending data in tableA and not being able to
delete it from tableB is not "corruption" of data, even if it creates a
state you don't want. Corruption is more like a wrong data type, an index
pointing to the wrong data, a unique constraint or a data referential
integrity rule that is not respected, or something like that.


Vanderghast, Access MVP

 

[Chris O''''Neill]

That was a *very* good explanation, Michel. Thank you for that!

I'm wondering if there are any general "guidelines" for when one should use
(or not use) transactions? Some folks (he said, pointing at himself) can get
carried away and overdo it if they don't have some guidelines to help them
decide when a transactional methodology is better (or worse) than just doing
it a regular way.

Fot instance, I'm creating a program that includes a subroutine that creates
a fiscal period table (fiscal period number, period start and end dates,
Yes/No field for whether the period is closed, etc.). The routine takes the
fiscal year start date supplied by the user (e.g. January 1st of any year)
and does 12 loops to create each row in the table. (Btw, the routine
accounts for fiscal years that don't start on January 1st and automatically
adjusts for leap years, which I thought was pretty cleaver of myself. (He
said proudly!))

Anyway, I'm thinking that maybe it might be a "good idea" to use a
transaction to create the table? That way, if something pukes during the
process, the table won't end up partially completed. Would *that* be an
example of an appropriate use for transactions? What are some other examples
of when (or when not) to use transactions?

Thanks, in advance, for your guidance and teaching...

Regards, Chris

 

[david]

Most corruption is caused by user errors. For example,
deleting the wrong record. Or updating the wrong record.
Or Adding the wrong record.

That happens, and your data is wrong: it is no longer
true. It is corrupt.

Access no longer guards against your computer failing
or being turned off while you are using it (as it did in Access
2.0), so the only kind of corruption transactions now
prevent is the kind of corruption that occurs when you
have a programming or data error that halts a complex
sequence of actions, leaving some of your tables updated
and some of your tables not updated. This is database
corruption: your data is corrupt.

A transactional file system like Novell Netware could do
the same thing at the file system level, and it could be
used (not with Access) to prevent your data becoming
corrupted when the network went down or your computer
turned off.

Access also sometimes has problems with the structure
of the database becoming corrupted. Transactions are
not used by Access to protect the structure of the database.
If transactions were used to protect the structure of the
database, they would protect the structure of the database
during complex sequences of actions, just like you can
use them to protect your data during complex sequences
of actions.

Access still does not use transactions to protect the
database structure even if you use transactions to
protect your data during complex sequences of actions.

When you said 'corruption' you probably meant damage
to the database structure. No, transactions do not
prevent that, because transactions are not used by
Access at that level.

If you only have simple database actions, then transactions
do not do anything.

It is good to keep in mind that corruption of your
data by a network error is much less likely than
corruption of your data by user error. Transactions
do not prevent user error.

(david)

 

[Michel Walsh]

If there is a need to add records in two different tables to accomplish one
goal, that can be a matter of using a transaction. If there is a need to add
12 records in one table, that may be better to use ONE insert query (which
will be almost like one atomic operation) but if that cannot be done and
looping need to be done, then, that may also become a good case to make a
transaction between the start and the end of the loop.


Transaction should be short. If there is a step that requires a user
intervention, inside the transaction, that is *probably* not very good (CAN
be too long, the user may have left for lunch) since tables and records may
become locked. With Jet, you don't get much control on what kind of
'isolation' (what other users can see about your transaction, even if they
can delete or append records on tables you just 'took a look at' , in your
transaction) you have, in comparison with MS SQL Server, but note that you
can indeed lock other users out of tables and records you touched: that is
an inconvenient about transaction, for these other users (or your own access
to the data though other means than through your transaction).


The user interface uses a transaction when you insert a bunch of data into a
table. Indeed, when you get the prompt about nnn records won't be added for
this or that reason, the prompt (user intervention ! ) is about accepting
the records that are not in 'error', or to abort the whole insertion thing.
This *is* a transaction that the interface started for you, and asked you
about committing it, or rolling it back! Inserting a bunch of data and
having to validate it is, indeed, a good example for a transaction. If you
have to 'touch' many tables (or the same table many times) and cannot do it
in one single query, that is typical example where a transaction can be
useful. Note that the operation system can also supply its own
transaction-thing when components outside a database may be involved (bank
transfer of money, as example), but that is, here, out of scope, even if it
is interesting to know that such thing is available.


Vanderghast, Access MVP

 

[Michel Walsh]

It seems you put in the same bag very different concepts such as data
integrity and database corruption, mainly when you speak of 'user error'.

If the user enter 23 when what should be entered is 32, that is a mistake.
That is a user error and indeed, no transaction could help. A cup of coffee,
having a validation number (CRC or otherwise) to be entered, having two
people entering the same data are all techniques that may help, but
transaction, no.

If the user fails to enter required data, or enter a not existing reference,
that should be covered and trapped by table design (not null) or by data
integrity rule, etc.

Transactions have NEVER been designed as safeguard for those previous kind
of 'user errors' in mind.

Sure, go immediately to the absurd, if its hard disk fails, then you are
likely to have to relay on having a RAID system, NOT on transaction, to be
able to recover from these errors.

You can come with cases were transactions are useless, indeed.

BUT

the table structures are protected by lock, when you modify them, and
internally by a transaction (never got the error message that the
modification(s) could not be written because some data won't match a new
constraint you just added ?). But you won't use transaction "to protect
table structure" when no-one is modifying them, you use a back-up (or
scripts, ... or RAID).


Vanderghast, Access MVP

 

[JString]

I think I probably should have made myself a little more clear about what I
meant by 'corruption'. Obviously corrupt data is bad data that Access cannot
handle. However, it's my understanding that Access will mark a database as
being 'suspect' if a connection is terminated prematurely, when a user is
manipulating data, even if there hasn't been an actual corruption of that
data. The end result is that all users will be locked out of the database
until someone recovers it.

So I definately get what you're saying about transactions having no affect
on data integrity, BUT, can it guard against a database being placed in a
'suspect' state? It seems to me that if a transaction is never committed (or
even rolled back) because of a system crash or whatever, Access shouldn't do
this.

 

[Michel Walsh]

Since nothing will terminate the transaction, some tables/records may be
locked forever. You *may* be able to recover from these locks by deleting
the ldb file, but I won't bet much that you will get your data 'uncorrupted'
after having done that, in every cases.


Vanderghast, Access MVP

 

[John W. Vinson]

The routine takes the
fiscal year start date supplied by the user (e.g. January 1st of any year)
and does 12 loops to create each row in the table. (Btw, the routine
accounts for fiscal years that don't start on January 1st and automatically
adjusts for leap years, which I thought was pretty cleaver of myself. (He
said proudly!))

I've done something similar using an auxiliary Num table with integer values
0-10000 or so, and the DateAdd function (which handles leapyears with no
additional effort):

INSERT INTO table
SELECT DateAdd("m", [N], [Enter start date of FY:])
FROM Num
WHERE N < 12;

No code, no loops, and no transactions needed.

 

[Tony Toews [MVP]]

I don't understand much about transactions and I would really like to know
if commit/rollback can help guard against corruption of the back end, and if
so what is the general procedure to make this happen.

If anything using transactions would increase the chance of
corruptions as Access/Jet would be flagging the database as being
updated for microseconds/milliseconds longer than without using
transactions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[david]

In Access version 2.0, it was common for databases to be left
in an indeterminate state, because databases idled in an
indeterminate state. That was in 1993.

This is no longer a problem. Access 95, 97, 2000,2002,2003,
2007 don't work that way. It is no longer common to find that
a database can't be opened because it was disconnected
without being closed correctly.

In Access 2000+, the LDB file is automatically deleted when
the last user disconnects. This means that any hanging locks
are deleted.

In Windows, file locks are automatically deleted sometime
after the user disconnects. This means that disconnected
users are automatically disconnected from MDB and LDB
files, allowing the LDB file to be automatically deleted,
allowing any hanging locks to be automatically deleted.

Have you ever had a database in a 'suspect' state?
Have you ever had hanging locks?

(david)

 

[Chris O''''Neill]

Hmmmm..... This is all very interesting and informative, but I'm beginning
to hear more "negatives" about using transactions in Access than positives.
Me thinks one should be very selective in deciding when to use a transaction.

Regards, Chris

 

[david]

Transactions were used in Access 2.0 for performance reasons.

You can still use transactions in some places to prevent database bloating.

Apart from that, you should only use transactions when you
know that you need them.

DAO Transactions can't be used with SQL Server any more,
so even when you know that you need them, you should
think twice.

(david)

 

[JString]

Yes, the company that I work for is having a few problems with the back end
being placed in a locked state. After restoring it, I've noticed that there
are a few people who's usernames have not been removed from the user table
which makes me think that they're just powering off their workstations
instead of correctly shutting down, and I've read that that is known to place
databases into suspect states. We use Access 2003 though so if that is
really the problem then is it possible that the issue was never really fixed?

 

[Tony Toews [MVP]]

Yes, the company that I work for is having a few problems with the back end
being placed in a locked state. After restoring it, I've noticed that there
are a few people who's usernames have not been removed from the user table
which makes me think that they're just powering off their workstations
instead of correctly shutting down, and I've read that that is known to place
databases into suspect states. We use Access 2003 though so if that is
really the problem then is it possible that the issue was never really fixed?

But just viewing the contents of the LDB file can be misleading.

Opening the .ldb file using notepad will show you both who's currently
in the database and some of the workstations which were in the
database. When a person exits Access their workstation name and
Access login id, Admin unless you are using Access security, are left
in a "slot" or record in the ldb file. This slot or record may get
overwritten the next time someone enters the MDB depending on what
slot or record is available previous to it in the ldb file.

Determining the workstation which caused the Microsoft Access MDB
corruption
http://www.granite.ab.ca/access/corruption/workstation.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[david]

The particular issue which led to all the publicity was fixed. It left the
database in a state which needed a 'repair' operation, but had no
real damage.

In your case, when you say "a locked state", do you mean that you
get a message telling you that your database is locked and you can't
use it? That might indicate that you need to patch Windows server
and workstations up to the current level, or it might indicate that
some user has opened or linked the BE in exclusive mode.

Or do you mean that when you try to delete your database, you
get a message telling you that you can't delete? And if you use
the server tools to disconnect all users from the file, you can delete?
That would indicate network failures, workstation crashes, or
users turning off their computers without shutting down.

After you have unlocked the database, is it corrupted? How can
you tell?

(david)

 

[JString]

The last time it happened, it was one of the main tables that was locked, not
the entire back end. After running a repair operation, I noticed that there
was one record that had its data corrupted - all of its field data was filled
with pound symbols.

The user table I'm referring to is an actual table in the back end. A
procedure in the front end adds and deletes CurrentUser data to that table
when the database is opened and closed. Since that portion of the back end
was not locked and the mdb could still be opened, I know that there were two
connections that closed incorrectly, and one of those users is someone I know
who is't very pc savvy.

 

[David W. Fenton]

If anything using transactions would increase the chance of
corruptions as Access/Jet would be flagging the database as being
updated for microseconds/milliseconds longer than without using
transactions.

That would be true for the "binary file level" of corruption.

For the level "logical consistency of data between tables"
transactions can improve things by making sure that inconsistencies
are not introduced.

 

[JString]

After reading all these replies I think I've gathered enough to see that I'm
making a big assumption about how transactions actually work. At first I was
thinking that the data that is wrapped in a transaction resides only in
memory until it is committed, but it seems like what you all are saying is
that the data is actually written into the DB file and resides there until it
is rolled back. Am I right about that?

 

[JString]

Having said that I wonder if it would be worthwhile to use temporary
recordsets for this purpose while users are manipulating/entering data.