Database Transaction

[charonlee]

hi,

just curious to know, Can the beginTrans work at Recordset level. As I found, all are done at connection, i.e:

BeginTrans
Con.Execute (SQL)
CommitTrans

can it be like that

BeginTrans
rs.Open SQL, con
CommitTrans

[Scampi]

Why, what are you trying to do so that you cant do it via a connection instead of a recordset?

[duckhunter]

No need to use transactions around a simple select statement.

SQL uses implicit transactions when running Selects statements. If you specify "with (nolock)" the query will not honor any locks placed on it by another process. If you leave the "with (nolock)" off, and another process is in the middle of an update, your select will wait for it to complete.

select field1 from myTable (or)
select field1 from myTable with (nolock)

The "with (nolock)" gives you what is called a Dirty Read. Which means that if a process is updating the row when you select, you'll get the data before the update occurs.

If you truly do want transactions:

Sub ExecuteMySQL(mySQL, DBConn)
On error resume Next

Set rs = server.createobject("ADODB.RECORDSET")

DBConn.BeginTrans
Set rs = DBConn.Execute(mySQL)

If DBConn.Errors.Count > 0 Then
If DBConn.Errors.Item(0).Number <> 0 Then
DBConn.RollbackTrans
Exit Sub
End If
End If

DBConn.CommitTrans

End Sub

[charonlee]

DuckHunter,

ThanK so much for your example and explaination.

well, can I confirm something.
1.) The begin and end of the transaction must done at DbConn
DBConn.BeginTrans
Set rs = DBConn.Execute(mySQL)
DBConn.RollbackTrans / DBConn.CommitTrans
It can be done at Recordset like this:
BeginTrans
rs.Open SQL, con
If not rs.oef then
rs.addnew
-------
rs.update
CommitTrans

[charonlee]

oh ya, want to add something

>>Why, what are you trying to do so that you cant do it via a connection instead of a recordset?

No, I just want to know whether it can be used in recordset or not.

duckhunter, if I have two SQL statement to execute, as below:

Sub ExecuteMySQL(mySQL, DBConn)
On error resume Next

Set rs = server.createobject("ADODB.RECORDSET")

DBConn.BeginTrans
Set rs = DBConn.Execute(mySQL)
Set rs2 = DBConn.Execute(mySQL2)

If DBConn.Errors.Count > 0 Then
If DBConn.Errors.Item(0).Number <> 0 Then
DBConn.RollbackTrans
Exit Sub
End If
End If

DBConn.CommitTrans

End Sub

[duckhunter]

Are both of these SQL's updates/inserts? If so, then there's no need for a Recordset, just DBConn.Execute and error checking.

The Connection Object is what will manage the Transaction and you have to explicitly code it or RollBacks from multi table updates are not possible when one statement fails.

On the Recordset side, you can set the LockType property on the recordset to one of the following values. Realize these locking parameters only apply when changing data, not selecting and do not allow for Rollbacks.

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

This defines each Value: [msdn.microsoft.com ]

[charonlee]

Problem one
Hi, actually I use recordset object instead of connection object no matter I select/update/delete
for instance I want to update my record:
//there has functions for DbConnectionOpen and RsOPen
Function RsOpen(ByVal strSQL, ByVal objDC)
Dim objRS
'talk to the database using ADODB.Recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
with objRS
.ActiveConnection = objDC
'use client side cursor
.CursorLocation = adUseClient
.CursorType=adOpenDynamic
'other users can access the record until changes are actually committed
.LockType=adLockOptimistic
end with
objRS.Open strSQL, objDC
Set RsOpen = objRS
End Function

Set objDC = DbConnectionOpen(DataPath)
sSQL="SELECT * FROM myData"
Set objRS = RsOpen(sSQL, objDC)
If Not objRS.EOF Then
objRS.AddNew
objRS("ID") = ID
objRS("Name") = Name
objRS("Job") = Job
objRS("Dprt") = Department
objRS.Update
Response.Write ("Record Updated!")
End If

End If

1.)May I know would it be a problem, is connection execution provides better performance?
2.)I use the LockType=adLockOptimistic all the time, but I'm not really understand the adLockOptimistic from the explaination it is stated->other users can access the record until changes are actually committed, but i'm not really understand, can you provides an example?

Problem two

I really hope that you can help me on this problem which I have posted in this forums:
that is [webmasterworld.com...] please see on the longest message I have posted.

Thank so much!

[charonlee]

Sorry, mistake on the code given, should be like that

//If Not objRS.EOF Then
objRS.AddNew
objRS("ID") = ID
objRS("Name") = Name
objRS("Job") = Job
objRS("Dprt") = Department
objRS.Update
Response.Write ("Record Updated!")

[aspdaddy]

> use recordset object instead of connection object no matter I select/update/delete

That can create a lot of unnecesssary problems, You only need to create recordsets for select.

[charonlee]

thanks aspdaddy.
duckhunter,

I hope that can get reply from you for my problem one and two with the answer I needed.

I found out that the Transaction Only allow on connection object. if for recordset, we can use batchUpdate and cancelbatch to replace the begin and commit transaction

[duckhunter]

1.)May I know would it be a problem, is connection execution provides better performance?

* Using the connection object to set the transactions and run a statement like "Insert Into ....." rather than the .addnew will increase performance and reduce table locking issues

2.)I use the LockType=adLockOptimistic all the time, but I'm not really understand the adLockOptimistic from the explaination it is stated->other users can access the record until changes are actually committed, but i'm not really understand, can you provides an example?

* adLockOptimistic will allow other users to run select queries until the change is commited. (ie: they will see the OLD data if you are updating) If you are inserting a row and another process tries to select it, it will not be there until your insert operation is complete.

I would use the Connection.BeginTrans approach as aspdaddy says, it reduces your problems.