Deafult NULL not working

I am using SQL Server 2000. I have a Column with DataType int and default value specified as (null). But, With Insert or Update if the column value is Blank, 0 is getting inserted instead of the desired NULL.

Thanks

Is there, by chance, a trigger on this table?|||

NO. There is no trigger on this Table.

|||Well... create a complete DDL script for this table and post it here - something must be there.

Also, how you make a insert / update - directly or via some kind of stored proc? There may be a preprocessing in there that you miss, for example.|||

What exactly do you mean by "column value is blank". If you are explicitly trying to force a blank into the field then yes, it is going to get assigned as zero:

create table dbo.testo
( rid int,
x int default (null)
)

insert into dbo.testo select 1, ' '

insert into dbo.testo (rid) select 1

select * from dbo.testo

/*
rid x
-- --
1 0
1 NULL
*/

If, however, you are wanting to insert a row and allow the default to occur you must do something similar to what I hilighted in red

If you want to UPDATE to the default value, you can use syntax something like this:

update dbo.testO
set x=default
where rid =1

|||

YES. The Column Value is getting evaluated to '' as the user did not enter anything for the field on the form. Is there any way '' can be evaluated to NULL instead of 0.

- vmrao

|||declare @.p1 varchar(255)

set @.p1 = ''

insert into Mytable (rid, myintcol)
select 10, nullif(@.p1, '')
|||Thanks. NULLIF worked.

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:[vbcol=seagreen]
> 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...|||> 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-6d1
81b640c9b.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:
>|||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:[vbcol=seagreen]
> 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-6
d181b640c9b.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 concurrenc
y
> 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 si
ze
> 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...|||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:[vbcol=seagreen]
> 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:|||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:
>

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.googlegro ups.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:[vbcol=seagreen]
> 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.googlegro ups.com...
|||> 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.googlegr oups.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:
>
|||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:[vbcol=seagreen]
> 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.googlegr oups.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:[vbcol=seagreen]
> 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:
|||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.googlegro ups.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:
>

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.
>> > >> >
>> > >
>

deadlock -again

thanx a lot...

in this procedure also i m getting same problem

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

DECLARE @.Bank_Name VARCHAR(50)
DECLARE @.MICR_CMSCode varchar(50),@.MICR_SchemeGroup varchar(50)

--BANK CURSOR
DECLARE CUR_BANK CURSOR FOR
SELECT Bank_Name FROM Tbl_BankMst where Bank_isactive=1

OPEN CUR_BANK

FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
WHILE @.@.FETCH_STATUS=0
BEGIN

--print(@.Bank_Name)
--MICR CURSOR
DECLARE CUR_MICR cursor for
--Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_BankName='ICICI BANK LTD'
Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_AuthStatus =2 and MICR_Optype =0 and MICR_BankName=rtrim(ltrim(@.Bank_Name))

Open CUR_MICR

Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup
while @.@.fetch_status = 0
begin
update Tbl_CMS_UploadDetails set CMS_SchemeGroup =@.MICR_SchemeGroup Where Scheme_Code=rtrim(ltrim(@.MICR_CMSCode))

--print(@.MICR_SchemeGroup)--@.MICR_CMSCode
Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup

end
close CUR_MICR
deallocate CUR_MICR

- update compare status and maker-

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName
--select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name='ICICI BANK LTD'

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
-
FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
END

close CUR_BANK
deallocate CUR_BANK
--Update Tbl_CMS_UploadDetails Set ReconciliationDate=cast(getdate() as varchar(11)) Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
--Added By Gopal For HDFC Bounce cases 24-Feb-07
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='83' and drcr ='D' and amount>0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in('82','83') and rtrim(ltrim(drcr)) ='C' and amount<0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='82' and rtrim(ltrim(drcr)) ='B' and amount>0 and compare_status='Pending'
--Added by Nachiket for failure
--Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='89' or format_id='91' or format_id='86' and amount>0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in ('89','91','86') and amount>0 and compare_status='Pending'
Declare @.recCount int
select @.recCount=count(*) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set rec_count =@.recCount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id
END

Split from the original post since this is a separate issue to be resolved.|||

hi

any body sort out this problem?

thanx a lot

ddl

CREATE TABLE [INTIMINGS] (
[EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INTIME] [datetime] NOT NULL ,
[EFFECTIVE_DATE] [smalldatetime] NULL ,
[END_DATE] [smalldatetime] NULL ,
[ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_INTIMINGS] PRIMARY KEY CLUSTERED
(
[EMPID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into timings values(‘1ab’,1899-12-30
09:00.00.00,2003-07-01,00:00:00,’’,jym)
CREATE TABLE [INTIMINGS_HISTORY] (
[EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INTIME] [datetime] NOT NULL ,
[EFFECTIVE_DATE] [smalldatetime] NOT NULL ,
[END_DATE] [smalldatetime] NULL ,
[ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_INTIMINGS_HISTORY] PRIMARY KEY CLUSTERED
(
[EMPID],
[EFFECTIVE_DATE]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into [INTIMINGS_HISTORY(‘1CMN’, 1899-12-30 09:00.00.00,’
‘2005-03-01,00:00:00’, ‘2005-02-08,00:00:00’

CREATE TABLE [EMPLOYEE_TIMINGS] (
[ENTRYTIME] [datetime] NOT NULL ,
[EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TIMETYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INOUT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IP_ADDRESS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EMPLOYEE_TIMINGS] PRIMARY KEY CLUSTERED
(
[ENTRYTIME]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into EMPLOYEE_TIMINGS(
2003-10-01 00:03:29.000, VVR, S, E, NULL)
2003-10-01 00:03:38.000 SM S E NULL
2003-10-01 00:25:11.000 NA S E NULL
2003-10-01 00:25:18.000 NA S E NULL
2003-10-01 00:25:25.000 AMB S E NULL
2003-10-01 00:38:08.000 KRK S E NULL
2003-10-01 00:51:25.000 KU S E NULL
2003-10-01 02:00:38.000 NZ S E NULL
2003-10-01 05:50:47.000 1FC B S NULL
2003-10-01 06:06:04.000 1IB S E NULL
2003-10-01 06:06:14.000 IH S E NULL
2003-10-01 06:06:21.000 TO S E NULL
2003-10-01 06:07:12.000 EE S S NULL
2003-10-01 06:08:22.000 QX S S NULLwhat is the problem. check quotes in insert
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:

> CREATE TABLE [INTIMINGS] (
> [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTIME] [datetime] NOT NULL ,
> [EFFECTIVE_DATE] [smalldatetime] NULL ,
> [END_DATE] [smalldatetime] NULL ,
> [ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_INTIMINGS] PRIMARY KEY CLUSTERED
> (
> [EMPID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Insert into timings values(‘1ab’,1899-12-30
> 09:00.00.00,2003-07-01,00:00:00,’’,jym)
>
>
> CREATE TABLE [INTIMINGS_HISTORY] (
> [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTIME] [datetime] NOT NULL ,
> [EFFECTIVE_DATE] [smalldatetime] NOT NULL ,
> [END_DATE] [smalldatetime] NULL ,
> [ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_INTIMINGS_HISTORY] PRIMARY KEY CLUSTERED
> (
> [EMPID],
> [EFFECTIVE_DATE]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> Insert into [INTIMINGS_HISTORY(‘1CMN’, 1899-12-30 09:00.00.00,’
> ‘2005-03-01,00:00:00’, ‘2005-02-08,00:00:00’
>
>
>
>
>
>
>
>
>
> CREATE TABLE [EMPLOYEE_TIMINGS] (
> [ENTRYTIME] [datetime] NOT NULL ,
> [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TIMETYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INOUT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IP_ADDRESS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_EMPLOYEE_TIMINGS] PRIMARY KEY CLUSTERED
> (
> [ENTRYTIME]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Insert into EMPLOYEE_TIMINGS(
> 2003-10-01 00:03:29.000, VVR, S, E, NULL)
> 2003-10-01 00:03:38.000 SM S E NULL
> 2003-10-01 00:25:11.000 NA S E NULL
> 2003-10-01 00:25:18.000 NA S E NULL
> 2003-10-01 00:25:25.000 AMB S E NULL
> 2003-10-01 00:38:08.000 KRK S E NULL
> 2003-10-01 00:51:25.000 KU S E NULL
> 2003-10-01 02:00:38.000 NZ S E NULL
> 2003-10-01 05:50:47.000 1FC B S NULL
> 2003-10-01 06:06:04.000 1IB S E NULL
> 2003-10-01 06:06:14.000 IH S E NULL
> 2003-10-01 06:06:21.000 TO S E NULL
> 2003-10-01 06:07:12.000 EE S S NULL
> 2003-10-01 06:08:22.000 QX S S NULL
>