Re: Locking in SQL Server

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 11:21:48 +0200

> By any chance is any of you knows any web site - document from
> which I can prove that it is not possible

If your manager understand locking, he/she will understand why it isn't possible. If your manager
doesn't understand locking, then he/she will not understand such a "proof" either. I haven't seen
such a proof explicitly defined, and I fail to see how you would phrase such a statement. You can
point your manager to Books Online and ask how your manager proposes this to be implemented.

Btw, you are using the term "Deadlock" incorrectly in your original post. What you see there is a
blocking. A deadlock is a different things (when you have two connections blocking each other) and
such are resolved by SQL Server within a few seconds.

SQL Server 2005 will have snapshot isolation where you work in a freezed time. This means that
others can modify data, but you are seeing what the system looked like at an earlier point in time. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sansaw80" <sansaw80@discussions.microsoft.com> wrote in message 
news:00A6F807-D518-46BC-A7A7-DC9A5B804992@microsoft.com...
> Thanks all for replying to my question. I know all of you are right and what
> I want to do is fundamentally not possible. Even when I put this question I
> was 90%sure it is not possible but my PM thinks, it is possible (How? He
> doesn't know). By any chance is any of you knows any web site - document from
> which I can prove that it is not possible. By any chance is this feature will
> be provided in Yukun (SQL Server 2005) any comments?
>
> "sansaw80" wrote:
>
>> Hi All,
>>
>> I got basic question about locking. I want while one user is updating a
>> data, other user should able to read a data (NOT DIRTY DATA). For example:
>>
>> Say User A issues the following command:
>> Use Northwind
>> Go
>>
>> Begin Transaction
>> Update Region with Set RegionDescription = 'South' where RegionID = 4
>>
>> At this stage User has not closed the transaction. That means User A either
>> Commit or Roll back.
>>
>> At the same time User B issues the following command: (When User A
>> transaction is open)
>> Use Northwind
>> Go
>>
>> Select * From Region
>>
>>
>> In this case User B goes into Deadlock situation. But what I want is User B
>> should able to read the data from the table but data before Update not Dirty
>> data.
>>
>> I tried using UPDLOCK with Update statement but found out User B command
>> still goes in Deadlock situation. Only way I could able to execute User B
>> command was use either NOLOCK or READPAST. But none of them serves my purpose.
>> NOLOCK - gives me Dirty data and READPAST - ignore lock record and shows
>> rest of the data.
>>
>> Please can anyone tell me is there any way to solve this problem. Or am I
>> doing something wrong here?
>>
>> Many thanks in advance.
>>

Relevant Pages

  • Re: Locking in SQL Server
    ... > But I think main problem is, this is his first proj on SQL server and MS ... >> If your manager understand locking, he/she will understand why it isn't possible. ... >>>> Say User A issues the following command: ... >>>> In this case User B goes into Deadlock situation. ...
    (microsoft.public.sqlserver.programming)
  • Re: ADO connection options
    ... I don´t know of any settings to manipulate this. ... You only chance for ... this is to put the command in your batch fired against the SQL Server. ...
    (microsoft.public.sqlserver.connect)
  • Re: [TCPIP V5.4] Session Disconnects (and DISCONNECT)
    ... This will abort all connections to the 10.10.10.10 address on interface ie0. ... Is there any chance that UCX will narrow this gap> in the very near future? ... Otherwise I will> have to write a DCL script instead of using one simple TCPIP command... ...
    (comp.os.vms)
  • Re: CD-blanking leads to machine freeze with current -git [was: Re: CD writing in future Lin
    ... The problem is, it's a laptop. ... So there not much chance to move the cdrom device ... The IDE controller can only accept one command at a time, so if that command takes a while to complete, the other drive on the same channel can not be accessed until the first command completes. ... If the system doesn't come back though after sufficient time has gone by for the burn to complete, then this is probably not what is happening. ...
    (Linux-Kernel)
  • Re: [opensuse] Spyware on my laptop? (hope its not OT)
    ... Hmm, well, i used that domain on my lan before i get internet at home ... :S Changed it to my ISPs domain, is there any command to flush the ... Change to a domain that has no chance of existing at all, ... Notice that what you put in the resolv file will be appended to non found ...
    (SuSE)