Command Object vs Connection Object for Row Level Locking 
Author Message
 Command Object vs Connection Object for Row Level Locking

Hi All,

I am trying to implement the row level locking.

I can lock the record successfully by using the connection object by the
following code:

<-----------------Start-------------------->
With ADOcon
        .Provider = "MSDASQL"
        .ConnectionString = "DSN=ABC;UID=ABC;PWD=ABC;driver={SQL Server}"
        .IsolationLevel = adXactIsolated
        .Mode = adModeShareExclusive
        .ConnectionTimeout = 5
        .Open
        .Execute "SET LOCK_TIMEOUT 0"
End With

With rs1
    .ActiveConnection = ADOcon
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic
End With

ADOcon.BeginTrans
LocalstrSel = "select * from TBL_A" & "  where UniqueNo = '" & UniqueNo &
"'"
rs1.Open LocalstrSel
<-----------------End---------------------->

However, I found that I can only lock the record for the scope of ADO. When
other user
don't use ADO, like using command object to call stored procedure to select
the same record, they can!

On the other hand, I found that I can use the same method (IsolationLevel )
for command object to lock
the records. As I know, command object haven't the IsolationLevel property,
so anyone know how to
do row level locking for command object with stored procedure?

Anyways, anyone can tell me any method to implement the row level locking in
Microsoft environment (COM+
by using ADO with SQL server)? thx !

Br,
Rayman



Sat, 23 Oct 2004 10:08:59 GMT  
 Command Object vs Connection Object for Row Level Locking
    You may want to run a profiler trace to see what statements the provider
is sending to better understand the locking specifications. Note that
without actually updating the row, you can't really lock the row from being
read by another connection. It depends on what isolation level the rest of
the queries use etc. In the case where every connection is using read
committed, then these hints will also not help.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Sat, 23 Oct 2004 14:56:11 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Command Object vs Connection Object for Row Level Locking

2. Command Object vs Connection Object for Row Level Locking

3. DataLink Objects vs Connection Object

4. DataLink Objects vs Connection Object

5. DataLink Objects vs Connection Object

6. ADO Command Object using Multiple Connection Objects

7. Connection String vs. Connection Object

8. Move ADO Connection object to Applictaion level?

9. Reuse application-level connection object

10. Remote Data Objects in combination with level 1 compliance level

11. Access 97 Row-Level Record Locking

12. row-level locking with dao and access 2000

 

 
Powered by phpBB® Forum Software