Deadlock Issue.

Greetings All, here is the ddl to create my test:
create table Parent
(
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
RIAmt decimal(28,10),
CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
)
go
create table Child
(
CPK1 decimal(10) not null,
CPK2 decimal(9) not null,
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
)
go
ALTER TABLE Child ADD CONSTRAINT FK
FOREIGN KEY (PPK1, PPK2)
REFERENCES Parent(PPK1, PPK2)
go
Next I open two different SQLCMD Windows: cmd1 and cmd 2
cmd1: begin tran;
go
insert into parent values (1, 999999999);
go
cmd2: begin tran;
go
insert into parent values (2, 999999999);
go
insert into child values (1, 999999999, 2, 999999999);
go
cmd1: insert into child values (2, 999999999, 1, 999999999);
go
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
WAIT CONDITION IS GENERATED
cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
go
DEADLOCK OCCURS
I am curious why this deadlock occurs when each thread is only
accessing data created in its own thread? I am thinking that a table
scan is taking place on the child table when I do the select and it is
bumping into a locked record?
Any and all help would be greatly appreciated.
Regards, TFD.> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
Your theory is correct. Since there is no index on PPK1 and PPK2, the
SELECT select statements must scan all data and become blocked when
uncommitted data are encountered.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> Greetings All, here is the ddl to create my test:
> create table Parent
> (
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> RIAmt decimal(28,10),
> CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> )
> go
> create table Child
> (
> CPK1 decimal(10) not null,
> CPK2 decimal(9) not null,
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> )
> go
> ALTER TABLE Child ADD CONSTRAINT FK
> FOREIGN KEY (PPK1, PPK2)
> REFERENCES Parent(PPK1, PPK2)
> go
>
> Next I open two different SQLCMD Windows: cmd1 and cmd 2
> cmd1: begin tran;
> go
> insert into parent values (1, 999999999);
> go
> cmd2: begin tran;
> go
> insert into parent values (2, 999999999);
> go
> insert into child values (1, 999999999, 2, 999999999);
> go
> cmd1: insert into child values (2, 999999999, 1, 999999999);
> go
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> WAIT CONDITION IS GENERATED
> cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> go
> DEADLOCK OCCURS
> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
> Any and all help would be greatly appreciated.
> Regards, TFD.
>|||Dan, let me ask you a broad question that may not have a direct answer
but hopefully some best practice might be applicable. This issue I
demonstrated here is happening in an application developed by my
company. It is a mult-threaded parallel processing application that is
required to have high throughput and will be performing complex
calculations. One way I can prevent the issue I brought up her is to
have ADO start the transaction in "snapshot" mode. This will avoid the
deadlock issue but I am worried about tempdb peformance? An
alternative is to go throught he physical data model and ensure that
all FK's have the appropriate indexes so that the scenario here (which
can happen in many places in the application) will not occur.
What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
Regards, TFD.
Dan Guzman wrote:
> > I am curious why this deadlock occurs when each thread is only
> > accessing data created in its own thread? I am thinking that a table
> > scan is taking place on the child table when I do the select and it is
> > bumping into a locked record?
> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> SELECT select statements must scan all data and become blocked when
> uncommitted data are encountered.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> > Greetings All, here is the ddl to create my test:
> >
> > create table Parent
> > (
> > PPK1 decimal(10) not null,
> > PPK2 decimal(9) not null,
> > RIAmt decimal(28,10),
> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> > )
> > go
> >
> > create table Child
> > (
> > CPK1 decimal(10) not null,
> > CPK2 decimal(9) not null,
> > PPK1 decimal(10) not null,
> > PPK2 decimal(9) not null,
> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> > )
> > go
> >
> > ALTER TABLE Child ADD CONSTRAINT FK
> > FOREIGN KEY (PPK1, PPK2)
> > REFERENCES Parent(PPK1, PPK2)
> > go
> >
> >
> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> >
> > cmd1: begin tran;
> > go
> > insert into parent values (1, 999999999);
> > go
> >
> > cmd2: begin tran;
> > go
> > insert into parent values (2, 999999999);
> > go
> > insert into child values (1, 999999999, 2, 999999999);
> > go
> >
> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> > go
> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> > go
> > WAIT CONDITION IS GENERATED
> >
> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> > go
> > DEADLOCK OCCURS
> >
> > I am curious why this deadlock occurs when each thread is only
> > accessing data created in its own thread? I am thinking that a table
> > scan is taking place on the child table when I do the select and it is
> > bumping into a locked record?
> >
> > Any and all help would be greatly appreciated.
> >
> > Regards, TFD.
> >|||> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
should not be used as a general cure for blocking. The SQL Server 2005
Books Online does a pretty good job of discussing the pros and cons of the
various row versioning levels
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
However, I want to add that performance and concurrency go hand-in-hand.
Blocking is often a symptom of an underlying performance issue as
illustrated by you example. Sure, you might be able to improve concurrency
by using SNAPSHOT ISOLATION but that's not the right approach unless you
know the root cause and ramifications. If you simply change the isolation
level rather than perform index/query tuning, you'll find the app doesn't
scale. CPU and disk i/o will be consumed in direct proportion to table size
snapshot isolation overhead only compounds the issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> Dan, let me ask you a broad question that may not have a direct answer
> but hopefully some best practice might be applicable. This issue I
> demonstrated here is happening in an application developed by my
> company. It is a mult-threaded parallel processing application that is
> required to have high throughput and will be performing complex
> calculations. One way I can prevent the issue I brought up her is to
> have ADO start the transaction in "snapshot" mode. This will avoid the
> deadlock issue but I am worried about tempdb peformance? An
> alternative is to go throught he physical data model and ensure that
> all FK's have the appropriate indexes so that the scenario here (which
> can happen in many places in the application) will not occur.
> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> Regards, TFD.
>
> Dan Guzman wrote:
>> > I am curious why this deadlock occurs when each thread is only
>> > accessing data created in its own thread? I am thinking that a table
>> > scan is taking place on the child table when I do the select and it is
>> > bumping into a locked record?
>> Your theory is correct. Since there is no index on PPK1 and PPK2, the
>> SELECT select statements must scan all data and become blocked when
>> uncommitted data are encountered.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
>> > Greetings All, here is the ddl to create my test:
>> >
>> > create table Parent
>> > (
>> > PPK1 decimal(10) not null,
>> > PPK2 decimal(9) not null,
>> > RIAmt decimal(28,10),
>> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
>> > )
>> > go
>> >
>> > create table Child
>> > (
>> > CPK1 decimal(10) not null,
>> > CPK2 decimal(9) not null,
>> > PPK1 decimal(10) not null,
>> > PPK2 decimal(9) not null,
>> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
>> > )
>> > go
>> >
>> > ALTER TABLE Child ADD CONSTRAINT FK
>> > FOREIGN KEY (PPK1, PPK2)
>> > REFERENCES Parent(PPK1, PPK2)
>> > go
>> >
>> >
>> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
>> >
>> > cmd1: begin tran;
>> > go
>> > insert into parent values (1, 999999999);
>> > go
>> >
>> > cmd2: begin tran;
>> > go
>> > insert into parent values (2, 999999999);
>> > go
>> > insert into child values (1, 999999999, 2, 999999999);
>> > go
>> >
>> > cmd1: insert into child values (2, 999999999, 1, 999999999);
>> > go
>> > select * from child where ppk1 = 2 and ppk2 = 999999999;
>> > go
>> > WAIT CONDITION IS GENERATED
>> >
>> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
>> > go
>> > DEADLOCK OCCURS
>> >
>> > I am curious why this deadlock occurs when each thread is only
>> > accessing data created in its own thread? I am thinking that a table
>> > scan is taking place on the child table when I do the select and it is
>> > bumping into a locked record?
>> >
>> > Any and all help would be greatly appreciated.
>> >
>> > Regards, TFD.
>> >
>|||Dan, perhaps you can entertain one more question for me seeing that you
know what is going on :)
The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:
> > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrency
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table size
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> > Dan, let me ask you a broad question that may not have a direct answer
> > but hopefully some best practice might be applicable. This issue I
> > demonstrated here is happening in an application developed by my
> > company. It is a mult-threaded parallel processing application that is
> > required to have high throughput and will be performing complex
> > calculations. One way I can prevent the issue I brought up her is to
> > have ADO start the transaction in "snapshot" mode. This will avoid the
> > deadlock issue but I am worried about tempdb peformance? An
> > alternative is to go throught he physical data model and ensure that
> > all FK's have the appropriate indexes so that the scenario here (which
> > can happen in many places in the application) will not occur.
> >
> > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> >
> > Regards, TFD.
> >
> >
> > Dan Guzman wrote:
> >> > I am curious why this deadlock occurs when each thread is only
> >> > accessing data created in its own thread? I am thinking that a table
> >> > scan is taking place on the child table when I do the select and it is
> >> > bumping into a locked record?
> >>
> >> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> >> SELECT select statements must scan all data and become blocked when
> >> uncommitted data are encountered.
> >>
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> >> > Greetings All, here is the ddl to create my test:
> >> >
> >> > create table Parent
> >> > (
> >> > PPK1 decimal(10) not null,
> >> > PPK2 decimal(9) not null,
> >> > RIAmt decimal(28,10),
> >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> >> > )
> >> > go
> >> >
> >> > create table Child
> >> > (
> >> > CPK1 decimal(10) not null,
> >> > CPK2 decimal(9) not null,
> >> > PPK1 decimal(10) not null,
> >> > PPK2 decimal(9) not null,
> >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> >> > )
> >> > go
> >> >
> >> > ALTER TABLE Child ADD CONSTRAINT FK
> >> > FOREIGN KEY (PPK1, PPK2)
> >> > REFERENCES Parent(PPK1, PPK2)
> >> > go
> >> >
> >> >
> >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> >> >
> >> > cmd1: begin tran;
> >> > go
> >> > insert into parent values (1, 999999999);
> >> > go
> >> >
> >> > cmd2: begin tran;
> >> > go
> >> > insert into parent values (2, 999999999);
> >> > go
> >> > insert into child values (1, 999999999, 2, 999999999);
> >> > go
> >> >
> >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> >> > go
> >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> >> > go
> >> > WAIT CONDITION IS GENERATED
> >> >
> >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> >> > go
> >> > DEADLOCK OCCURS
> >> >
> >> > I am curious why this deadlock occurs when each thread is only
> >> > accessing data created in its own thread? I am thinking that a table
> >> > scan is taking place on the child table when I do the select and it is
> >> > bumping into a locked record?
> >> >
> >> > Any and all help would be greatly appreciated.
> >> >
> >> > Regards, TFD.
> >> >
> >|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on :)
> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:
> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> >
> > I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> > should not be used as a general cure for blocking. The SQL Server 2005
> > Books Online does a pretty good job of discussing the pros and cons of the
> > various row versioning levels
> > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> >
> > However, I want to add that performance and concurrency go hand-in-hand.
> > Blocking is often a symptom of an underlying performance issue as
> > illustrated by you example. Sure, you might be able to improve concurrency
> > by using SNAPSHOT ISOLATION but that's not the right approach unless you
> > know the root cause and ramifications. If you simply change the isolation
> > level rather than perform index/query tuning, you'll find the app doesn't
> > scale. CPU and disk i/o will be consumed in direct proportion to table size
> > snapshot isolation overhead only compounds the issue.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> > news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> > > Dan, let me ask you a broad question that may not have a direct answer
> > > but hopefully some best practice might be applicable. This issue I
> > > demonstrated here is happening in an application developed by my
> > > company. It is a mult-threaded parallel processing application that is
> > > required to have high throughput and will be performing complex
> > > calculations. One way I can prevent the issue I brought up her is to
> > > have ADO start the transaction in "snapshot" mode. This will avoid the
> > > deadlock issue but I am worried about tempdb peformance? An
> > > alternative is to go throught he physical data model and ensure that
> > > all FK's have the appropriate indexes so that the scenario here (which
> > > can happen in many places in the application) will not occur.
> > >
> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> > >
> > > Regards, TFD.
> > >
> > >
> > > Dan Guzman wrote:
> > >> > I am curious why this deadlock occurs when each thread is only
> > >> > accessing data created in its own thread? I am thinking that a table
> > >> > scan is taking place on the child table when I do the select and it is
> > >> > bumping into a locked record?
> > >>
> > >> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> > >> SELECT select statements must scan all data and become blocked when
> > >> uncommitted data are encountered.
> > >>
> > >>
> > >> --
> > >> Hope this helps.
> > >>
> > >> Dan Guzman
> > >> SQL Server MVP
> > >>
> > >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> > >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> > >> > Greetings All, here is the ddl to create my test:
> > >> >
> > >> > create table Parent
> > >> > (
> > >> > PPK1 decimal(10) not null,
> > >> > PPK2 decimal(9) not null,
> > >> > RIAmt decimal(28,10),
> > >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> > >> > )
> > >> > go
> > >> >
> > >> > create table Child
> > >> > (
> > >> > CPK1 decimal(10) not null,
> > >> > CPK2 decimal(9) not null,
> > >> > PPK1 decimal(10) not null,
> > >> > PPK2 decimal(9) not null,
> > >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> > >> > )
> > >> > go
> > >> >
> > >> > ALTER TABLE Child ADD CONSTRAINT FK
> > >> > FOREIGN KEY (PPK1, PPK2)
> > >> > REFERENCES Parent(PPK1, PPK2)
> > >> > go
> > >> >
> > >> >
> > >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> > >> >
> > >> > cmd1: begin tran;
> > >> > go
> > >> > insert into parent values (1, 999999999);
> > >> > go
> > >> >
> > >> > cmd2: begin tran;
> > >> > go
> > >> > insert into parent values (2, 999999999);
> > >> > go
> > >> > insert into child values (1, 999999999, 2, 999999999);
> > >> > go
> > >> >
> > >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> > >> > go
> > >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> > >> > go
> > >> > WAIT CONDITION IS GENERATED
> > >> >
> > >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> > >> > go
> > >> > DEADLOCK OCCURS
> > >> >
> > >> > I am curious why this deadlock occurs when each thread is only
> > >> > accessing data created in its own thread? I am thinking that a table
> > >> > scan is taking place on the child table when I do the select and it is
> > >> > bumping into a locked record?
> > >> >
> > >> > Any and all help would be greatly appreciated.
> > >> >
> > >> > Regards, TFD.
> > >> >
> > >|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegroups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>> Dan, perhaps you can entertain one more question for me seeing that you
>> know what is going on :)
>> The scenario I described above is further complicated by the fact that
>> in my application the base table is accessed via view. When I create
>> the index on the FK's and then execute the SQL the scan goes away.
>> When I make the same call via a database view the index is not used and
>> I am once again doing a table scan and my deadlock rears its ugly head.
>> How do I force an index when selecting data through a view?
>>
>> e.g.)
>> CREATE INDEX Parent_IDX1
>> ON Parent(PPK1,PPK2);
>> ** This uses the index on PPK1 and PPK2
>> select * from child where ppk1 = 2 and ppk2 = 999999999;
>> go
>> ** This does not use the index on PPK1 and PPK2
>> ** The Optimizer comes back sayign it used the Primary Key of Child for
>> a Clustered Index seek.
>> CREATE VIEW MyView AS
>> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
>> FROM Child
>> go
>>
>> How do I force the Index Parent_IDX1 to get used? MY test only has a
>> few rows of data but in production this table will be heavily populated
>> and used.
>> Any and all help woudl be greatly appreciated.
>> TFD
>>
>>
>> Dan Guzman wrote:
>> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
>> >
>> > I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
>> > should not be used as a general cure for blocking. The SQL Server 2005
>> > Books Online does a pretty good job of discussing the pros and cons of
>> > the
>> > various row versioning levels
>> > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
>> >
>> > However, I want to add that performance and concurrency go
>> > hand-in-hand.
>> > Blocking is often a symptom of an underlying performance issue as
>> > illustrated by you example. Sure, you might be able to improve
>> > concurrency
>> > by using SNAPSHOT ISOLATION but that's not the right approach unless
>> > you
>> > know the root cause and ramifications. If you simply change the
>> > isolation
>> > level rather than perform index/query tuning, you'll find the app
>> > doesn't
>> > scale. CPU and disk i/o will be consumed in direct proportion to table
>> > size
>> > snapshot isolation overhead only compounds the issue.
>> >
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> > news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
>> > > Dan, let me ask you a broad question that may not have a direct
>> > > answer
>> > > but hopefully some best practice might be applicable. This issue I
>> > > demonstrated here is happening in an application developed by my
>> > > company. It is a mult-threaded parallel processing application that
>> > > is
>> > > required to have high throughput and will be performing complex
>> > > calculations. One way I can prevent the issue I brought up her is to
>> > > have ADO start the transaction in "snapshot" mode. This will avoid
>> > > the
>> > > deadlock issue but I am worried about tempdb peformance? An
>> > > alternative is to go throught he physical data model and ensure that
>> > > all FK's have the appropriate indexes so that the scenario here
>> > > (which
>> > > can happen in many places in the application) will not occur.
>> > >
>> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
>> > >
>> > > Regards, TFD.
>> > >
>> > >
>> > > Dan Guzman wrote:
>> > >> > I am curious why this deadlock occurs when each thread is only
>> > >> > accessing data created in its own thread? I am thinking that a
>> > >> > table
>> > >> > scan is taking place on the child table when I do the select and
>> > >> > it is
>> > >> > bumping into a locked record?
>> > >>
>> > >> Your theory is correct. Since there is no index on PPK1 and PPK2,
>> > >> the
>> > >> SELECT select statements must scan all data and become blocked when
>> > >> uncommitted data are encountered.
>> > >>
>> > >>
>> > >> --
>> > >> Hope this helps.
>> > >>
>> > >> Dan Guzman
>> > >> SQL Server MVP
>> > >>
>> > >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> > >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
>> > >> > Greetings All, here is the ddl to create my test:
>> > >> >
>> > >> > create table Parent
>> > >> > (
>> > >> > PPK1 decimal(10) not null,
>> > >> > PPK2 decimal(9) not null,
>> > >> > RIAmt decimal(28,10),
>> > >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
>> > >> > )
>> > >> > go
>> > >> >
>> > >> > create table Child
>> > >> > (
>> > >> > CPK1 decimal(10) not null,
>> > >> > CPK2 decimal(9) not null,
>> > >> > PPK1 decimal(10) not null,
>> > >> > PPK2 decimal(9) not null,
>> > >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
>> > >> > )
>> > >> > go
>> > >> >
>> > >> > ALTER TABLE Child ADD CONSTRAINT FK
>> > >> > FOREIGN KEY (PPK1, PPK2)
>> > >> > REFERENCES Parent(PPK1, PPK2)
>> > >> > go
>> > >> >
>> > >> >
>> > >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
>> > >> >
>> > >> > cmd1: begin tran;
>> > >> > go
>> > >> > insert into parent values (1, 999999999);
>> > >> > go
>> > >> >
>> > >> > cmd2: begin tran;
>> > >> > go
>> > >> > insert into parent values (2, 999999999);
>> > >> > go
>> > >> > insert into child values (1, 999999999, 2, 999999999);
>> > >> > go
>> > >> >
>> > >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
>> > >> > go
>> > >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
>> > >> > go
>> > >> > WAIT CONDITION IS GENERATED
>> > >> >
>> > >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
>> > >> > go
>> > >> > DEADLOCK OCCURS
>> > >> >
>> > >> > I am curious why this deadlock occurs when each thread is only
>> > >> > accessing data created in its own thread? I am thinking that a
>> > >> > table
>> > >> > scan is taking place on the child table when I do the select and
>> > >> > it is
>> > >> > bumping into a locked record?
>> > >> >
>> > >> > Any and all help would be greatly appreciated.
>> > >> >
>> > >> > Regards, TFD.
>> > >> >
>> > >
>