ADO Transactions with SQLSERVER(??)

[amolt]

Hi All,
I designed an client/server application which updates and reads data from and to server.

I am having a big problem in transactions, The Code is :

Provider Used to open connection with SQL Server 2005 : sqloledb

Private Function UpdateRemoteDB()
On Error GoTo ErrHnd
    Dim Con as New ADODB.Connection
    Dim rsSQL as New ADODB.Recordset

    Con.CursorLocation = adUseServer 
    Con.ConnectionString = SQLCONNECTIONSTRING
    Con.Open

    'Later update each record from local database to server
    
    Do While not rsLocalRecord.EOF = True
             
             Con.BeginTrans

             rsSQL.CursorLocation = adUseClient
             rsSQL.Open "SELECT * FROM MyTable WHERE ID = '11'", _
                             Con, adOpenKeyset, adLockOptimistic

             rsSQL("Field1") = rsLocalRecord("Field1")
             rsSQL("Field2") = rsLocalRecord("Field2")
             rsSQL("Field3") = rsLocalRecord("Field3")
             :
             :
             rsSQL.Update
             rsSQL.Close

             'I need to do some other processing here. 
             If OtherFunction() = False then
                  GoTo ErrHnd
             End If

             Con.CommitTrans
    Loop
    Exit Function
ErrHnd:
    Con.RollbackTrans
End Function

On other Client End I just displayed all the updated records from SQL Server
with "SELECT * FROM MyTable" and show them to grid.

The main problem above is while my first client is in transaction till the CommitTrans is not executed my other client cannot displayed the results for query.

What should I can do so that the Concurrent Updates are possible!!!!!!

I read about Connection Properties in MSDN so tried likewise
Con.Mode = adModeShareDenyNone
Con.IsolationLevel = adXactReadCommitted
But nothing really happens !!!!


Thanks !!!!!!!!

[si_the_geek]

That behaviour is basically expected, and despite the annoyance is a good thing - the records are being changed, so showing them in a mid-way state would be misleading.

What you should do is keep transactions as brief as possible, so that the delay is small enough to ignore.


In the code you posted there are two obvious ways to speed it up, the first is to not use a recordset for updating records - instead use a Insert statement, preferably via a Command object. For examples, see the article How can I add a record to a database? from our Database Development FAQs/Tutorials (at the top of this forum). For the way to use a Command object with a loop, see here.

The other issue is the way you declare the object variables, which slows down all of the usage of them - I would recommend reading the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)


I have no idea what the Do/Loop is about (as you don't do a .MoveNext etc), or what is in OtherFunction, but there is a good chance that they could be improved too.

[szlamany]

Are you really just updating a single record in each iteration of that loop?

If that is the case then there is a much better way to accomplish this without holding transactions open in the client!

[dee-u]

Basing on your sql I think it can be done with one action query or that you may try to modify it like this..

Con.BeginTrans
Do While not rsLocalRecord.EOF = True
             
             

             rsSQL.CursorLocation = adUseClient
             rsSQL.Open "SELECT * FROM MyTable WHERE ID = '11'", _
                             Con, adOpenKeyset, adLockOptimistic

             rsSQL("Field1") = rsLocalRecord("Field1")
             rsSQL("Field2") = rsLocalRecord("Field2")
             rsSQL("Field3") = rsLocalRecord("Field3")
             :
             :
             rsSQL.Update
             rsSQL.Close

             'I need to do some other processing here. 
             If OtherFunction() = False then
                  GoTo ErrHnd
             End If


    Loop
Con.CommitTrans

[szlamany]

I was going to suggest an action query - but need to wait for clarification on what the whole loop is doing.

Locking is a bad idea from the client - always - as is being experienced by the OP now.

[amolt]

Hi all,
Thanks for all your replies ,,,
The problem is resolved ...

The flow for my application is ..
1) First Client
Takes criteria from user and updates Server database for specific user from local Access database.

2) Second Client
Displays all the records that was updated for specific user in grid.
When user selects some or all of records from grid, it just updates the local Access database for this client and based on that records do other local processing.
On updating local database successfully the server records are updated with a status value one by one.

For this purpose I need to use transactions ... so as if the local processing fails my records cannot updated to local database as well the server records also not get updated with status value.
Thats why I iterate through recordset and update local database for each record ... do local processing based on that record which takes time ... on success the server record is updated and both transactions are commited.

As I am in need for the server thing first I post a small code here.
The recordset is opened for this purpose only and hence I do not use any action querys as my only recordset does all things.

The thing is when First Client updating one record to table, the Second Client was not able to retrieve other rows which was committed, to overcome this thing I simply used WITH(READPAST) option in query and everthing is solved.
But now I am undergoing with SQL Server Error Disconnected from server more frequently. I cannot find the way how is related with code as it appear many times after I done with this way.


Now the Second Problem I am having with Local Access Database.
as I am updating local database, if any local process is working at that time it seems that the whole table is locked? Could someone tell me How should I modify my local access to work with more than one process or updates?
It seems to be really hard to get a way out from it, I setup JET Properties as
bellow to both processes.

con.Provider = "Microsoft.Jet.OleDB.4.0"
con.Mode = adModeShareDenyNone
con.Properties("Jet OLEDBatabase Locking Mode") = 1

I need both of my processes working that is my updating process from server to local database and other any local process updating local database ?


Thanks!!

[brin351]

On your 2nd question Access (being file based) can not have 2 process updating the same record at the same time.

[amolt]

Hi brin,,

I am not updating same record at the same time but updating same table at the same time, in which two different records are updating.

[szlamany]

Stop using transactions.

Stop getting a recordset to perform a simple update.

Start using ACTION QUERIES.

strSql = "Update MyTable Set Field1='" & rsLocalRecord("Field1") _
                        & ", Field2='" & rsLocalRecord("Field2") _
.
.
.
                        & " Where Id='11'"

Build that string and then use .Execute on your connection object to run it.

This requires no lock - no transaction - no hold on the DB.

That's a more proper way to update records in my opinion.

[amolt]

Hi szlamany,
You are correct to use action querys in simple updates.
But its my need to rollback the transactions too.
Also if I use simple updates the record which is updated and for which the local process is still running, have been seen by the client and thus lead to misbehaviour.

Regds,

[szlamany]

Then there is an answer to that issue as well.

Most people don't want to think set-based - it's so much easier to program in an iterative fashion.

But to truly utilize SQL you must always be set-based.

The simple set-based solution to your problem is to load a temp table with the new data first. MS SQL offer bulk insert options - both at command line and also part of the SQL Client - to get that data loaded in one shot.

Once you get the data into a temp table you can do a single UPDATE statement to process all rows.

We do this in literally hundreds of places - loading all kinds of data in these ways.

Expecting to use transactions to manage errors and then do rollbacks is not appropriate.

You should be creating temp tables.

Then you could delete the rows that aren't good to load.

Then you could do the UPDATE all in one step - syntax would be something like this.

Update MyTable Set Column1=TT.Column1, Column2=TT.Column2
   From #TempTable TT
   Left Join MyTable MT on MT...=TT...
   Where {some great condition statement to make sure the UPDATE will not fail}

Taking data into VB to be processed one row at a time in a multi-user environment is a poor choice when you have other set-based "single" shot ACTION QUERY abilities that fully respect set-based logic.

If you want assistance with doing this in a set-based fashion step back and tell us what you are trying to actually achieve. What the source of the data is - a couple of columns of sample data - and you will be amazed at how easy this can be done without looping through rows in VB and causing deadlock issues with your database