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

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!

David
Mon Aug 16 10:16:02 CDT 2004


"Pedro" <pnmm@portugalmail.pt> wrote in message
news:9f72c57b.0408160711.38d78d0d@posting.google.com...
> 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?
>

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

Warnat
Tue Aug 17 01:17:58 CDT 2004

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?

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?
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


> 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?

"Pedro" <pnmm@portugalmail.pt> schrieb im Newsbeitrag
news:9f72c57b.0408160711.38d78d0d@posting.google.com...
> 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?