Atomic read/writes in SQL Server

10 Jul 2009 12:08 AM

JohnB

I'm about to start working on an (Internet) web server whose processing is
pretty routine (note that all users with be anonymous). Web forms are
read/writing to the (SQL Server) DB and everything is hooked up using
standard ASP.NET techniques (web controls wired to my business layer objects
calling into the DB using my data layer objects). Do I need to deal with
mutexes or critical sections for updating single records from a given table
or even single records in different tables? I'll be applying transaction
processing for the latter case and optimistic concurrency in general but
what about two users trying to read/write at the same time? Can they clobber
each other in any way I need to be aware of. What about cascading updates
and deletes? Thanks for your help.

10 Jul 2009 2:06 AM

sloan

Google

ASP.NET Optimistic Locking (which will usually go with timestamp/rowversion
solutions)
ASP.NET Pesstimistic Locking

There are a few ways. I still rely on TSQL BEGIN TRAN/ COMMIT TRAN /
ROLLBACK TRAN for my ASP.NET applications.

I would get the EnterpriseLibrary.Data (3.1 or 4.1) and call stored
procedures.
But that's me, there are different methods.

If you have Sql Server 2008, you can also look at the UPSERT/MERGE commands.
http://pratchev.blogspot.com/2008/03/upsert-and-more-with-merge.html

Show quoteHide quote

"JohnB" <_nospam@_no_spam.com> wrote in message
news:%23fTAaKPAKHA.4336@TK2MSFTNGP04.phx.gbl...
> I'm about to start working on an (Internet) web server whose processing is
> pretty routine (note that all users with be anonymous). Web forms are
> read/writing to the (SQL Server) DB and everything is hooked up using
> standard ASP.NET techniques (web controls wired to my business layer
> objects calling into the DB using my data layer objects). Do I need to
> deal with mutexes or critical sections for updating single records from a
> given table or even single records in different tables? I'll be applying
> transaction processing for the latter case and optimistic concurrency in
> general but what about two users trying to read/write at the same time?
> Can they clobber each other in any way I need to be aware of. What about
> cascading updates and deletes? Thanks for your help.
>

10 Jul 2009 10:06 AM

Erland Sommarskog

JohnB (_nospam@_no_spam.com) writes:

> I'm about to start working on an (Internet) web server whose processing
> is pretty routine (note that all users with be anonymous). Web forms are
> read/writing to the (SQL Server) DB and everything is hooked up using
> standard ASP.NET techniques (web controls wired to my business layer
> objects calling into the DB using my data layer objects). Do I need to
> deal with mutexes or critical sections for updating single records from
> a given table or even single records in different tables? I'll be
> applying transaction processing for the latter case and optimistic
> concurrency in general but what about two users trying to read/write at
> the same time? Can they clobber each other in any way I need to be aware
> of. What about cascading updates and deletes? Thanks for your help.

SQL Server will take care of locking, so two users cannot update the same
row simultaneously, nor can a user read a partially updated row or anyhing
like that. If you perform updates to several tables that need to be atomic,
you need to define transactions for those.

Still there are some tricky parts: what if user1 reads a row, user2 reads
the same, and user1 then updates, and next user2 updates but from stale
data. This is usually dealt with optimistic concurrency, for instance with
timestamp (a.k.a rowversion) columns.

There are also more intricate scenarios where you need to make sure that
no one changes the data you have read, but they are less common.

Mutexes or critical sections is nothing you should use. Not the least
because they are client-side devices, so they will not protect you from
what other client processes do.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx