Re: Isolation level question

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 12/01/04 

Date: Tue, 30 Nov 2004 19:35:59 -0800

Hi Vincent

Your understanding of read uncommitted is incorrect. Read uncommitted does
not take SHARE locks and it ignores EXCLUSIVE locks held by other processes.
But you are getting an EXCLUSIVE lock when update the row where nunber = 1.

The behavior you see happens because there are no indexes on your table, so
the only way the second connection can find the row where number = 10000 is
to scan the whole table. It gets to the first row, which is locked, and it
can't tell if that is a row it needs to return or not, so it has to wait.

If you build an index on the number column you should have the behavior you
are expecting. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vicent Palasí" <vpalasi@nospamaurumsol.com> wrote in message 
news:uGKKZX11EHA.2180@TK2MSFTNGP10.phx.gbl...
> Hi.
>
> I am learning about transaction isolation levels. The problem is that I 
> see
> is that one single update (with "read uncommitted") can lock the whole
> table, which does not make sense to me. I explain with an example.
>
> I have a simple table "isolation_test" with two columns "number"(int) and
> "name"(char(20)). I have 10,000 records like this
>
> number        name
> -------        -------
> 1                 Record number 1
> 2                 Record number 2
> 3                 Record number 3
> and so on, until
> 10000         Record number 10000
>
> Now I open two windows in query analyzer, that is, 2 DB connections. In 
> the
> first window I run :
>
> set transaction isolation level read uncommitted
> BEGIN TRAN
> UPDATE isolation_test
>   SET name = 'New value' where number=1
>
> (Please note that the transaction remains unfinished).
>
> In the second window, I execute
>
> set transaction isolation level read committed
> select * from isolation_test where number =10000
>
> And when I run this second window, this window gets stuck (that is, the
> window is waiting and gives no result). Obviously, it is locked because,
> when I finish first transaction in first window (either with COMMIT or
> ROLLBACK), the window stops waiting and returns the rows produced by 
> SELECT
> command.
>
> That is, one single update in one row locks the whole table. I couldn´t
> believe my eyes. This is very weird to me. I have read that "read
> uncommitted" doesn´t lock anything at all and "read committed" allows to
> read. So first window should not lock second window. Please can somebody
> explain this to me? Any information would be greatly appreciated.
>
> Thanks in advance,
>
> Vicent Palasi
>
>

Relevant Pages

  • Isolation level question
    ... set transaction isolation level read uncommitted ... In the second window, I execute ... one single update in one row locks the whole table. ... uncommitted" doesn´t lock anything at all and "read committed" allows to ...
    (microsoft.public.sqlserver.programming)
  • Re: ext3 allocate-with-reservation latencies
    ... > enough as you fill up a block group with small files, ... probably try to skip the block groups without enough usable free blocks ... > No, I'm suggesting that if we need the write lock for tree updates, we ... > individual window. ...
    (Linux-Kernel)
  • Re: ext3 allocate-with-reservation latencies
    ... > what I was suggesting is, if allocation from the home group failed and ... > probably try to skip the block groups without enough usable free blocks ... and using read lock for that should help reduce the latency. ... window, and that's going to have to wait for us to finish finding a free ...
    (Linux-Kernel)
  • RE: synchronizing domain user Local cached credentials with domain (VP
    ... locally cached credentials are out of sync with domain credentials. ... For the technical savvy will lock their screen and logon on ... change (script the detection of the Window bubble, ...
    (microsoft.public.windowsxp.security_admin)
  • Re: Legend C and Vista C sensitivity
    ... > I have had a basic yellow etrex for the last two years and have been happy ... My first GPSr was an eTrex Summit, bought in 2000, on which reception ... (again, near a window). ... lock inside a building, no matter how close you were to a window. ...
    (sci.geo.satellite-nav)