Locking Datasource

[Bob Day]

Using VS 2003, VB.Net, MSDE, ADO.net with disconnected datasets.

I have two applications, AppA and AppB. AppA is a multit thread application
that uses Synclock when accessing a datasource (such as a DataAdapter.Update
command). AppA works fine. AppB is a single thread, but must access the
same DataSouce as AppA while AppA is possibly accessing it also. These are
both winform applications on a desktop Windows XP.

Question 1) - Just considering AppA running by itself, do I need to worry
about locking on the SQL level (i.e. in the actual SQL commands used by the
DataAdapters)? I have skimmed the SQL information, and it looks like I
could create locks on that level in addition to the SyncLock level in the
VB.Net code. It would seem to me that this would not be necessary, that
SyncLock is all I need to do.

Question 2) - Now consider AppA and AppB running at the same time. If AppA
has a datasource open for an Update, If AppB tired to try open it, what
happens? I assume it would either a) fail, b) act in a fashion similar to
Synclock, and wait its turn until the DataSource was availalbe. I am not
sure which, and if it would fail, how to I protect against that? Synclock
in AppB would be of no value (becase it doesn't know what is going on in
AppA).
How, how do I wirte AppB to access a common datasource without problems?
Any URL to sample code is appreciated.

I am not an SQL programmer, so I am hopping you answer does not involve low
level SQL programing.

Thanks!
Bob Day

 

[Scott Allen]

Bob:

In general, all the locking can take place on the database server.
Unless your situation needs transactions, all the locking may be
implicit (that is - you dont need to tell the database when and where
to take locks - it figures this out for itself).

You might want to do a search in the help for "optimistic
concurrency".

I don't have enough details to be sure, but I strongly suspect taking
a lock on the DataSource is not what you want to be doing at all.

 

[Kevin Yu [MSFT]]

Hi Bob,

Thank you for posting in the community! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know two questions. The
first one is do we have to lock on the SQL database level when accessing
data from a multi-threaded app with ADO.NET. And the second one is do we
have to do SQL database level lock when multiple apps are accessing the
same database. If there is any misunderstanding, please feel free to let me
know.

Generally, the answer to both of your questions is "No, we needing worry
about the SQL level locking problem either in a multi-threaded app or in
multiple apps." SQL server / MSDE will do the database level lock
automatically.

In a multiuser environment, there are two models for updating data in a
database: optimistic concurrency, and pessimistic concurrency. The DataSet
object is designed to encourage the use of optimistic concurrency for
long-running activities such as when you are remoting data and when users
are interacting with data. In ADO.NET, when an optimistic concurrency
occurs, we can handle it in the RowUpdated event of the DataAdapter.

Here is a link about Optimistic Concurrency as Scott mentioned in his post.
There is also an code example provided in this article to test for
optimistic concurrency and how to handle optimistic concurrencies.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconOptimisticConcurrency.asp

For a better understanding of locking in SQL Server, please check the
following article in MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_con_7a_7xde.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."