Good practice on transactions (1 Viewer)

Bob · Feb 4, 2010

Hi there

I have a series of statements that needs to be carried out as a single unit (transaction). (If one fails, all should roll back)

I intend to use ADO and command objects on the mdb backend.

What is good practice?

As I build and execute the various appends and updates I need to do some SELECTs in between and I don't know if this will give me trouble. Should I open another connection from which I do all the reads and only execute the database changes on the other connection object, or can I just go ahead and use the same connection for reads and writes?

I will need some of the autonumbers created along the way (Select @@Identity) , which is why I can't wait executing the database changes until all reads are done.

How about locks? Could that be a problem?

Suggestions to a nice approach greatly appreciated.

Banana · Feb 4, 2010

Just so you are aware, either ADO or DAO are equally capable of doing transactions. I would think DAO would be better suited for the job since the backend is mdb. ADO is a great object model when I need something natively from a ODBC backend, to be sure.

I would think you should be able to do all SELECTs you need to do in the same transactions without any problem, including getting the identity. I would think that opening another connection would create more problems than it would solve. Are you running into any problems because of this?

Wrapping this in a transaction should also protect against dirty reads so you don't have to worry about others seeing the SELECTs outside the transactions.

Bob · Feb 4, 2010

ADO only chosen in case I want to move it to SQL server (perhaps MSDE). But then I would probably use a stored procedure for this, so...

I haven't run into problems (yet!) but I have a vague memory that one should be careful, so I was just looking for some general guidelines. Thanks.

From a programming point of view I don't really mind which to use but why would DAO be better suited for an mdb?

SOS · Feb 4, 2010

Bob said:
...(perhaps MSDE).

Just an FYI -

MSDE is no longer the name for the desktop version of SQL Server. It was in SQL Server 2000 but now it is SQL Server Express 2005 or SQL Server Express 2008 (and it is better to use one of those as the storage space is double what it used to be as well as other enhancements).

Banana · Feb 4, 2010

Fair enough. Likewise, I would do it in stored procedure rather than using ADO (or even DAO's transaction model).

I think the most problem with transactions come from trying to binding a form to the recordsets in middle of transactions. It could be done (Leigh Purvis has a nice demo), this is where things really get complicated, but if you're doing it entirely in code, then it's pretty easy-squeezy.

DAO is basically JET/ACE (the database engine driving Access which is just a UI on top of this)'s native interface, and is actively developed & enhanced while ADO is pretty much dead (the world has moved onto ADO.NET which the only common thing it has with ADO is the first three letters). Generally, DAO will outperform ADO against MDB/ACCDB backend as there's less layers to pass through compared to ADO. Now, if we're talking about different backend, then it's ultimately a race for capability rather than performance.

HTH.

LPurvis · Feb 4, 2010

Of course the irony is that Jet supports Transactions natively - but only the OLEDB provider (i.e. ADO) exposes the syntax. ;-)

I just wanted to draw attention to one line:
>> "I will need some of the autonumbers created along the way (Select @@Identity) , which is why I can't wait executing the database changes until all reads are done."

Are you implying that updates performed by other users at the same time would affect the Identity value returned to you?
That's not something you need to worry about. @@Identity is connection specific. Nothing they do will affect your code in that respect.
However you'll still need a transaction anyway to conform to the consistency requirement of your updates.

Cheers.

Banana · Feb 4, 2010

LPurvis said:
Of course the irony is that Jet supports Transactions natively - but only the OLEDB provider (i.e. ADO) exposes the syntax. ;-)

Hey! Thought you would chime in.

Hm, I'm sure you know that DAO also has the syntax as well though it's a bit odd in that it's within Workspace object, not Database object because JET also supports distributed transactions (at least it tries to...:-s) whereas ADO couldn't, IINM... Could you be referring to different syntaxes in mind?

LPurvis · Feb 4, 2010

>> Could you be referring to different syntaxes in mind?
Yes indeed.
Object.BeginTrans
I'd consider methods of the data API which wraps transaction functionality. (Where Object could be DAO or ADO sourced as you say).

Natively supported in DML SQL such as
BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION

But, as I say, only the OLEDB provider supports that syntax.
(And, naturally, since this is Jet, you have to execute single statements at a time - you just need to do so through the same connection object).
e.g.
cnn.Execute "BEGIN TRANSACTION"
cnn.Execute "UPDATE TableName Set FieldName = 0"
cnn.Execute "ROLLBACK TRANSACTION"

That's all I was getting at.
(Apart from the Identity stuff too of course).

Cheers.

Banana · Feb 4, 2010

Aha, didn't think of that angle. Yeah, I just tried this out in DAO:

Code:

With CurrentDb
   .Execute "BEGIN TRANSACTION;", dbFailOnError
   .Execute "ROLLBACK;", dbFailOnError
End With

and that raises an error, complaining that the SQL statement should begin with ....

Never actually occurred to me to use SQL DML with Jet/ACE like that though I do that all times with MySQL & SQL Server. For some reasons, seem more intuitive to use the Object.BeginTrans/Object.Rollback/Object.CommitTrans in either model but that's just me.

However, this does work:

Code:

With CurrentDb
    Debug.Print .OpenRecordset("SELECT @@IDENTITY;", dbOpenSnapshot).Fields(0)
End With

LPurvis · Feb 4, 2010

Yes @@Identity works fine through DAO.
It was a misconception that it was accessible via ADO only - probably because of the big push towards ADO when MS released Access 2000 (and therefore Jet 4 where @@Identity was introduced).

Hell, it even works through the Access UI - but it's much harder to scope the connection.

Cheers.

Banana · Feb 4, 2010

Bob -

Apologies for not having mentioned this earlier but it occurred to me one pitfall you may want to watch is how to handle transactions in error handling.

You need to make sure you don't exit a procedure with a transaction hanging. So take care to be sure that error handling will roll back the transaction (if appropriate) and that all possible exits will either commit or rollback transaction.

Example:

Code:

Private Sub Doit()

On Error Goto Oops

With <context>
   .BeginTrans
   ....
   If <test> Then
      ....
      CommitTrans
   Else
      RollBackTrans
   End if
End Sub

Exit:

'Clean up here

Oops:

Select Case Err.Number
    Case XXX
       'Rollback
    Case YYY
       'Re-try by resuming
    Case ZZZ
       'Commit transaction anyway because it's OK
End Select

GoTo Exit

End Sub

Unfortunately, there is no property or flag that tells you what state you are in regarding to the transactions so there's no simple check to ask either object model (AFAIK) "are we still in middle of a transaction?", hence need for the care that all exit points are covered.

It may be simpler to use your own flag and funnel through one entry and exit point:

Code:

Private Sub StartWork()

Dim bTranSuccess As Boolean

bTranSuccess = DoUnitOne()
If bTranSuccess Then
   bTranSuccess = DoUnitTWo()
End If

If bTranSuccess Then
   CommitTrans
Else
   RollBack
End If

End Sub

Private Function DoUnitOne() As Boolean

On Error Goto Oops

...

DoUnitOne=True

Exit Sub

Oops:

DoUnitOne=False

End Sub

Private Function DoUnitTwo() As Boolean

... similar to DoUnitOne

End Function

Hopes this give you some idea.

Banana · Feb 4, 2010

LPurvis said:
Yes @@Identity works fine through DAO.
It was a misconception that it was accessible via ADO only - probably because of the big push towards ADO when MS released Access 2000 (and therefore Jet 4 where @@Identity was introduced).

Hell, it even works through the Access UI - but it's much harder to scope the connection.

Cheers.

Right. It may just be me but I've yet to need an occasion where a user needed more than one connection so the scoping issue is not really that relevant because it's still per-user in either model, but I do agree that scoping will be a bit problematic in DAO compared to ADO where making a new connection object is comparatively easier than making a new workspace.

LPurvis · Feb 4, 2010

I agree, muliple connections are a (and should be) a relatively rare thing.

Just to be clear though, when I was talking about the scoping being harder - I was referring to using @@Identity not from DAO or ADO, but from Access itself.
(Which is tricky to the extent of being all but useless - but supported).

Cheers.

Banana · Feb 4, 2010

Hmm, you got me curious. Care to elaborate? I'm guessing we're now talking about the absence of SQL Server's useful SCOPE_IDENTITY()?

Bob · Feb 4, 2010

Thanks Banana and LPurvis. All very useful and very much what I was looking for.

As I was planning to use ADO I thought of having a simple boolean controlling whether we were in a transaction or not. Declaring my connection object WithEvents should fire BeginTransComplete, CommitTransComplete and RollbackTransComplete at the right time and there I could set the boolean.

Banana · Feb 4, 2010

Bob said:
Thanks Banana and LPurvis. All very useful and very much what I was looking for.

As I was planning to use ADO I thought of having a simple boolean controlling whether we were in a transaction or not. Declaring my connection object WithEvents should fire BeginTransComplete, CommitTransComplete and RollbackTransComplete at the right time and there I could set the boolean.

Indeed, I forgot about events supported in ADO, so that's one advantage for ADO over DAO and yes, I would think a Property procedure would provide a reliable indication of what state you are in because only events should manipulate this boolean property.

Hope you have enough to get a good start, and do feel free to post back with any questions.

LPurvis · Feb 4, 2010

Not really no, though Scope_Identity would be largely unnecessary in Jet (there's not really anything that could cause the @@Identity to be affected - Data Macros could I suppose - but I'd imagine won't).

I was referring to how you can have an actual query defined in Access, such as:
SELECT @@Identity As NewID

And it will execute.
However the scope of the connection Access uses is entirely down to the discresion of the application itself. (Access is free to drop and recreate connections as it sees fit).
It uses a different object to access the database than DAO and ADO's default objects. (The holy trinity if you like lol. Access UI, DAO CurrentDb and ADO CurrentProject.Connection ;-)

Consequently the only thing that Access query will give you most of the time is 0.
However if you tighten the scope of the connection Access employs with the currently running database then you can see that @@Identity is at least working in the UI.
Change the query def to a Insert
INSERT INTO TableName (FieldName) VALUES ('New Value')
run it,
With the window still open, change the SQL definition of that same query to
SELECT @@Identity As NewID
and then open it. You should see the inserted AN value.
Move outside and nada. Back to zero.
That's what I mean by extremely limited connection scope through the Access UI. ;-)

Cheers.

Banana · Feb 4, 2010

You know, I like the idea of "Holy Trinity" in Access.

Thanks for the elaboration, Leigh. It never actually occurred to me to try and do a select AN in a saved query, but I can see how someone might think it would provide a convenient placeholder to retrieve the value. C'est la vie.

I really should spend more time thinking about 'What if..." situations...

Thanks again.

Good practice on transactions (1 Viewer)

Bob

Registered User.

Banana

split with a cherry atop.

Bob

Registered User.

SOS

Registered Lunatic

Banana

split with a cherry atop.

LPurvis

AWF VIP

Banana

split with a cherry atop.

LPurvis

AWF VIP

Banana

split with a cherry atop.

LPurvis

AWF VIP

Banana

split with a cherry atop.

Banana

split with a cherry atop.

LPurvis

AWF VIP

Banana

split with a cherry atop.

Bob

Registered User.

Banana

split with a cherry atop.

LPurvis

AWF VIP

Banana

split with a cherry atop.

Similar threads

Users who are viewing this thread