|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Deadlock with index intersection
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Deadlock with index intersection
|
|
| Shailesh Khanal 2002-11-08, 1:24 pm |
| Hi,
I have a deadlock situation with index intersection.
Connection1:
Updates a table T1 which has indexes I1 and I2
Connection2:
Selects from T1 (joining many other tables), SQL Server
uses index intersection of I1 and I2.
The deadlock error log shows,
Connection1 has exclusive lock on I1 and waits on
exclusive lock request on I2
Connection2 has a shared lock on I2 and waits on shared
lock request on I1
Connection2 is selected as the deadlock victim.
The transaction isolation level is explicitly set to READ
COMMITTED in ADO connection object.
I don't understand why SQL server needs to hold on the
shared lock on I1 while waiting for a shared lock on I2.
Is there any index hint to prohibit SQL server from using
index intersection other than explictly using INDEX=I1 or
I2.
Thanks
| |
| Hal Berenson 2002-11-09, 7:23 pm |
| Within the execution of a single statement locks are often briefly held to
insure integrity. In the case of something like the index intersection, SQL
Server is going to make sure that the values it expects to use for the
intersection don't change while it's actually doing the comparison. So
you'd get a share lock on I1, then hold it while you check for a matching I2
(which requires a lock as well). This situation is not restricted to index
intersection, you'd see similar locking behavior during a join where I1 was
an index on table T1 and I2 was an index on table T2.
You could use a hint to eliminate the index intersection, at the cost of
reduced performance for the query. But it certainly is a strange use of
optimizer hints and may provide only temporary relief.
I'd look at other options for restructuring the application to reduce or
eliminate the deadlocks. For example, if T1 is so actively updated that
deadlocks from reporting queries are common then I'd look at breaking up my
complex queries. Have an initial query that simply loads the desired data
from T1 into a temporary table and then have the complex queries operate on
the temporary table. The temporary table load can be simple enough to avoid
the deadlocks or, if deadlocks are infrequent, it can be written to retry on
a deadlock. If the data can be slightly less consistent (and read committed
already means that the values you are seeing are out of date with respect to
one another) then loading from T1 into the temporary table can be done using
read uncommitted. And once you have the data in the temporary table you can
run the complex query using read committed or another isolation level.
--
Hal Berenson
True Mountain Consulting
"Shailesh Khanal" <skhanal@centresolutions.com> wrote in message
news:b29401c2875b$bd48be30$2ae
2c90a@phx.gbl...
> Hi,
>
> I have a deadlock situation with index intersection.
>
> Connection1:
>
> Updates a table T1 which has indexes I1 and I2
>
> Connection2:
>
> Selects from T1 (joining many other tables), SQL Server
> uses index intersection of I1 and I2.
>
> The deadlock error log shows,
>
> Connection1 has exclusive lock on I1 and waits on
> exclusive lock request on I2
>
> Connection2 has a shared lock on I2 and waits on shared
> lock request on I1
>
> Connection2 is selected as the deadlock victim.
>
> The transaction isolation level is explicitly set to READ
> COMMITTED in ADO connection object.
>
> I don't understand why SQL server needs to hold on the
> shared lock on I1 while waiting for a shared lock on I2.
>
> Is there any index hint to prohibit SQL server from using
> index intersection other than explictly using INDEX=I1 or
> I2.
>
>
> Thanks
>
|
|
|
|
|