Do Transactions guard against corruption?

[Michel Walsh]

Recordsets supported by another database without continuous link (as a
linked table, for example) to yours? You then have a replication problem at
the moment you decide to finally merge the records. What if the autonumber
value you used has been used and inserted by another user? what if the
record,on append, will find that another user has already append the same
value of a UNIQUE constrained field,? etc.

Recordset on the ... same tables same database? at best, they are just
another 'user', in extra.


Maybe there is something I missed, though.

 

[JString]

The point is to use memory and avoid writing to disk for as long as possible.
I suppose you can't use a temporary recordset unless you append it though,
which would sort of defeat the purpose. But you could extract an array from
a recordsource containing only one record, modify it and then run an update.

 

[Tony Toews [MVP]]

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?

Yes, I would think so. I don't know so but that would be my
understanding.

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/

 

[Tony Toews [MVP]]

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

But then you also get the lock problem. To add to Michel's
explanation. What if one user updates an address and another user
updates the same address? Right now Access does a decent job in
informing the second user their data is out of date. Now you have to
take over that functionality. Lots of extra work.

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 W. Fenton]

Yes, I would think so. I don't know so but that would be my
understanding.

That's not my understanding at all. My understanding is that until
the transaction is commited, the original MDB file is left untouched
(though locked appropriately), and the new data is in a temp file in
the %TEMP% directory of your workstation. The COMMIT folds the temp
file edits into the real MDB file.

Because of this, I really don't believe that uncommitted
transactions can cause corruption or bloat.

The purpose of transactions is to make sure that operations that are
interdependent but can't be executed in a single SQL statement get
completed. That is all.

 

[JString]

That's not my understanding at all. My understanding is that until
the transaction is commited, the original MDB file is left untouched
(though locked appropriately), and the new data is in a temp file in
the %TEMP% directory of your workstation. The COMMIT folds the temp
file edits into the real MDB file.

Because of this, I really don't believe that uncommitted
transactions can cause corruption or bloat.

The purpose of transactions is to make sure that operations that are
interdependent but can't be executed in a single SQL statement get
completed. That is all.

Ok. Then I'm guessing that the update from the temp db to the target db is
just as prone to corruption as a normal update.

But in regards to my original problem, isn't there at least some way to
programatically protect against corruption if it's really needed?

 

[Michel Walsh]

I assumed you meant VBA-standard recordsets, NOT disconnected recordsets.


You have to write the modified data anyhow, don't you (corruption don't
occur with a read)? recordset adds extra overhead, by comparison to do it
directly with an atomic SQL statement.

For multiple records being modified, looping over a recordset, by itself,
does not offer the same 'atomicity' of the operation than a transaction
would: in fact, it may be preferable to start an explicit transaction before
looping over the recordset loop, if it is important that all the
modifications seem to occur "at once" for anyone else. If the transaction is
not required, then it still *may* be preferable to loop with direct SQL
update statements rather than opening a 'temporary' recordset *just* to do
the updates. It is another matter if you have already open the recordset, to
support a form. But you mentioned 'temporary' recordset, so I imagine you
don't plan to use it for anything else than to append or modify data.



Vanderghast, Access MVP

 

[Michel Walsh]

I am not sure about how Jet does it, but I assume it does real writing in
the db (with locks) and use its internal log for a roll back, as MS SQL
Server do: "Write-ahead logging and automatic rollback and rollforward of
transactions during the recovery phase of SQL Server startup ensure
durability" (typo, if any, are mine, taken from: Inside Microsoft SQL Server
2000, page 43) and so, even "If the powers fails immediately after the
acknowledge (...) the transaction is guaranteed to exist in the database".
With a 'merge' from an external db, as you expose, that won't be possible,
unless you have a mechanism to handle the case of failure 'while merging',
and thus, be able to undo it the resulting 'mess' ... without having
transaction at your disposition.



Vanderghast, Access MVP

 

[JString]

I found a bit of useful info in the VBA help files on what you were just
saying:

"In a Microsoft Jet workspace, you can include the dbFlushOSCacheWrites
constant with CommitTrans, This forces the database engine to immediately
flush all updates to disk, instead of caching them temporarily. Without using
this option, a user could get control back immediately after the application
program calls CommitTrans, turn the computer off, and not have the data
written to disk. While using this option may affect your application’s
performance, it is useful in situations where the computer could be shut off
before cached updates are saved to disk."

 

[Tony Toews [MVP]]

That's not my understanding at all. My understanding is that until
the transaction is commited, the original MDB file is left untouched
(though locked appropriately), and the new data is in a temp file in
the %TEMP% directory of your workstation. The COMMIT folds the temp
file edits into the real MDB file.

Because of this, I really don't believe that uncommitted
transactions can cause corruption or bloat.

The purpose of transactions is to make sure that operations that are
interdependent but can't be executed in a single SQL statement get
completed. That is all.

Sure, that could make sense too.

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/

 

[Tony Toews [MVP]]

Ok. Then I'm guessing that the update from the temp db to the target db is
just as prone to corruption as a normal update.

Correct. Again, I'd say slightly more prone given that it will take
longer to perform.

But in regards to my original problem, isn't there at least some way to
programatically protect against corruption if it's really needed?

No.

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/

 

[RD]

In Access 2000+, the LDB file is automatically deleted when

the last user disconnects.

Ummm ... not really. I'm the main Access support around here and I
see old ldb's fairly often.

 

[Michel Walsh]

Easy repro:

Open and mdb.
Open the task manager, kill the Access mdb app.
The ldb file is present.


Vanderghast, Access MVP

 

[David W. Fenton]

Ok. Then I'm guessing that the update from the temp db to the
target db is just as prone to corruption as a normal update.

???

It would happen far, far faster, which means less opportunity for
problems. And the relationship between temp database and real
database is going to be carefully managed, with lots of error
checking to make sure that everything completes successfully. If the
update didn't succeed, it would mean that your COMMIT failed, and
the transaction would leave the original file as it was.

But in regards to my original problem, isn't there at least some
way to programatically protect against corruption if it's really
needed?

I don't know that there is. Why would you be worrying about such a
thing?

 

[David W. Fenton]

Correct. Again, I'd say slightly more prone given that it will
take longer to perform.

Longer to perform than *what*?

 

[David W. Fenton]

I am not sure about how Jet does it, but I assume it does real
writing in the db (with locks) and use its internal log for a
roll back,

What internal log? There is no such thing in Jet.

as MS SQL
Server do: "Write-ahead logging and automatic rollback and
rollforward of transactions during the recovery phase of SQL
Server startup ensure durability" (typo, if any, are mine, taken
from: Inside Microsoft SQL Server 2000, page 43)

Jet doesn't work anything like SQL Server because there is no
centralized process managing writes to a multi-user MDB. It's all
done through direct operations on the file, managed by the locks.

and so, even "If the powers fails immediately after the
acknowledge (...) the transaction is guaranteed to exist in the
database". With a 'merge' from an external db, as you expose, that
won't be possible, unless you have a mechanism to handle the case
of failure 'while merging', and thus, be able to undo it the
resulting 'mess' ... without having transaction at your
disposition.

The way SQL Server works has ZILCH to do with the way Jet works.

 

[David W. Fenton]

....More precisely, in Jet 4 and later...

Ummm ... not really. I'm the main Access support around here and
I see old ldb's fairly often.

It is deleted if it *can* be. You can run a database in a folder
where the users lack delete permissions and the LDB file will not be
deleted when the last user exits.

Most leftover LDBs, on the other hand, are likely to come from
errors in exiting Access.

 

[David W. Fenton]

Easy repro:

Open and mdb.
Open the task manager, kill the Access mdb app.
The ldb file is present.

And if the LDBs are getting orphaned regularly, then you have major
problems with your operating environment that could lead to
corruption/data loss.

 

[Tony Toews [MVP]]

Longer to perform than *what*?

Using transactions will take longer than not using transactions.
Thus transaction would be slightly more prone to corruption.

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/

 

[Michel Walsh]

Jet maintains an internal log, at least, John L. Viescas, among other, made
that claim long time ago (cannot find a Google reference, though), but that
log file is not exposed through any public API.

It is not centralized? well, it is ONE physical file, to start with, and it
is a good start, isn't it? sure there is no SERVICE, but it is
'centralized', in the end.

Vanderghast, Access MVP