Re: select during transaction**
From: Uri Dimant (urid_at_iscar.co.il)
Date: 07/05/04
- Next message: N: "Re: IRR calculation"
- Previous message: Pablo: "RE: select during transaction**"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 5 Jul 2004 11:33:56 +0200
RM
What kind of indexes do you have ?
create an unique non-clustered index on bookid.
--1 connection
begin tran
update books set book_desc='mathematical'
where book_desc='psychology'
---2 connection
select * from books ---you will be waiting till the first connection to
release its lock
select bookid from books -----This time, you won't be kept waiting
See execution plan for the query.
information that you asked for could be answered by reading an index page
and does not need to read the datapage
this kind of query called as covering query
But in your case your WHERE condition contains book_id=2 therefore it will
be locked if you have an index on bookid column
"RM" <m_r1824@yahoo.co.uk> wrote in message
news:opsanmzrwshqligo@msnews.microsoft.com...
> 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....
>
> 2)select * from books where book_id=1
>
> result: OK
> book_id book_desc
> ---------- ------------
> 1 physics
>
> 3)select * from books where book_id=3
>
> result: OK
> book_id book_desc
> ---------- ------------
> 3 psychology
>
> 4)select * from books where book_id<2
>
> result: OK
> book_id book_desc
> ---------- ------------
> 1 physics
>
> 5)select * from books where book_id>2
>
> result: OK
> book_id book_desc
> ---------- ------------
> 3 psychology
> 4 culture
>
> 6)select * from books where book_id<>2
>
> result: query is in working....
>
>
>
>
>
>
> any help would be greatly thankful.
>
- Next message: N: "Re: IRR calculation"
- Previous message: Pablo: "RE: select during transaction**"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
