Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-JTim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:
> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:
> > Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> > there is no need to even think about excluding anything else when just
> > reading. For example SELECT * from pubs..authors aquires two locks - an
> > Intent-Exclusive table level lock on the clustered index and another
> > Intent-Exclusive table on the non-clustered index.
> >
> > I believe this is excessive. Can anyone please explain?
> >
> > Thanks in advance,
> > Tim G-J