Re: Locking problem
From: Francisco D. Kurpiel (francisco.kurpiel_at_westbest.com.br)
Date: 03/02/04
- Next message: Bent Lund: "Re: Select next value from a group"
- Previous message: Damon: "Copy Column?"
- In reply to: David Browne: "Re: Locking problem"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 2 Mar 2004 11:31:06 -0300
(The indexes names!!! Who I lived before this proc?)
> The reason for all of this is that without snapshot concurrency, it's easy
> to become overly syncronized, and loose concurency. There is a very
strong
> policy in favor of concurrency, and so you might not get all the locks you
> expect.
It’s okay to be a default behavior, but I don't have the option to serialize
data access when using covering index? Allowing serialization just on index
doesn’t sounds very arbitrary? If Microsoft chooses to don’t allow
programmers to serialize data access (ignoring the standard definition of
serializable transaction level), why they just remove the serialization
resource of the SQL Server? This is a big argument to concurrency (just
kidding...). If I create an index exactly to be a covering index to a
procedure that’s working may I insert a bug on the app?
(woo-hoo!!! index names!!)
Suppose we create a select statement on three tables for some application.
We released the app to users do her work. After some weeks the query plan,
initially very trivial to waste the time of the optimizer, is getting more
costly. SQL Server wastes just some milliseconds and chooses another plan,
with other indexes. I now that this example is a little obvious, but it
illustrates perfectly my preoccupation. Some applications need serialization
in some resources. And serialization needs to be deterministic. I’m sorry to
be so emphatic, but this kind of problem is my responsibility on my project.
>
> select * from bla takes an IS lock on bla and an S lock on un_bla_nome
> because un_bla_nome is, suprisingly, a covering index for the query. It's
> only a covering index because the only columns in bla, but not in
> un_bla_nome, are the clustered index columns (ie id). The clustered index
> columns are included in the index because that's the only way to locate a
> row in a table with a clusered index.
>
> And IX and IS are compatable. Here's the lock compatability matrix from
> BOL.
>
You are right. But I forgot to say that the behavior I just described occurs
when table doesn’t have clustered index. Without clustered index the fist
script creates an IX lock (if using the xlock hint) on the table, and the
second creates S lock, what solves the restrict problem of my example. But
if I do a “select nome from bla” on the second script it uses the unique
index, creating just a IS lock on the table and some locks that’s just
applicable to this index, what I just discovered that don’t grant anything.
>
> > Do you have a link to some documentation about this subject? My current
> > project probably will become big and I don’t want more surprises.
> >
>
> Inside Microsoft® SQL Server™ 2000 by Kalen Delaney
> http://www.microsoft.com/MSPress/books/4297.asp
>
Hei, I have read this book! But I don't remember anything about covering
index being locked independently of the table. I will reread this session.
> >
> > I appreciate your help and the enfort on make it clear. Any additional
> > reference will be welcome.
> >
> > PS: Where did you get this sp_lock2? The one that I have doesn’t show
the
> > index name. It helps a lot.
>
> http://support.microsoft.com/support/kb/articles/Q255/5/96.ASP
>
I was using this one before you send me the other script.
Thank you for the help and sorry for my poor english.
(hehehe... index names...)
- Next message: Bent Lund: "Re: Select next value from a group"
- Previous message: Damon: "Copy Column?"
- In reply to: David Browne: "Re: Locking problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
