URGENT - Problem with read commited isolation level

Question

Post reply

URGENT - Problem with read commited isolation level

spaliwal

SSChasing Mays

Points: 604
More actions
January 13, 2004 at 4:21 am

#106728

Hi Gurus,
I am using default READ COMMITED isolation level . While inserting a single
row in a table it is now allowing to read any row from the table. As i know
it uses row level locking then why it is now allowing to read rows which are
already commited ?
say table name ABC with code, Name coloumns
existing rows are -
01 Tom
02 Jerry
03 Mouse
Say in a session i am giving and it is not commited yet
INSERT INO ABC values ('04','Mickey')
and in another session Immediately i give
SELECT * from ABC where Code = '01'
It wont return any row for 01 even it is already commited
locks on the table are
RID X
Page IX
Table IX
Database S
Immediate help is greatly appreciated .
TIA ,
Sheilesh

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 1

Do you have an index on column Code ?

If not it will have to perform a table-scan and therefor it will not be able to read through the uncommitted row(s).

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

spaliwal SSChasing Mays Points: 604 More actions · Answer 2

Oops , My question is wrong - Pl. read the below one text 'now' is changed to 'not'

Alzdba, I dont have index on 'Code' field

Hi Gurus,

I am using default READ COMMITED isolation level . While inserting a single

row in a table it is not allowing to read any other row from the table. As i know

it uses row level locking then why it is not allowing to read rows which are

already commited ?

say table name ABC with code, Name coloumns

existing rows are -

01 Tom

02 Jerry

03 Mouse

Say in a session i am giving INSERT and it is not commited yet -

INSERT INTO ABC values ('04','Mickey')

and in another session Immediately i give

SELECT * from ABC where Code = '01'

It wont return any row for 01 even 01 row is already commited.

locks on the table are

RID X

Page IX

Table IX

Database S

Immediate help is greatly appreciated .

TIA ,

Sheilesh

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 3

read my previous reply

Activate the "show (estimated) execution plan" in QueryAnalyser, maybe that will clarify the issue.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

spaliwal SSChasing Mays Points: 604 More actions · Answer 4

alzdba,

I am not getting your comments in last mail.Pl. elaborate.where will i should see the execution plan in INSERT session or in SELECT session ?

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 5

Perform it on both, maybe that gets it all into the daylight, but at least perform it where you are having the problem (i.e. the select).

- QA\Query\display estimated execution plan

- QA\Query\show execution plan

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

justevolved SSC Enthusiast Points: 188 More actions · Answer 6

The Problem lies in how sql server handles locking mechanism. If sql server feels that placing 4 row locks would be more resource intensive than placing a single page lock it would do the latter.

So in u'r case if there is no index on the column which is in the where clause sql server does a table scan and hence does not return u the row. Try and put an index on the column and the row will be returned. As suggested try and check the execution plan.

You have to dig for tons of dirt to get an ounce of Gold