Re: Concurrency issue - Best Practice wanted

From: Scott Allen (bitmask_at_[nospam)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 10:31:30 -0400

Hi Bob:

This article is a good read:

Handling Data Concurrency Using ADO.NET
John Papa
http://msdn.microsoft.com/msdnmag/issues/04/09/DataPoints/default.aspx 

--
Scott
http://www.OdeToCode.com
On Mon, 23 Aug 2004 14:18:33 GMT, "Robert Schuldenfrei"
<bob@s-i-inc.com> wrote:
>Hi Nick,
>
>OK, I promise NEVER to even CONSIDER locking.  I have two new skills to
>learn:  1/ Transactions and 2/ using timestamps.  I had been using a
>technique of old record / new record to determine if the record had been
>changed on update or delete.  Timestamps seem more elegant.  Sadly, the book
>I have been using for learning SQL is Petkovic, SQL Server 2000, A
>Beginner's Guide.  He mentions timestamps, but then goes forward to
>explaining locking (and not to well I might add).  Can you suggest a good
>book that implements your suggested approach to concurrency that would cover
>both the C# and the SQL issues?
>
>Thank you,
>
>Bob
>
>Robert Schuldenfrei
>bob@s-i-inc.com
>
>
>"Nicholas Paldino [.NET/C# MVP]" <mvp@spam.guard.caspershouse.com> wrote in
>message news:eNU$FeRiEHA.632@TK2MSFTNGP12.phx.gbl...
>> Robert,
>>
>>     Never, ever, EVER use locking.  If you do, you will be commiting your
>> system to death.  It will NOT be scalable, period.  You will hit a glass
>> ceiling in terms of how many operations you can support, and it will not
>be
>> pretty.
>>
>>     While yes, with concurrency checking, you will have to tell the user
>to
>> "try again", it is better than having corrupt data in your database, or
>data
>> that the user enters based on other data that is stale.  It's generally
>not
>> a good thing.  For SQL Server based solutions, I recommend the use of
>> timestamps, and then modifying your SQL on updates and deletes:
>>
>> update <table> set <field> = <value> where id = <id> and timestamp =
>> <timestamp>
>>
>>     The delete follows the same pattern:
>>
>> delete from <table> where id = <id> and timestamp = <timestamp>
>>
>>     The reason you do this is that from code, you can see the number of
>> records affected.  If the number of records affected is zero, then you
>know
>> that either the id does not exist, or that the timestamp has changed, and
>> you can inform the user as such.
>>
>>     Of course, you wrap this operation in a transaction (unless it is the
>> only operation you are performing, since all single statements are
>> serializable transactions in themselves) to ensure that when you touch the
>> record, no one else can either, for the duration of your operation.
>>
>>     Hope this helps.
>

Relevant Pages

  • Re: Concurrency issue - Best Practice wanted
    ... I promise NEVER to even CONSIDER locking. ... 1/ Transactions and 2/ using timestamps. ... I have been using for learning SQL is Petkovic, SQL Server 2000, A ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: row vs page locking...
    ... There was a major rewrite of much of the core database engine code in SQL 7. ... SQL 7 and above do a great job of row-level locking. ...
    (microsoft.public.sqlserver.server)
  • Re: Retrieving primary key of newly added record
    ... key field is an identity/autonumber one in SQL Server - that is SQL ... I'm worried that if I do another explicit query, ... where the dynamic sql was derived using the CommandBuilder object: ... Handling concurrency with stored procs. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Retrieving primary key of newly added record
    ... Please reply only to the newsgroup so that others can benefit. ... key field is an identity/autonumber one in SQL Server - that is SQL ... where the dynamic sql was derived using the CommandBuilder object: ... Handling concurrency with stored procs. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: computational model of transactions
    ... that much of the difficulty lies with the fact that multiple concurrent ... transactions may operate on the same data. ... That is the concurrency problem, ... problem that can not be dealt with simplyistic SQL based solutions. ...
    (comp.databases.theory)