Locking Hints

[tim]

I am really new to ASP.NET and I was wondering if most developers use
locking hints when accessing the data in SQL Server. What kind of
multi-user issues come up when using an ASP.NET application?

 

[Alexey Smirnov]

I am really new to ASP.NET and I was wondering if most developers use
locking hints when accessing the data in SQL Server. What kind of
multi-user issues come up when using an ASP.NET application?

In most cases, I think, the problem will be in the database design.
However, some ASP.NET "related" things, like leaking connections,
round-trips and server controls may cause a bottleneck. A server
controls can be a problem in some situations, e.g. the DataGrid
minimises the development, but its paging function requires all of the
data to be bound.

 

[GroupReader]

In general, there are "multi-user issues and considerations" when
using ASP.Net.... and there are "multi-user issues and considerations"
when using SQL Server. ASP.Net and SQL Server are two different
products and you need to consider multi-user issues for each.

SQL Sever:

- Usually don't need to use locking hints. In general not
recommended. If it turns out that you do diagnose and discover that
you do have "bottlenecks" or "deadlocks" then you might start using
locking hint to help resolve them. For really common read-only
queries, I do sometimes like to issue the "(with nolock)" statement to
ignore locks and just return the data.

- The main multi-user issue you have is that you need to decide the
business rules of how you want to handle the situation when two people
try to update the same record. Is it okay that the last one wins?
What if: Person 1 reads a record, then Person 2 reads and updates
that record, then Person 1 finally updates the record. Changes from
Person 2 are blown away. Is that okay? Sometimes yes, sometimes no.
Depends on the business logic.


ASP.Net:

Types of multi-user issues here are when using shared memory such as
global state variables stored in the "application object" or "cache
object" or "session object". Make sure one thread is not updating
while another thread is reading. Better: make sure each variable is
accessed by only one thread at a time. This can be a real problem
when using session variables in conjunction with multiple AJAX
threads.

A different type of multi-user issue is when you put multiple
instances of the same "User Control" on one page. Make sure they can
co-exist and that all variables are uniquely identified.

Hope that helps.