Solved

For connection to SQL server, does nested transaction possible ?

Posted on 2001-06-13
19
285 Views
Last Modified: 2013-11-23
I have a question related to connection to SQL server.

I have a code which is as follows :

   cn.BeginTrans
   
     cn.BeginTrans
     strsql = "UPDATE file1 SET field1='888'"
     cn.Execute strsql
     cn.CommitTrans
   
     cn.BeginTrans
     strsql = "UPDATE file1 SET field1='777'"
     cn.Execute strsql
     cn.CommitTrans
   
   cn.RollbackTrans

I did not run the above logic as it returns error :
"Only one transaction can be active"

Does this kind of nested transaction in VB possible ?

0
Comment
Question by:BarryTang
  • 10
  • 4
  • 4
  • +1
19 Comments
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6189682
It depends on the provider for ADO, whether nested transactions are supported or not.
0
 

Author Comment

by:BarryTang
ID: 6189707
I use the Microsoft ActiveX Data Objects 2.1 Library
for this connection but fail, so do you know which
provider for ADO can make it work ?
0
 

Author Comment

by:BarryTang
ID: 6189714
I use the Microsoft ActiveX Data Objects 2.1 Library
for this connection but fail, so do you know which
provider for ADO can make it work ?
0
 

Expert Comment

by:guntherds
ID: 6189824
I guess you can check a propertie, the one that's a collection of properties, on the connection
otherwise , write you code as follow:



  cn.BeginTrans
 
    strsql = "UPDATE file1 SET field1='888'"
    cn.Execute strsql
 
    strsql = "UPDATE file1 SET field1='777'"
    cn.Execute strsql

  cn.CommitTrans
 
on exception:  cn.RollbackTrans
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6189868
It is possible to use nested transactions in SQL Server, say in stored procedures executions etc.

You could better use a single transaction and put all sql statements as guntherds has commented, since nesting transaction is not advisible.

check this link for info:
http://www.sql-server-performance.com/reducing_locks.asp

Hope it helps.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6189876
It is possible to have multiple transactions, as per the SQL documentation, from MS and SQL Books online.
It uses the direct sql statements for transactions.
0
 

Author Comment

by:BarryTang
ID: 6189943
Thanks for response for valli_an and guntherds

For Valli_an, I do need to commit a record at first and
then refer this committed record to do somethings, and
to rollback all the transaction if something wrong during
the above transaction, which should be something like
the following....

  cn.BeginTrans
 
    cn.BeginTrans
    strsql = "UPDATE file1 SET field1='888'"
    cn.Execute strsql
    cn.CommitTrans
 
    cn.BeginTrans
    strsql = "select * from file1 where field1='888'"
    cn.Execute strsql
    cn.CommitTrans
 
  cn.CommitTrans

If I do not commit the first statement, I am able to
refer to it in the second statement !

I would like to ask for guntherds if your suggestion
can do that or not, and would you please to give me the
exact propertie name because I could not find out.

Thank you

0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 40 total points
ID: 6190073
When you do retrieving, within a transaction, it should have the updated data, if I am right.

I also tried opening a recordset, it works well with the updated data after Begintrans.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6190083
  cn.BeginTrans
   strsql = "select * from file1 where field1='888'"
   cn.Execute strsql
   cn.CommitTrans

the execute statement returns a recordset, but you dont use it here.

try like this:
dim rsfile1 as recordset

   cn.BeginTrans

   strsql = "select * from file1 where field1='888'"
   Set rsfile1 = New Recordset
   Set rsfile1 = cn.Execute (strsql)
   ':process the records here with recordset, if need be
   'you can also use sql statements to execute here
   'like update statement.

   cn.CommitTrans

hope this helps.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:andyclap
ID: 6190099
Hi - SQL Server doesn't support nested transactions. It uses the concept of savepoints within a transaction rather than (more logical) nested transactions. Unfortunately the ODBC driver doesn't do the (quite simple) work for you.
I'm a bit fuzzy on this as it's been ages since I used it, but I think you might need something like:

cn.BeginTrans
 
   cn.execute "SAVE TRAN step1"
   strsql = "UPDATE file1 SET field1='888'"
   cn.Execute strsql
   'cn.CommitTrans
 
   cn.execute "SAVE TRAN step2"
   strsql = "select * from file1 where field1='888'"
   cn.Execute strsql
   'cn.CommitTrans
   'rollback step 2 for example
   cn.execute "ROLLBACK TRAN step2"
 
 cn.CommitTrans


I used to use a little wrapper for begin commit and rollback to make it more implicit, and help conversion if I needed to switch to a db which supported nested transactions properly - something along the lines of (typing off the top of my head here, so excuse typos):

private mlTranDepth as long
sub TransactionBegin(byval con as Connection)

  if mlTranDepth=0 then
     con.BeginTrans
  else
     con.execute "SAVE TRAN t" & mlTranDepth
  end if
  mlTranDepth=mlTranDepth+1

end sub

sub TransactionRollback(byval con as Connection, optional byval bFullRollback as boolean=false)

  mlTranDepth=mlTranDepth-1
  if bFullRollback then
    mlTranDepth=0
  end if
  if mlTranDepth=0 then
    con.rollbackTrans
  else
    con.execute "ROLLBACK TRAN t" & mlTranDepth
  end if

end sub

sub TransactionCommit(byval con as Connection, optional byval bFullCommit as boolean=false)

  mlTranDepth=mlTranDepth-1
  if bFullCommit then
    mlTranDepth=0
  end if
  'note committed nested transactions will be committed when their top level transaction commits
  if mlTranDepth=0 then
    con.commitTrans
  end if
 
end sub

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6190205
andyclap,

The meant the savepoints as nested transactions, as it is in some web sites also.

But, is there a reason to do it, from VB? I see that it might be needed if some stored procedures use transactions, and some other places, they are executed from.

Also, I found that it is not a good idea to use nested transactions, as per the link that I have provided above, says.

Cheers.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6190211
sorry,
The meant the savepoints as nested transactions, as it is in some web sites also.

should read as,

I meant the savepoints only, as nested transactions, as it is in some web sites also.
0
 
LVL 6

Expert Comment

by:andyclap
ID: 6190335
Using explicit transactions inside SPs is not really a good idea if you're planning on using the SPs from within a vb program which maintains transactions itself (or is running under MTS). The transactional state should be maintained at one point only, either at the database end (which more or less rules out MTSs distributed transactions), or at the client end (ie VB/MTS).

Rather than using transactions as an 'undo' mechanism within stored procedures, it's better to write explicit code to test results and undo the changes if transactional state will be maintained by the client.

Is this helping? I'm a little confused as to what you're trying to do, and why you're doing it.

Remeber transactions are active across a connection, rather than a statement, so naturally if you do not commit the first statement, you are able to refer to it in the second statement, as it's over the same conenction and hence within the same transaction.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6190412
>The transactional state should be maintained at one point >only, either at the database end (which more or less >rules out MTSs distributed transactions), or at the >client end (ie VB/MTS).

ya, the nested transactions that I meant was referring to the SQL Server documentation.

From SQL Server Books Online:
Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

They have also given example there.

But Barrytang,
did your problem solve? Try using only one transaction for all sql statements. thats it. does this work?


0
 
LVL 6

Expert Comment

by:andyclap
ID: 6191342
Hmm, I'm not sure about nesting explicitly named transactions: I just did a quick test and it wouldn't roll back an inner named transaction, only the outermost one.

Have you thought about using @@trancount in your procedures?

you could do something like
if @@trancount=0
  begin tran a
else
  save tran a

....

if @itworked=1
  commit tran a
else
  rollback tran a
0
 

Author Comment

by:BarryTang
ID: 6202745
Thank you very much for valli_an and andyclap comment.
Both are useful.

Anyway, I try valli_an comment that the program can
read an uncommitted SQL statement and found that it
is true. It is a more simple solution so I accept
valli_an as the answer of my question.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6203099
Thank you.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6203109
And also, thanks, andyclap, for the discussions and your code on Savepoint.
0
 
LVL 6

Expert Comment

by:andyclap
ID: 6204422
No propblemo, it was interesting to revisit it again after a couple of years :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now