Help! Don't understand transactions

Hi guys,
I'm having troubles with transaction control.
I recently decided to add transaction control to some operations on an
app I am developing (Dreamweaver MX 2004, ASP, VBScript, ADO and
SQLServer 2000).
I started by doing a test web page with an update of up to 3 different
tables driven by simple pushbuttons, and more buttonds for BEGIN TRAN,
COMMIT and ROLLBACK.
Worked perfectly. It did exactly what I wanted, and even though I was
using separate record set variable for each of the commands and tables,
and closing my connection after every BEGIN TRAN.
So I decided to build that same kind of logic in my app, and test it. I
built in an error in a chain of inserts and updates I am doing, and I
put an error trap around each of the possible error-producing actions.
When an error occur, I check if a transaction has been started (which is
indicated by a flag set by my code when calling up the BEGIN TRAN), and
if yes, I rollback.
The results are very unimpressive: it doesn't work. The error that I
built in is to insert a duplicate row, but before that, I have inserted
proper rows. The code detects the error, executes the roll back, but the
rows inserted after the BEGIN TRAN and before the error are in the DB !
I.e. it looks like the BEGIN TRAN does not work. How can I check this,
and what could be the reasons that my test app works fine, and my real
app doesn't ? In the real app, the error is happening on a stored
procedure call, which is the one inserting the rows. Is the behaviour of
an SP different than a normal SQL statement sent over ADO ?
I have attached my test page.
Can you tell me:
- when should I issue the BEGIN TRAN ? Before the very first INSERT /
UPDATE, or can it be even before that (I do quite a bit of reading
(SELECT) before doing the first INSERT)
- on which connection
- should I have all the updating / inserting SQL statements going on one
and the same connection, or can I use different connections ?
- somebody told me there was a difference if I was using the
ActiveConnection object as opposed to just a connection object.
- I changed my routine that does the BEGIN / COMMIT / ROLLBACK to this
in my real app:
sub TransControl(action)
Set conn= Server.CreateObject("ADODB.Connection")
conn.Open MM_SDS_STRING
select case action
case "BEGIN"
sql= "BEGIN TRANSACTION"
Response.Write("Starting transaction <br>")
case "COMMIT"
sql= "COMMIT TRANSACTION"
Response.Write("Committing transaction <br>")
case "ROLLBACK"
sql= "ROLLBACK TRANSACTION"
Response.Write("Rolling back transaction <br>")
case else
end select
Set rs= conn.Execute(sql)
conn.Close
set conn= nothing
set rs = nothing
end sub
Does this make any difference to my DoIt routine in the test page ?
- and finally: I just noticed that I use the follwing 2 syntaxes:
conn.Open <connection string>
and also:
conn.Open = <connection string>
What's the right syntax ? Starngely, both seem to work
I am a bit lost, and would appreciate a lot if you could point me in the
right direction.
Thanks
Bernard
bthouin wrote:[vbcol=seagreen]
The transaction must always be on the same connection. You want a
transaction to run as quickly as possible, so issue if you are executing
multiple procedures and they must be a part of the same transaction,
issue the begin tran, execute the procedures, and then rollback or
commit the transaction.
For example:
create table tran_test( col1 int)
go
create proc dbo.tran_test_insert (@.i int)
as
Insert into dbo.tran_test (col1) values (@.i)
go
-- test 1
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Rollback
Select * from dbo.tran_test
go
-- test 2
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Commit Tran
Select * from dbo.tran_test
go
drop proc dbo.tran_test_insert
go
drop table dbo.tran_test
go
David Gugick
Imceda Software
www.imceda.com
|||Hi David,
Thanks for answer. I'm aware of the need to have quick transactions. But
I'm not working directly at the DB level, i.e. I'm not only using stored
procedures, I'm mostly building my SQL statements and executing them
through ADO. So your example is clear, but I can't apply it directly.
For one, there is no explicit connection in stored procedures. So that
makes it harder for me, as I am using the typical VB-like ASP ADO
commands. And Dreamweaver, when you let it generate all the data access
stuff, builds by default a new connection for every access, and closes
it after the access, plus it destroys the connection object. Although I
am not using Dreamweaver-generating capabilities, I have "inherited"
that usage concept of - constructing the access object; - accessing the
data; - closing the connection and destroying the access object, just to
keep memory clean.
And, what IS a connection ? Is it that which is established by saying:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open <connection string>
Or the alternative:
Set rs_T = Server.CreateObject("ADODB.Command")
rs.ActiveConnection = <connection string>
?
Is there any difference between the 2 possibilities ?
What's the difference between the "conn" connection object in the 1st
possibility and the "rs" command object (also called record set) in the
2nd possibility ?
All this is not clear to me, and I'd be VERY thankful for some answers.
Regards
Bernard
David Gugick wrote:
> bthouin wrote:
>
> The transaction must always be on the same connection. You want a
> transaction to run as quickly as possible, so issue if you are executing
> multiple procedures and they must be a part of the same transaction,
> issue the begin tran, execute the procedures, and then rollback or
> commit the transaction.
> For example:
> create table tran_test( col1 int)
> go
> create proc dbo.tran_test_insert (@.i int)
> as
> Insert into dbo.tran_test (col1) values (@.i)
> go
> -- test 1
> Select * from dbo.tran_test
> Begin Tran
> Exec dbo.tran_test_insert 1
> Exec dbo.tran_test_insert 2
> Exec dbo.tran_test_insert 3
> Rollback
> Select * from dbo.tran_test
> go
> -- test 2
> Select * from dbo.tran_test
> Begin Tran
> Exec dbo.tran_test_insert 1
> Exec dbo.tran_test_insert 2
> Exec dbo.tran_test_insert 3
> Commit Tran
> Select * from dbo.tran_test
> go
> drop proc dbo.tran_test_insert
> go
> drop table dbo.tran_test
> go
>
>
|||bthouin wrote:
> Hi David,
> Thanks for answer. I'm aware of the need to have quick transactions.
> But I'm not working directly at the DB level, i.e. I'm not only using
> stored procedures, I'm mostly building my SQL statements and
> executing them through ADO. So your example is clear, but I can't
> apply it directly. For one, there is no explicit connection in stored
> procedures. So that makes it harder for me, as I am using the typical
> VB-like ASP ADO commands. And Dreamweaver, when you let it generate
> all the data access stuff, builds by default a new connection for
> every access, and closes it after the access, plus it destroys the
> connection object. Although I am not using Dreamweaver-generating
> capabilities, I have "inherited" that usage concept of - constructing
> the access object; - accessing the data; - closing the connection and
> destroying the access object, just to keep memory clean.
>
[vbcol=seagreen]
That's not true. It may be that the way the code is generated, the
procedure executes on a short-lived connection, but one is there. I
don't know if this is a web app or not, but in any case, using that
paradigm does not allow you to use a transaction the way you want. Every
time you create a new connection, by using Conn.Open, you get a "new"
connection. It's possible if you are using connection pooling (and you
should be with that type of design) that you "could" get the same
connection twice, but you can never depend on it.
If you need to mix procedures and SQL in any combination and have those
statements be in the same transaction, you need to use the same
connection.
David Gugick
Imceda Software
www.imceda.com