Efficient way for Begin,Commit and rollback transaction. ?

[coolkish]

I am using ADODB property of BeginTrans, CommitTrans, RollBackTrans but they have to be used in
very Forms where Insert, update ,Delete command occured.


Is there any other efficent way for Rollback,Commit Transaction? At present we are using Access & then we will shift to Oracle / SQL Server.

[dee-u]

What do you mean by efficient? Rollback is usually called in cases where there is an error occured in the processing of those action queries...

[Chubby]

I am using ADODB property of BeginTrans, CommitTrans, RollBackTrans but they have to be used in
very Forms where Insert, update ,Delete command occured.


Is there any other efficent way for Rollback,Commit Transaction? At present we are using Access & then we will shift to Oracle / SQL Server.

For my money I would steer clear of the ADODB properties for these. The main reasoning is that if your app hangs when a transaction has been begun and is, say holding a row of a table locked for update, no other use of the database can access this row. Additionally, if the SQL is either complex or even slightly incorrect (i.e. issues a table scan etc) then its highly possible that a begun transaction could even lead to a whole table being locked out).

IN my humble opinion I find it better to put Transaction Manaqement into Stored Procedures themselves and keep the scope of the transaction to the bareset minimum.

Chubby..

[dee-u]

For my money I would steer clear of the ADODB properties for these. The main reasoning is that if your app hangs when a transaction has been begun and is, say holding a row of a table locked for update, no other use of the database can access this row. Additionally, if the SQL is either complex or even slightly incorrect (i.e. issues a table scan etc) then its highly possible that a begun transaction could even lead to a whole table being locked out).

Isnt that has got something to do with using specific type of locks? Will the ff. code encounter such problem?

Public Sub TestTransaction()
    On Error GoTo HandleError
    adoConn.BeginTrans
    adoConn.Execute "UPDATE Table..."
    adoConn.CommitTrans
    Exit Sub
HandleError:
    adoConn.RollbackTrans
End Sub

And Access doesnt have SP...

[Chubby]

Isnt that has got something to do with using specific type of locks? Will the ff. code encounter such problem?

Public Sub TestTransaction()
    On Error GoTo HandleError
    adoConn.BeginTrans
    adoConn.Execute "UPDATE Table..."
    adoConn.CommitTrans
    Exit Sub
HandleError:
    adoConn.RollbackTrans
End Sub

And Access doesnt have SP...

Access doesn't have SP but the Query is essentally considered the same as an SP in the later versions of Acess at any rate. As you're migrating to either SQL Server or Oracle also its a consideration as you will more than likely end up re-engineering part of your application to handle this ?..

With the code (above) everything will work fine if the code enters the HandleError block. IF the "Update Table..." execute fails to return then your App hangs, Transaction never gets committed. Not the end of the world as you can set the QueryTimeout of the connection, just as a personal thing I prefer not to get into these kinds of issues.... Added to which you're essentially using the ADO connection to handle the transaction rather than making the Server Code transaction safe internally.....

Chubby..

[yrwyddfa]

If you are only executing one SQL stmt then you will get an implicit transaction anyway (on SQL Server)

You only need to wrap up many SQL stmts in a transaction and only if you need the operations to act 'as one' ie atomic.

As I see it you've three options

ADODB (as discussed here)
COM transactions
Stored Procedures

Stored procedures are probably the most efficient.

[ashly]

Isnt that has got something to do with using specific type of locks? Will the ff. code encounter such problem?

Public Sub TestTransaction()
    On Error GoTo HandleError
    adoConn.BeginTrans
    adoConn.Execute "UPDATE Table..."
    adoConn.CommitTrans
    Exit Sub
HandleError:
    adoConn.RollbackTrans
End Sub

If I have 2 connections, adoConn & adoConn1.

Public Sub TestTransaction()
    On Error GoTo HandleError
    adoConn1.BeginTrans
    adoConn1.Execute "INSERT INTO Table..."
    adoConn1.CommitTrans
 
    adoConn.BeginTrans
    adoConn.Execute "UPDATE Table..."
    adoConn.CommitTrans
    Exit Sub
HandleError:
    adoConn.RollbackTrans
    adoConn1.RollbackTrans
End Sub

I have 1st insert into statement, then 2nd update statement.
If my update statement failed, can I roll back the 1st insert into statement transaction?

[dee-u]

If adoConn has not started a transaction then doing a RollBack will generate an error.

[UtOkbOlinAO]

Just put the rollback trans where the DML is located in the procedure.

[ashly]

If adoConn has not started a transaction then doing a RollBack will generate an error.

But for the example that given, I already have adoConn1.CommitTrans, then when got eror at adoConn.Execute, can I do adoConn1.RollbackTrans?

Or simple explanation, can I have .RollbackTrans after .CommitTrans?

[dee-u]

No, I think not. Why are you using two connections in the first place?

[ashly]

No, I think not. Why are you using two connections in the first place?

I need to copy records from server1 to server2. After record being inserted to server2, I need to update a flag in server1.

This is why I need 2 connections.

[dee-u]

I think you can accomplish that with something like this.

Private Sub CommitOrRollbackTransactions()
    adoConn1.BeginTrans
    adoConn2.BeginTrans
    If Execute1("x") = True And Execute2("y") = True Then
        adoConn1.CommitTrans
        adoConn2.CommitTrans
    Else
        adoConn1.RollbackTrans
        adoConn2.RollbackTrans
    End If
End Sub

Private Function Execute1(ByVal strSQL As String) As Boolean
    On Error GoTo HandleError
    adoConn1.Execute strSQL, , adExecuteNoRecords
    Execute1 = True
    Exit Function
HandleError:
    Execute1 = False
    MsgBox "Errored!"
End Function

Private Function Execute2(ByVal strSQL As String) As Boolean
    On Error GoTo HandleError
    adoConn2.Execute strSQL, , adExecuteNoRecords
    Execute2 = True
    Exit Function
HandleError:
    Execute2 = False
    MsgBox "Errored!"
End Function

[leinad31]

IMO if its possible (only consideration is technical in nature) I would fast track migration to Oracle or SQL Server. There are lots of advantages and some were already mentioned... my two cents, what is most often overlooked in database application development is making the testing process efficient.

With stored procedures you can test to your hearts content at the database level without having to worry about GUI related errors/incorrect settings... its a classic input/output procedural exercise in logic. And since procedural SQL is compiled, you get to avoid typo related errors in SQL construction.

You can test in phases, first ensure stored procedures are correct then proceed with end-to-end test using GUI. Compare to performing end-to-end test for each and every possible scenario/branching at the client-side.

You can also automate tests using stored procedures; retrieve hundreds or thousands of test data from source table and dump output in result table then check using SQL (select records where result is not same as existing parallel data). Can you imagine how tedious that amount of test would be if done manually using GUI? Your fingers will start to cramp just after several test data.

Related to automation, you can even check backward compatibility of updated stored procedures. Just run them in parallel and check if they return same output. I did this when I corrected a LUHN algorithm, I needed to make sure that keys previously generated were still supported and I was able to check all of them within a minute or so using SQL.

Then there's dependency tracking feature of database. Can you tell of the top of your head which forms and applications depend on a particular table? Database dependecy tracking is not 100% but it is a lot better than not knowing at all or needing more than 5 years of experience on the system or shifting through lots of supplementary documentation just to be able to find which forms will be affected by an additional column on a table (that was an exaggeration but I'm sure you get the point).

You also simplify exception handling at the client side... after all, there's not much the client app can do when a database related error occurs other than keeping itself from crashing and forwarding to the user an intelligible message. Why not have the stored procedure also perform error logging right?

[abhijit]

IN my humble opinion I find it better to put Transaction Management into Stored Procedures themselves and keep the scope of the transaction to the bareset minimum.

Chubby..

What if you had to call
StoredProc1
StoredProc2
StoredProc3

Then you had to rollback after storedproc3?
At this time, you would have committed the changes made by StoredProc1 and StoredProc2.

[brucevde]

What if you had to call
StoredProc1
StoredProc2
StoredProc3
Then you had to rollback after storedproc3?

Then you have a flaw in your original design or the business rules have changed. Regardless, simply create stored procedure 4 which calls and encompasses sprocs 1 - 3 in a transaction.

[abhijit]

Then you have a flaw in your original design or the business rules have changed. Regardless, simply create stored procedure 4 which calls and encompasses sprocs 1 - 3 in a transaction.

That's what my front-end code does. I can't keep changing stored procedures, just for changing the transaction logic.

Encompasses stored procedures in a single transaction. I feel that transaction control must be done by the top level invoker, in this case my vb code. The sole exception being auditing transactions.

[leinad31]

That's what my front-end code does. I can't keep changing stored procedures, just for changing the transaction logic.

Encompasses stored procedures in a single transaction. I feel that transaction control must be done by the top level invoker, in this case my vb code. The sole exception being auditing transactions.

As mentioned there's probably a flaw in the design specifically with regard to entry point of transaction processing. Make design comparable to modern OS library (e.g. DLL) architecture... versions come and go but backward compatibility (specifically call/entry points) is maintained.

Your application is probably limited to a small number of users/installations for you to consider continuous front-end upgrades as feasible... if you had more than 200 users/PCs in various locations (intermediaries/partners, brokers, branches, etc) deploying your front end repeatedly would be tedious and expensive (travel cost, meal allowances, time, effort, etc) as compared to centrally managed source code/business processes/stored procedures.

I'm sure someone at where you work is tasked with assessing all the issues involved with your rapid front-end change... I'm sure he already considered orchestration, migration to web based application, streamlining development/testing/maintenance, investing in reusability/libraries, etc. With regard to migration to web based app, this old article may give you additional insights; read the section Factoring Out Application Tier Code (other side of the coin, opposite of choice you made) at http://www.oracle.com/technology/pub...stopython.html

"I feel that transaction control must be done by the top level invoker, in this case my vb code."
As to that statement... in most cases such preference is not because all the pros and cons have been weighed from low level to high level view of task in an unbiased manner but rather it's simply because the programmer prefers to program in the language he is most productive with and will surely pay the bills. I'm not singling you out... it a common trend. Be honest if you think your being restricted by that line of thinking... if so then experiment moving out of your comfort zone. Also consider this... we wouldn't be members of vbforum if we don't have experience developing in VB and yet here we are advising an alternate solution...

[abhijit]

"I feel that transaction control must be done by the top level invoker, in this case my vb code."
As to that statement... in most cases such preference is not because all the pros and cons have been weighed from low level to high level view of task in an unbiased manner but rather it's simply because the programmer prefers to program in the language he is most productive with and will surely pay the bills. I'm not singling you out... it a common trend. Be honest if you think your being restricted by that line of thinking... if so then experiment moving out of your comfort zone. Also consider this... we wouldn't be members of vbforum if we don't have experience developing in VB and yet here we are advising an alternate solution...

Perhaps, I need to rephrase that. I am not against putting business logic in stored procedures. Transaction logic is limited to "Begin Trans" & "Commit Trans".
Business logic is different. Business logic for example would be calculating the amount due on a lease if the customer wants to get out of the contract. I can see myself (and have been) putting that kind of logic in the stored procedure. As a matter of fact, in the last 10 years or so, I have been slowly but surely moving towards development in database languages ( PLSQL in my case).

However I have almost never put a commit statement ([COLOR="rgb(112, 128, 144)"]exception being audit triggers[/COLOR]) in a stored procedure.

[leinad31]

Perhaps, I need to rephrase that. I am not against putting business logic in stored procedures. Transaction logic is limited to "Begin Trans" & "Commit Trans".
Business logic is different. Business logic for example would be calculating the amount due on a lease if the customer wants to get out of the contract. I can see myself (and have been) putting that kind of logic in the stored procedure. As a matter of fact, in the last 10 years or so, I have been slowly but surely moving towards development in database languages ( PLSQL in my case).

However I have almost never put a commit statement ([COLOR="rgb(112, 128, 144)"]exception being audit triggers[/COLOR]) in a stored procedure.

Error logging via PRAGMA AUTONOMOUS_TRANSACTION is another exception.

Only other concern then would be number of connections maintained and excessive transaction management at client side which may result in numerous redo logs, locking, and limited scalability... if so then consider splitting transaction into several phases that dump into temporary or working tables so you can COMMIT more often, minimize locks, and minimize extent of changes to transaction processing code (per group of steps rather than end-to-end). To visualize what I mean, imagine you only have one connection to work with as with legacy client-server systems (Developer/2000 and 6i).

[dilettante]

Properly locating components of an application can also insulate the development team from costly recoding if the system or the location of the data changes. For example, by putting the data access rules in a data layer rather than in stored procedures, the application is more easily insulated from dependence on a specific DBMS. Not only are changes confined and testing compartmentalized, but data sources can be changed and data can be distributed without fundamentally changing the application.

The Physical Model: Application Architecture

Stored procedures = planned obsolescence, and therefore bad from an employer/customer point of view. Business logic should go into a middle tier, between the DAL and UI layer. Also see Using a Three-Tier Architecture Model.


Excessive use of temporary tables in the database can break connection pooling in the DAL or middle tier, or cause database server congestion.

Temporary Tables and Resource Pooling

If the connection is returned to the pool, the temporary table will persist until the connection is actually released—not just returned to the pool. If your application uses many temporary tables, this might create a resource problem on the server. If you use a stored procedure on the server to create the temporary table, the temporary table will be destroyed when the stored procedure goes out of scope. Temporary tables not created by stored procedures will be destroyed only after the connection is actually released from the pool.

Pooling in the Microsoft Data Access Components

One might take this as a point in favor of using stored procedures... or avoiding the use of temporary tables.