.Net Framework, Best practice to lock DB SQLServer records in VB.NET application

microsoft.public.dotnet.framework - Microsoft .NET technology


.Net Framework >> Best practice to lock DB SQLServer records in VB.NET application

by pnmm » Mon, 16 Aug 2004 23:11:20 GMT

Hi all;


I wonder if you have an opinion about this matter:

Besides using "Version Number Approach" and "Saving All Values
Approach"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskPerformingOptimisticConcurrencyChecking.asp

suggested by MS for solving lock problems, is there any other options
to do this? In my opinion there's one limitation in these methods: if
you loose your connection to your db for some reason while comitting
the transaction, how can you be sure the lock is in the rigth state?

Can you use threads to lock and unlock records? If so, can someone
point me to some kind of a sample or tutorial to learn more about it?


Urgent!

Thanks in advance!
Top

.Net Framework >> Best practice to lock DB SQLServer records in VB.NET application

by David Browne » Mon, 16 Aug 2004 23:16:02 GMT



http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskPerformingOptimisticConcurrencyChecking.asp

What do you mean? These are methods of optimistic concurrency. They don't
create locks in the database. Rather they reject updates of previously
updated rows. If you try to update a row which has been updated by another
connection since you selected it from the database, you get a conncurrency
exception and you must handle it in the application.

David
Top

.Net Framework >> Best practice to lock DB SQLServer records in VB.NET application

by Pedro Monteiro » Tue, 17 Aug 2004 02:52:29 GMT


I understood you David, but is that the best way of locking records? And
what about using threads instead? Is it a good idea? If so, how can it
be implemented?

Thanks in advance!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Top

.Net Framework >> Best practice to lock DB SQLServer records in VB.NET application

by Warnat » Tue, 17 Aug 2004 14:17:58 GMT

Hi Pedro,


the point is:
there are NO locks on the database to acomplish concurrency situations
with "Version Number Approach" or "Saving All Values"
because:
"Saving All Values" means all datacolumns are checked to be the original
values before updating.
and "Version Number Approach" means:
the timestamp is unmodified by other users/other threads.

remeber: there are no locks on the db using default ADO.NET behaviour

you can work disconnected (lose your connection by reason)
or work in multiple threads etc.

after reconnecting with your db, filling your adapter and merging your
changes, the update-process will only success, if there have been no
modifications to your data (wich would be a concurrency situation).

hope this brings some light into locking behaviour by ADO.NET

Ralf



"Pedro" < XXXX@XXXXX.COM > schrieb im Newsbeitrag

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskPerformingOptimisticConcurrencyChecking.asp
Top
Similar Threads

1. Best practice for locking records (VB, ADO and SQL)

2. Best practices for a handheld application for a visual basic .net application 

Hi,

I am working on .net compact framework application already develop by
other person, before developing any new tasks in the application, I
want yo ensure that it conforms to Best practices for a handheld
application for a visual basic .net application.

For that I searched msdn for a sample project so that i can have a
look at the coding standard used in the CF application. I was unable
to find a simple sample project.

Anybody is aware of some sites from where i can get valuable
information or some sample project please do let me know about it.

Any information provided will be greatly appreciated.

Thanks in advance,

Debu

3. best practices for secure ASP.NET SQL application - Asp.Net

4. Recover from record locking error - VB to Access db 

I'm a self taught newbie who wrote a program (See code 
below) that parses data from a text file and adds records 
to several open Access tables. Usually several hundred 
recs are added to the various tables, one at a time.

The main proc opens the file & reads data up to a 
delimitor.  I'm opening the db and tables in main I leave 
the tables open until EOF; thinking this will cut down on 
overhead of opening/closing tables???

Main then calls a sub that parses the segment into fields 
& decides which table to updatee.  This sub Then calls a 
sub to add a single record.  

Occasionally, I get a 'record locked' error.  I added 
error handling in the sub that adds the rec, to keep 
retrying until the record became unlocked.  My code is 
Wrongo!  I get stuck in a loop.  Any tips on how 
to 'unlock' the record and retry the addnew?  Is there 
some timing issue between my vb code & Access??

Thank you in advance!

Code snippet from main:
.....some code
Set dbeeob = New adodb.Connection
dbeeob.Open strConnString

Set rsclaim = New adodb.Recordset
rsclaim.Open "tbl_claim_level", strConnString, , 
adLockOptimistic

Set rssvce = New adodb.Recordset
rssvce.Open "Tbl_Service_Level", strConnString, , 
adLockOptimistic

Set rssvcadj = New adodb.Recordset
rssvcadj.Open "Tbl_Service_Level_Adjustmts", 
strConnString, , adLockOptimistic
.....more code
End Sub

Public Sub SaveSvcRec()
    'save service level fields to an Access Database 
recordset
    
    On Error GoTo SaveSvcRec_Err
AddService:
    rssvce.MoveLast   'added to see if it would help!
    
    rssvce.AddNew
    field1=variable1
    field2=variable2
    ....
    field15=variable15
         
    rssvce.Update
  
    ClearSvcVariables   'initialize the service level 
variables
    Exit Sub
    
SaveSvcRec_Err:
       Debug.Print "Error in SaveSvcRec: " & Err.Number 
& " " & Err.Description
    
    If Err.Description Like blah blah blah Then
         Resume Next
    ElseIf Err.Description Like yah yah yah Then
        do something
        Resume Next
    
    ElseIf Err.Description Like "*" & "locked" & "*" Then
        MsgBox "Record " is locked, trying again."

        rssvce.CancelUpdate
        Err.Clear
        Resume AddService  'go back to label and try again?
            
    End If
    
End Sub

5. Best practice for store & retrieve images in a SQL Server DB

6. Locking some rows on a SqlServer db 

Hi there,

is there a (simple) way to lock some rows in a Sql Server db table from 
a C# application?

Thanks,
Giulio
--

7. MIcrosft SQLServer Best Practices document on securing SQLServer

8. .net and DB Data layer : Best Practice 


I am converting an app. from PHP to .net.  I know that you can create
an xsd and then have the system generate code to load data into a
dataset.  I assume that you use this and an a data adapter to talk to
the DB.  

However, is this the "optimal" or recommended way to add, update, etc.
information?  Or should one create code to accept the data and then
pass it to a stored procedure?

I do not dislike the adapter way, but I can see that it could
introduce problems and the programmer can loose control which may
cause errors or data integrity violations.

Comments?

Thanks