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

  • Thread starter Thread starter Pedro
  • Start date Start date
P

Pedro

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/d...skPerformingOptimisticConcurrencyChecking.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!
 
Pedro said:
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/d...skPerformingOptimisticConcurrencyChecking.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?
Click to expand...

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
 
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!
 
Hi Pedro,
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?
Click to expand...

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.
Can you use threads to lock and unlock records?
Click to expand...
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
 
Back
Top