Deadlocks & BEGIN/END TRANSACTION

Greetings,

I've been reading with interest the threads here on deadlocking, as I'm
finding my formerly happy app in a production environment suddenly
deadlocking left and right. It started around the time I decided to
wrap a series of UPDATE commands with BEGIN/END.

The gist of it is I have a .NET app that can do some heavy reading (no
writing) from tblWOS. It can take a minute or so to read all the data
into the app, along with data from other tables.

I also have a web app out on the floor where people can enter
transactions which updates perhaps 5-20 records in tblWOS at a time.
The issue comes when someone is loading data with the app, and someone
else tries an update through the web app: deadlocks-ville on the
application and/or the web app.

Again, I believe it began around the time I wrapped those 5-20 record
updates to tblWOS on the web app with BEGIN/END. The funny thing is
that the records involved are not the same ones, so I'm thinking some
kind of table-level lock is going on.

I've played with UPDLOCK in examples, but don't quite understand what
it's attempting to do. Since the web update is discrete and short, and
it is NOT updating records that are getting loaded, I'd like the
BEGIN/UPDATE/END web transaction to happen and not deadlock the loading
application.

Any suggestions? I'd be most grateful.

thanks, LeafLeaf (rangerleaf@.hotmail.com) writes:
> I've been reading with interest the threads here on deadlocking, as I'm
> finding my formerly happy app in a production environment suddenly
> deadlocking left and right. It started around the time I decided to
> wrap a series of UPDATE commands with BEGIN/END.
> The gist of it is I have a .NET app that can do some heavy reading (no
> writing) from tblWOS. It can take a minute or so to read all the data
> into the app, along with data from other tables.
> I also have a web app out on the floor where people can enter
> transactions which updates perhaps 5-20 records in tblWOS at a time.
> The issue comes when someone is loading data with the app, and someone
> else tries an update through the web app: deadlocks-ville on the
> application and/or the web app.
> Again, I believe it began around the time I wrapped those 5-20 record
> updates to tblWOS on the web app with BEGIN/END. The funny thing is
> that the records involved are not the same ones, so I'm thinking some
> kind of table-level lock is going on.
> I've played with UPDLOCK in examples, but don't quite understand what
> it's attempting to do. Since the web update is discrete and short, and
> it is NOT updating records that are getting loaded, I'd like the
> BEGIN/UPDATE/END web transaction to happen and not deadlock the loading
> application.

Of course, if you want those 5-20 updates to be performed all or none
of them, but not only half of them, user-defined transactions is the way
to go. But since you then will hold locks for a longer period, you will
be more prone to deadlocking.

Deadlock situations can be fairly straight-forward to understand, but can
also be very complex. Therefore it's difficult to give precise advice from
any distance.

I can give some general advice though:

Indexing is important. Make sure that all involved queries uses Index Seek
or Clustered Index Seek, so the queries do not require table locks. You
can study the query plans by running the queries from Query Analyzer, but
you can also use Profiler to trace the application, and include the
Show Execution Plan event.

Important is also the order of access. Say that process A updates rows
1, 5, 9, 11, 17 in that order and process B updates rows 24, 27, 11, 1, 2
in that order. They will deadlock, because when A comes to row 11, B already
has updated that one, but not committed it. B then gets stuck on row 1,
because A has a lock on that row.

I don't if this happening in your application, but it's very important to
not have transactions in progress while waiting for user input. You could
be waiting all day in such case.

Finally, UPDLOCK, is a locking hint which is good when you read a row,
with the intention to update it in the same transaction. UPDLOCK itself
is a shared locks, and thus readers are not block. But the lock remains
to the end of the transaction, and no other process can have an UPDLOCK
on the same resource.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks so much for your quick and verbose response.

> Indexing is important. Make sure that all involved queries uses Index
Seek
> or Clustered Index Seek, so the queries do not require table locks.

I'm not using queries, but rather ADO.NET to pull data from tables by
filtering on IDs. Can I take that to mean I should make sure that any
IDs in my WHERE statements should be indexed? All records in my DB have
a primary key, clustered index. But in one-to-many tables, I filter
heavily on that related table to the primary key in another table. For
example, tblWOS.FactoryOrderID is the reference in a child table to PK
in tblFactoryOrders.FactoryOrderID:

SELECT * FROM tblWOS WHERE FactoryOrderID=10

Are you implying that I should make sure tblWOS.FactoryOrderID should
be indexed, too?

I'll be a bit more explicit. The web app passes a series of discrete
SQL commands via an ADO.NET connection object:

BEGIN TRANS
-- Update primary table (one record)
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
-- Update related child table (many records)
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS

tblFactoryOrders has one record in it (primary record), and tblWOS
could have 5-20 related to tblFactoryOrders.

These transactions can happen for any primary record at any time from
the web by 100 users, but generally each primary record gets hit just a
couple times a day.

Meanwhile, there's a .NET app which periodically (2-10 times daily)
loads a set of data from tblFactoryOrders and tblWOS, about 900 records
from the first and 5,000 records from the second. It loads this way:

SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...

and then a series of for each record in tblFactoryOrders

SELECT * FROM tblWOS WHERE FactoryOrderID=...

The issue is that while this load is happening, someone on the web
doing an UPDATE on records unrelated to the loaded values cause a
deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?

It's OK if someone on the web updates while this load is happening.

I'd like to resolve:

- if two folks on the web hit the same FactoryOrderID object, I'd like
them not to deadlock, but one to wait on the other.

- if someone is loading the application, I'd like it not to deadlock
when someone on the web does a transaction.

- the app can also write back to tblWOS (again, records not available
by the web app), and not deadlock the web app or the save process.

Would it be wise to stick a:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANS
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS

thank you, Leaf|||Leaf (rangerleaf@.hotmail.com) writes:
> I'm not using queries, but rather ADO.NET to pull data from tables by
> filtering on IDs. Can I take that to mean I should make sure that any
> IDs in my WHERE statements should be indexed? All records in my DB have
> a primary key, clustered index. But in one-to-many tables, I filter
> heavily on that related table to the primary key in another table. For
> example, tblWOS.FactoryOrderID is the reference in a child table to PK
> in tblFactoryOrders.FactoryOrderID:
> SELECT * FROM tblWOS WHERE FactoryOrderID=10
> Are you implying that I should make sure tblWOS.FactoryOrderID should
> be indexed, too?

Assuming tblWOS is of any size, you should definitely have an index
on that column. Now, I don't know much about this table, but I like
to point out from what you said here, it could very well be that this
is the column you should have your clustered index on.

> Meanwhile, there's a .NET app which periodically (2-10 times daily)
> loads a set of data from tblFactoryOrders and tblWOS, about 900 records
> from the first and 5,000 records from the second. It loads this way:
> SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...
> and then a series of for each record in tblFactoryOrders
> SELECT * FROM tblWOS WHERE FactoryOrderID=...

It would certainly be a good idea, to write a stored procedure that
produces rwo result sets: one that contains the rows from tblFactoryOrders,
one that contain all rows from tblFactoryOrders. In any case, sending
a query for each FactoryOrderId means a lot of network roundtrips.
Basically, if you get 100 ids pact, the load takes 100 times of what
it could take.

> The issue is that while this load is happening, someone on the web
> doing an UPDATE on records unrelated to the loaded values cause a
> deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?

Not really. The table scans are the real issue here.

> - if two folks on the web hit the same FactoryOrderID object, I'd like
> them not to deadlock, but one to wait on the other.

And then the guy that is number #2 overwrites the updates of #1? The
common strategy is to use optimistic locking. This can be implemented
in several ways, but the easiest is to add a timestamp column.
Timestamp columns are automatically updated by SQL Server each time
you update a row. (And they have nothing to do with date and time.)
So you add a timestamp condition to the UPDATE, and if they don't
match, the user is informed of an update conflict?

> - if someone is loading the application, I'd like it not to deadlock
> when someone on the web does a transaction.

I know too little about the scenario to tell whether just adding the
index will help. I'm also a little concerned of the consistency the
data that is being loaded. What happens if there is an update to
tblWOS when a load is in progress? What is the desired result?

By wrapping the load in a transaction, with the isolation level of
REPEATABLE READ or SERIALIZABLE, you could ensure consistency, as no
update of orders being loaded could be performed while the load is going
on.

This would even more require you to make sure tblFactoryOrders is
read only once, and not once for each ID.

> Would it be wise to stick a:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANS
> UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
> UPDATE tblWOS SET ... WHERE FactoryOrderID=10
> COMMIT TRANS

Actually, as hinted above, it's more the SELECT transaction that
can benefit for a higher isolation level. The UPDATE transaction
will not change that much, if at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your kind reply. VERY helpful.sql

deadlock on tempdb..sysindexes

Greetings,
I have stored procedure dumping resultset into created temporary table.
Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
Problem is I that can not change stored procedure and not sure how to stop
locking sysindexes since stored proc will take between 5-15 seconds to run
depending on date range supplied.
I would appreciate any suggestions how to resolve this issue
PS: Code goes like this
create table #temp(...)
insert into #temp
execute sp_StoredProc
--
SaxonThe problem appears to be that the stored procedure is executing within a
transaction. This is unavoidable, since the INSERT...EXEC statement starts
a transaction prior to executing the procedure. To avoid the deadlocks, you
MUST alter the stored procedure. If you can't alter it, then make a copy
and alter that. Change the procedure so that it executes an INSERT
statement into the temp table. (If a temp table is created before executing
the stored procedure, it is available within the body of the stored
procedure.) This will eliminate the transaction
You should avoid creating, altering or deleting temporary objects within a
transaction. This includes both tables, indexes and constraints. You
should avoid executing procedures within a transaction. For this reason, I
generally avoid INSERT...EXEC.
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
> Greetings,
> I have stored procedure dumping resultset into created temporary table.
> Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
> Problem is I that can not change stored procedure and not sure how to stop
> locking sysindexes since stored proc will take between 5-15 seconds to run
> depending on date range supplied.
> I would appreciate any suggestions how to resolve this issue
> PS: Code goes like this
> create table #temp(...)
> insert into #temp
> execute sp_StoredProc
> --
> Saxon|||Thanks Brian,
so basically if I create temp table and call stored proc to insert into
table instead of using INSERT... EXEC it would not cause deadlock since no
transactions would be started.
PS: Why inserting into temp table would hold lock on sysindexes anyway? I
tried to find some info on that but no luck.
Regards
Saxon
"Brian Selzer" wrote:

> The problem appears to be that the stored procedure is executing within a
> transaction. This is unavoidable, since the INSERT...EXEC statement start
s
> a transaction prior to executing the procedure. To avoid the deadlocks, y
ou
> MUST alter the stored procedure. If you can't alter it, then make a copy
> and alter that. Change the procedure so that it executes an INSERT
> statement into the temp table. (If a temp table is created before executi
ng
> the stored procedure, it is available within the body of the stored
> procedure.) This will eliminate the transaction
> You should avoid creating, altering or deleting temporary objects within a
> transaction. This includes both tables, indexes and constraints. You
> should avoid executing procedures within a transaction. For this reason,
I
> generally avoid INSERT...EXEC.
>
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
>
>|||The lock isn't caused by inserting, it's caused by creating, altering, or
deleting a temporary object within the procedure! The problem is that
normally, when a procedure runs, any transactions must be explicitly started
within the body of the proc. INSERT...EXEC wraps the procedure call in a
transaction. There are several articles on MSDN about lock contention and
blocking--some cite concurrency issues with tempdb. (There are fixes for
that in SP4.)
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
> Thanks Brian,
> so basically if I create temp table and call stored proc to insert into
> table instead of using INSERT... EXEC it would not cause deadlock since no
> transactions would be started.
> PS: Why inserting into temp table would hold lock on sysindexes anyway? I
> tried to find some info on that but no luck.
> Regards
> --
> Saxon
>
> "Brian Selzer" wrote:
>|||Thank you kindly Brian.
Much appreciated.
Regards
Saxon
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||Thanks for this useful description of the problem.
If we creates at temporary table in the procedure and fill data into it with
a function, will that cause a transaction too?
create table #temp(...)
insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||On Wed, 23 Nov 2005 03:36:11 -0800, winther wrote:

>Thanks for this useful description of the problem.
>If we creates at temporary table in the procedure and fill data into it wit
h
>a function, will that cause a transaction too?
>create table #temp(...)
>insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
Hi winther,
Yes. Every modification is automatically part of a transaction. If you
didn't start one explicitly, it will be started implicitly.
If SET IMPLICIT_TRANSACTION is OFF, the implicitly started transaction
will also be implicitly committed after each statement. With this
setting to ON, the server waits for an explicit COMMIT or ROLLBACK to
end the transaction.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There will be a transaction, but it won't put a lock on sysindexes because
the create table occurs apart from the transaction started for the insert.
"winther" <winther@.discussions.microsoft.com> wrote in message
news:0A8FC1D4-1571-45B8-91A8-29656995C285@.microsoft.com...
> Thanks for this useful description of the problem.
> If we creates at temporary table in the procedure and fill data into it
> with
> a function, will that cause a transaction too?
> create table #temp(...)
> insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
>
> "Brian Selzer" 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:[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.
>> > >> >
>> > >
>