I am developing VB.NET application which will be multi user application - around 50 users and every user will be using hos own copy of it. I am now investigating topic about multi connection to sql server database and get info that only one user login can be in connection string and every user with his application can use same so that this itself will not raise any block/lock towards db when other user using same connection string credentials.
Now considering there is another thing which makes me thinking and it is data integration. By that read when one user is doing something with data and other user uses it how to avoid data integrity issues? Does transactions solve the problem? Let's take example when one user deleting record and second is playing with those data or at the same time both doing those operations. Should i make queries also with transaction or maybe particural query already usin transaction and only multi queries should be in transaction. However will it be enough?
Looking for your significant info back.
Sql Transaction locks the table by default until and unless you explicitly tell it not to lock with
with(nolock) or until the
rollback. That means no other user will be able to access the table during the
Transaction if you did specify it's isolation level with
And Yes again use of
Sql Transaction is recommended.
When you are performing one of the
CRUD(Create,Read,Update,Delete) operation on a particular
transaction, no one except you will be allow to access that table until and unless your
transaction is complete(Commit or Rollback).
There are functions that allow others to access the table even with during the transaction(by setting ISOLATION LEVEL, using
WITH(NOLOCK)) like I said.