Access Transaction Rollback problem

[Colin]

There seems to be a serious problem with transaction rollbacks using DAO 3.6
or 3.51 on Access databases. I have written a minimal VB6 application and
would be grateful if anyone could confirm the problem and, if possible, a fix.
The problem is that the recordset is "losing" records after the transaction
rollback when nothing has been done to change the recordset.

To test this:

1. Create a new Access database with one table (Table1) in it. The table
only needs one (Autonumber) column in it called RecordID. Populate the table
with at least 2 records.

2. Create a new VB6 project in the same folder as the table. Create a
default form and put one command button on it called cmdGo

3. Copy the code below into the form and run the project...you should end up
with the following in the debug window: "open, ID 1 found, begintrans, ID 2
found, rollback, ID 2 not found, requery, ID 2 found, close".

Note: I am using the XP SP2 version of DAO360.dll. I have also tested this
application with DAO3.51 and get the same results. The database is Access 97
but I get the same results with an Access 2000 database.

If you look at the code and take out all the debug print stuff you'll see it
is incredibly simple.

---------------------------------------------------------------------------------------
Option Explicit

Dim db As DAO.Database
Dim wrk As DAO.Workspace

Private Sub Form_Load()

'Open default workspace and the database...The database has one table
called Table1.
'This table has one field called RecordID and the field is an
AutoNumber. There are
'2 records in the table.

Set wrk = Workspaces(0)
Set db = Workspaces(0).OpenDatabase(App.Path & "\" & "Test.mdb", False,
False)

End Sub

Private Sub cmdGo_Click()

Dim rst As DAO.Recordset
Dim strMessage As String
Dim lngID As Long

'Open a simple recordset
Set rst = db.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset,
dbInconsistent, dbOptimistic): strMessage = "open, "

'Find the first record - OK
lngID = 1
rst.FindFirst "RecordID=" & lngID: strMessage = strMessage & "ID " &
lngID & IIf(rst.NoMatch, " not found", " found") & ", "

'Begin a transaction and find the second record - OK
wrk.BeginTrans: strMessage = strMessage & "begintrans, "
lngID = 2
rst.FindFirst "RecordID=" & lngID: strMessage = strMessage & "ID " &
lngID & IIf(rst.NoMatch, " not found", " found") & ", "

'Rolback the transaction
wrk.Rollback: strMessage = strMessage & "rollback, "

'Look for second record NOT OK...requery, find second record OK
rst.FindFirst "RecordID=" & lngID: strMessage = strMessage & "ID " &
lngID & IIf(rst.NoMatch, " not found", " found") & ", "
rst.Requery: strMessage = strMessage & "requery, "
rst.FindFirst "RecordID=" & lngID: strMessage = strMessage & "ID " &
lngID & IIf(rst.NoMatch, " not found", " found") & ", "

'Tidy up
rst.Close: strMessage = strMessage & " close"

'Print what we found
Debug.Print strMessage

End Sub

 

[Allen Browne]

Colin, I think you will find that all these problems disappear if you begin
the transaction *before* you open the recordset.

The recordset that is already opened before you BeginTrans is the most
obvious problem here.

 

[Colin]

Hi Allen,

Thanks for your reply and I'm sure you are right...if we do everything iin
the transaction then all would be fine.

However, in our real application this is not possible. We are usually
working through a (or sometimes several) recordset using either .MoveNext or
..FindFirst then diving deep down into business rules logic where other
recordsets are edited within transactions (a) to ensure consistancy and (b)
so that the changes can be rolled back if the business logic at any stage
says that the changes are not allowed.

What we have found is that the main recordset that we are working our way
through is getting "damaged" by the rollback so that we are suddenly at .EOF
when we shouldn't be or don't find something we know should be in the
recordset.

Although I know that opening a recordset outside a transaction and closing
it within one (or vice-versa) is a recipe for disaster, doing what we are
doing is exactly what tranactions should be capable of - I think there is a
serious and fundamental bug in DAO.

Can you confirm that yoou also see this behaviour from the example
application?

 

[Allen Browne]

Colin, I did not follow through with your example, as I don't expect Access
to work the way you are attempting it.

From my perspective, you have a faulty algorithm, not a bug in DAO.

 

[Colin]

I don't understand that Allen...almost every example I can find showing how
to use transactions go like this
1. Open a recordset
2. begin transaction
3. do things to the recordset
4. Depending on how thiings went either commit or rollback
5. close the recordset (and destroy it)

(a typical exaple is at
http://msdn2.microsoft.com/en-us/library/bb243806.aspx but there are hundreds
that are much the same on the Web)

That's what I'm doing (I think) except that I'd like to expect the recordset
to be exactly as it was before I started the transaction if I do a rollback,
and it isn't.

Can you point out my logic error please?

 

[Allen Browne]

Perhaps someone else has a different view to contribute for you.

 

[Colin]

OK Allen - I have to accept you probably want to duck out of this one. I just
wish someone would confirm the problem or show me a fix.

I have now taken the Northwind example from
http://msdn2.microsoft.com/en-us/library/bb243806.aspx and run it - if the
recordset is formed from the table asa in the example all seems OK. If it is
formed by using "SELECT * FROM Employees"
then it fails in just the same way as my earlier example test program - the
recordset looses all but the first record. This cannot be correct behaviour!!!

 

[David W. Fenton]

I have now taken the Northwind example from
http://msdn2.microsoft.com/en-us/library/bb243806.aspx and run it
- if the recordset is formed from the table asa in the example all
seems OK. If it is formed by using "SELECT * FROM Employees"
then it fails in just the same way as my earlier example test
program - the recordset looses all but the first record. This
cannot be correct behaviour!!!

Is there a reason that you need to use the default workspace,
instead of opening a new one for your transaction?

 

[Colin]

Thanks David...it may be possible to do that as a workround for the problem.
I may be able to keep the main recordset(s) in the default workspace and open
all the others that might get rolled back in another workspace(s). I will
investigate...

 

[David W. Fenton]

it may be possible to do that as a workround for the problem.
I may be able to keep the main recordset(s) in the default
workspace and open all the others that might get rolled back in
another workspace(s). I will investigate...

Well, sure. A transaction applies to an entire workspace, so any
recordsets opened after the beginning of your transaction are going
to be within that transaction, unless you use a different workspace.

I've always made a point of using a new workspace for transactions.

 

[Colin]

Well, the problem is finally solved. I'll explain how below but first
consider the problem we were facing...I'll try to use a simple analogy.
Consider a recordset of customers where we want to update their account
details. If they are trying to purchase more goods than their credit limit
we'll want to roll back all orders for the batch of their orders that put
them over their limit. So, first, we form the recordset of all the customers
that have outstanding batches of orders. Then, as we loop through the set of
customers, we start a transaction for each new batch and process each order
in the batch...processing an order will entail starting further nested
transactions to ensure that the stock balance and stock re-order tables are
kept in sync etc. Assuming the whole batch can be processed without the
customer exceeding their credit limit the outermost transaction can be
committed. However, if the credit limit is exceeded at any point then the
outermost transaction must be rolled back (this will roll back the inner
transactions too because they were nested). Either way we then want to move
on to the next record in the cutomer/batch recordset.
It is at this point that DAO screws up because, if a rollback has been done,
the recordset (of type dbOpenDynaset) containing the customer/batch details
will lose records that DAO hasn't yet populated the recordset with so we end
up with a premature EOF in our outer loop and the remaining customer orders
never get processed.

Now, I don't care what Allen Browne thinks - this is *not* correct behaviour
and it *is* correct usage - there is no feasible alternative (although there
are workrounds) to using transactions in this way for this purpose. I still
maintain that, after a rollback everythinig should be *exactly* as it was
before the transaction was begun and it is *not*. Worse still I have never
seen anything anywhere that explains this wierd behaviour.

And David, using separate workspaces doesn't, in general solve the problem
although, in this case it could if we were not, in the real application,
trying to deal with reams of "legacy" spaghetti code.

Incidentally, ADO doesn't show this problem, possibly because it appears to
fully populate recordsets when they are opened - but it suffers from the
single database connection transaction problem - we are using multiple
databases!!

In our case virtually all recordset opens are done through a wrapper
procedure. If the recordset is "moved last" when opened it is fully populated
and there are then no problems. The application contains hundreds of
recordset opens (1564 at the last count!!) so being able to simply add one
..MoveLast and then a .MoveFirst (if the opened recordset had at least one
record) to the open recordset wrapper code was a quick and easy fix (albeit
with the potential of some performance hit although DAO was MUCH quicker than
ADO opening a 100,000 record recordset even with the .MoveLast, .MoveFirst
added to the DAO open).

 

[David W. Fenton]

And David, using separate workspaces doesn't, in general solve the
problem although, in this case it could if we were not, in the
real application, trying to deal with reams of "legacy" spaghetti
code.

It seems to me that using the default workspace for the list of
accounts with open balances, and a different workspace opened for
each account for the batch processing should have solved the
problem.

Incidentally, ADO doesn't show this problem, possibly because it
appears to fully populate recordsets when they are opened - but it
suffers from the single database connection transaction problem -
we are using multiple databases!!

Did you switch to ADO?

In our case virtually all recordset opens are done through a
wrapper procedure. If the recordset is "moved last" when opened it
is fully populated and there are then no problems. The application
contains hundreds of recordset opens (1564 at the last count!!) so
being able to simply add one .MoveLast and then a .MoveFirst (if
the opened recordset had at least one record) to the open
recordset wrapper code was a quick and easy fix (albeit with the
potential of some performance hit although DAO was MUCH quicker
than ADO opening a 100,000 record recordset even with the
.MoveLast, .MoveFirst added to the DAO open).

I'm unclear on what the solution was.

And did you try any of the other recordset types, like snapshot? I
think a snapshot is not available until it has reach EOF, but I
could be mistaken on that. And if you need to edit the recordsets
that were showing the problem, it wouldn't work, either.

But did you try the other recordset types?

 

[Colin]

Hi David

Thanks for taking the time to read my rantings...as you can tell I'm not
very pleased with the DAO problem! Hope the following answers the points you
raised.

1. Different workspaces...unfortunately not as in the real application all
the recordsets involved need to be edited on a customer by customer basis.
Difficult to explain in my simple analogy but after a week of analysing the
real application it would have involved huge amounts of work to make this
practical.

2. I tested ADO in a small test application. I considered switching to ADO
but we would have had to write a class for translating DAO methods that are
not in ADO and accessing all the recordsets through that class (e.g. no find
on multiple fields in ADO would have to be handled by the class)...but the
most worrying aspect of ADO was that in my tests it was performing much worse
than DAO and our real application is very UI dependent and relies on the user
seeing things happen quickly!

3. The solution was to always populate DAO recordsets fully by doing a
..MoveLast followed by a .MoveFirst immediately after opening the recordset.
This was easy to implement as (luckily) one of the original coders (1st June
1999) had decided to write a function for opening recordsets and virtually
all recordsets were opened using this function so we only needed to insert 2
lines of code (plus a bit more to check the recordset had at least two
records in it).

4. Tried snapshot...same problem after rollback and, as you say, not
updatable. Also tried dbOpenForwardOnly but, of course, .FindFirst etc isn't
supported for this type.