Re: pessimistic locking

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 16:08:41 -0500

Preeta wrote:
> Can you pls tell me what lock iam supposed to use?
>
> "David Gugick" wrote:
>
>> Preeta wrote:
>>> I need to place a lock exclusively,because i don't want two people
>>> updating the same row at the same time without knowing what changes
>>> the other user has made.My software requires this.
>>>
>>> "Tom Moreau" wrote:
>>>
>>>> By default, there will be an exclusive lock placed on the row when
>>>> you update it. You do not have to do anything to make this happen.
>>>>
>>>> --
>>>> Tom
>>>>
>>>> ---------------------------------------------------------------
>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>>> SQL Server MVP
>>>> Columnist, SQL Server Professional
>>>> Toronto, ON Canada
>>>> www.pinnaclepublishing.com
>>>>
>>>>
>>>> "Preeta" <Preeta@discussions.microsoft.com> wrote in message
>>>> news:27BC4F77-9DB8-4AB1-88DA-670C5838E7BE@microsoft.com...
>>>> i have a table A with a field B
>>>> iam updating a row say where B = 1
>>>> when iam updating this row i do not want another user updating
>>>> this row at the same time.i want to place a lock only for updating
>>>> and only on this row. the other users must be able to update
>>>> other rows which are not equal to 1.
>>>>
>>>> i asked this question before and got very good answers for
>>>> it.Forgive me for asking again,"is there a lock in SQL to do this,
>>>> if so is it wise to use locks?" Because i think locks can be
>>>> dangerous sometimes.Please reply.Thanks.
>>
>> Put a TIMESTAMP column on the table. Select the timestamp when you
>> query the data and in your stored procedure that updates the data,
>> check to make sure the timestamp hasn't been changed while the user
>> was editing. If it has, raise an error and have the application put
>> up a meaningful message and requery the data. Leaving locks on the
>> database is a surefire way to kill performance across the board
>> since any user that needs to query that row will be blocked while
>> the extended edit is going on.
>>
>> If you really are convinced you want to lock the row, see ISOLATION
>> LEVELS in BOL for more information. More specifically, you'll need
>> either one of these:
>> REPEATABLE READ
>>
>> Locks are placed on all data that is used in a query, preventing
>> other users from updating the data, but new phantom rows can be
>> inserted into the data set by another user and are included in later
>> reads in the current transaction. Because concurrency is lower than
>> the default isolation level, use this option only when necessary.
>>
>> SERIALIZABLE
>>
>> Places a range lock on the data set, preventing other users from
>> updating or inserting rows into the data set until the transaction is
>> complete. This is the most restrictive of the four isolation levels.
>> Because concurrency is lower, use this option only when necessary.
>> This option has the same effect as setting HOLDLOCK on all tables in
>> all SELECT statements in a transaction.
>>
>>
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com

Preferably neither as I stated. BOL is clear to point out that these
options should only be used infrequently and also states that
serializable is more restrictive than repeatable read. But both are
restrictive in their own right and neither are desireable in a
production application. Use a timestamp instead. It's much better for
overall performance and it will save you a lot of headaches.

How are you going to prevent an application from leaving these locks on
the server should a user leave the edit screen open at lunch or at the
end of the day? What provisions are you going to make to find and kill
these users should they leave locks on the server? How are you going to
monitor performance ongoing to make sure these problems do not appear
during the work day? Are you prepared to lock everyone out from reading
data on those locked rows/pages while these extended edit are going on?
How is your appliction going to deal with extended blocking issues? Are
they running queries asynchronously so users can cancel a query when
they are locked by someone editing a row? Are you going to set a
lock_timeout for each connection and roll back queries at the client if
an attention event should be raised in response to a lock timeout
triggering? These are all things that should be considered and may need
to be architected into an application before using strict isolation
levels. Remember, this is not the dBase world where users would take
rlocks() on their data in order to edit.

Why not use a timestamp and avoid all this? It gives you the same
protection with just a little bit of coding in the stored procedure and
causes non of the performance problems these isolation levels are surely
to cause. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: Threading in Order?
    ... thread B then thread A's message goes in the log first do something like this (pseudo code): ... Just because thread A calls the LogAMsgmethod before thread B does, that doesn't mean that thread A will acquire the lock before thread B; it could happen to get pre-empted just before acquiring the lock, with thread B running immediately after and acquiring the lock before thread A. ... The best one can do is not generate a timestamp until the lock has actually been acquired, but then the timestamp winds up being not the time of the event being logged, but the time that the event _is_ logged. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Rowversion and locking
    ... can not lock it to any level above shared. ... right now it is the same as timestamp, ... with rowversion. ... > As for the trigger... ...
    (microsoft.public.sqlserver.programming)
  • Re: [PATCH] perf lock: Drop "-a" option from set of default arguments to cmd_record()
    ... injection will be really helpful thing. ... I think that making 3 or 4 events per each lock sequences ... timestamp of itself+ lock addr ... whole lock sequence, like in my above lock_contended example, ...
    (Linux-Kernel)
  • Re: [PATCH] perf lock: Drop "-a" option from set of default arguments to cmd_record()
    ... injection will be really helpful thing. ... >> I think that making 3 or 4 events per each lock sequences ... > by dependencies (take lock A and then take lock B under A, ... timestamp of itself+ lock addr ...
    (Linux-Kernel)
  • Re: unique id
    ... but how can i lock it so no one straight after can grab the same value ... If you need a timestamp, then store the timestamp as well. ... considered bad design practice to have one database field serving two ...
    (alt.php)