Re: Isolation level question
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 12/01/04
- Next message: Aaron [SQL Server MVP]: "Re: how to exclude weekends from a datediff?"
- Previous message: avnrao: "Re: Isolation level question"
- In reply to: Ed: "Re: Isolation level question"
- Next in thread: Mal .mullerjannie_at_hotmail.com>: "Re: Isolation level question"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 30 Nov 2004 22:01:47 -0800
Hi Ed
HTH = Hope this helps :-)
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Ed" <Ed@discussions.microsoft.com> wrote in message news:F0E67124-9AB6-4209-B249-8F9DD12B2928@microsoft.com... > HTH, > You are exactly correct... I always enjoy seeing your post... > Ed > > "Kalen Delaney" wrote: > >> 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 >> > >> > >> >> >>
- Next message: Aaron [SQL Server MVP]: "Re: how to exclude weekends from a datediff?"
- Previous message: avnrao: "Re: Isolation level question"
- In reply to: Ed: "Re: Isolation level question"
- Next in thread: Mal .mullerjannie_at_hotmail.com>: "Re: Isolation level question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
