RE: select during transaction**
From: Pablo (Pablo_at_discussions.microsoft.com)
Date: 07/05/04
- Next message: Uri Dimant: "Re: select during transaction**"
- Previous message: Uri Dimant: "Re: Temporary table and procedure - resources"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 5 Jul 2004 01:26:01 -0700
Hi,
"RM" wrote:
> Hi
>
> I'm working with SQL server 2000 and have
> following problrm:
>
> in query analyzer I want to update
> one row of my table called 'books' in
> a transaction
>
> my table structure and data 'book' is :
> book_id(PK) book_desc
> ---------- ------------
> 1 physics
> 2 test
> 3 psychology
> 4 culture
>
> first page(in query analyzer):
> begin transaction
> update books set book_desc='mathematical'
> where book_id=2
>
> but imagine another user from another station want
> to see all the information about books during this changing
> ,which we can try it in another page in query analyzer.
> and I tried it in 6 different types and have got
> different results as followed,why?
> is there any way to see all old information as
> the result of first case below?
>
>
>
> second page(in query analyzer):
> 1)select * from books
>
> result: query is in working....
beacuse sql server decide on clustered index scan, therefore this statement must wait on releasing locks made by UPDATE
I don't know way for showing old data (i think is not possible) but with
select * from books with (NOLOCK)
you can get new data, or
select * from books with (READPAST)
you can get data without locked rows.
>
> 2)select * from books where book_id=1
>
> result: OK
> book_id book_desc
> ---------- ------------
> 1 physics
because sql server chose clustered index seek and it didn't touch/need
the data locking by UPDATE from first window.
>
> 3)select * from books where book_id=3
>
> result: OK
> book_id book_desc
> ---------- ------------
> 3 psychology
>
because sql server chose clustered index seek and it didn't touch/need
the data locking by UPDATE from first window.
> 4)select * from books where book_id<2
>
> result: OK
> book_id book_desc
> ---------- ------------
> 1 physics
>
because sql server chose clustered index seek and it didn't touch/need
the data locking by UPDATE from first window.
> 5)select * from books where book_id>2
>
> result: OK
> book_id book_desc
> ---------- ------------
> 3 psychology
> 4 culture
because sql server chose clustered index seek and it didn't touch/need
the data locking by UPDATE from first window.
>
> 6)select * from books where book_id<>2
>
> result: query is in working....
>
because sql server chose clustered index scan and was waited on locked resources.
When you change statement to
select * from books where book_id>2 or book_id<2
optimizer will choose clustered index seek and query will execute without blocking
>
>
>
>
>
> any help would be greatly thankful.
>
>
Regards,
Pablo
- Next message: Uri Dimant: "Re: select during transaction**"
- Previous message: Uri Dimant: "Re: Temporary table and procedure - resources"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
