Deadlocks

We have a deployed website with many concurrent users who are mostly
reading from the database although there are frequent inserts/updates
as well. At scheduled intervals, we run multiple matching queries
against a table with around 120,000 rows. We used to run it WITH
(NOLOCK), but we decided that the default behavior of skipping over
noncommited transactions was acceptible. However, whereas before it
might get a deadlock once or twice a day, after taking out the WITH
(NOLOCK) we are getting up to 9 deadlocks every time it is run! Does
anyone know why these (read-only) queries are deadlocking so much? Oh,
if it helps, the scheduled queries are sequential so they are not
interfering with each other. Here is some (renamed) DDL if it helps:
CREATE PROCEDURE [mycompany].[my_sp] (
@.id int,
@.age int)
AS
SELECT
f.code_alpha,
f.code_beta,
f.low,
f.high
FROM foo AS f
JOIN bar AS b ON f.site = b.site
WHERE
DATEDIFF(hh, f.created, getdate()) <= @.age AND
DATEDIFF(hh, f.created, getdate()) > 0 AND
b.id = @.id AND
((b.category1 = 1 AND f.category = 1) OR
(b.category2 = 1 AND f.category = 2) OR
(b.category3 = 1 AND f.category = 3) OR
(b.category4 = 1 AND f.category = 4) OR
(b.category5 = 1 AND f.category = 5) OR
(b.category6 = 1 AND f.category = 6)) AND
(b.low <= f.high AND
b.high >= f.low) AND
((b.policy = 1 AND f.policy_alpha IN (1, 3)) OR
(b.policy = 2 AND f.policy_beta IN (1, 3)) OR
(b.policy = 3 AND f.policy_alpha IN (1, 3) AND f.policy_beta IN (1,
3)) OR
b.policy = 0) AND
f.code_alpha >= b.code_alpha AND
f.code_beta >= b.code_beta AND
f.confirmed = 1 AND
f.valid = 1
GO(steve.edison@.gmail.com) writes:
> We have a deployed website with many concurrent users who are mostly
> reading from the database although there are frequent inserts/updates
> as well. At scheduled intervals, we run multiple matching queries
> against a table with around 120,000 rows. We used to run it WITH
> (NOLOCK), but we decided that the default behavior of skipping over
> noncommited transactions was acceptible. However, whereas before it
> might get a deadlock once or twice a day, after taking out the WITH
> (NOLOCK) we are getting up to 9 deadlocks every time it is run! Does
> anyone know why these (read-only) queries are deadlocking so much? Oh,
> if it helps, the scheduled queries are sequential so they are not
> interfering with each other. Here is some (renamed) DDL if it helps:
It's about impossible to tell why queries we know little about deadlock.
I would guess, though, that they clash with some updating process.
Have you look at the deadlock trace? If you have not enabled this, you
should do that. From Enterprise Manager, specify -T 1204 and -T 3605 as
startup parameters, and restart the server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

deadlock issue in sql server 2000 enterprise edition version 8.00.

we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
--
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>|||thanks for your feedback.
I have seen the article on deadlock but any simpler way to handle it.
like a sp_configure statement
"Ryan" wrote:
> To see what version you're on issue the following :-
> SELECT SERVERPROPERTY('Edition')
> This article may provide help with your deadlocking :-
> http://support.microsoft.com/kb/271509/
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> > we have installed sql server 2000 enterprise edition on our erp server.
> > We are facing frequent deadlock problem ie one process blocks the other
> > process frequently.
> > The compatibility of the databases has been set to 80.
> > First of all whether the version is that of enterprise edition ?
> > secondly any particular setting to resolve the deadlock issues ?
> >
> >
>
>|||I'm afriad there is no quick fix for deadlocking, there are some traceflags
you can turn on to give you detailed information about the nature of your
deadlock :-
DBCC TRACEON (1204,3605,-1)
This will write deadlock information to the SQL Server Errorlog, which can
be read using sp_ReadErrorLog.
Here's a good article about Anti-Blocking strategies :-
http://vyaskn.tripod.com/anti_blocking_strategies.htm
HTH. Ryan
"Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> thanks for your feedback.
> I have seen the article on deadlock but any simpler way to handle it.
> like a sp_configure statement
> "Ryan" wrote:
>> To see what version you're on issue the following :-
>> SELECT SERVERPROPERTY('Edition')
>> This article may provide help with your deadlocking :-
>> http://support.microsoft.com/kb/271509/
>> --
>> HTH. Ryan
>>
>> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
>> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
>> > we have installed sql server 2000 enterprise edition on our erp server.
>> > We are facing frequent deadlock problem ie one process blocks the other
>> > process frequently.
>> > The compatibility of the databases has been set to 80.
>> > First of all whether the version is that of enterprise edition ?
>> > secondly any particular setting to resolve the deadlock issues ?
>> >
>> >
>>|||thanks
"Ryan" wrote:
> I'm afriad there is no quick fix for deadlocking, there are some traceflags
> you can turn on to give you detailed information about the nature of your
> deadlock :-
> DBCC TRACEON (1204,3605,-1)
> This will write deadlock information to the SQL Server Errorlog, which can
> be read using sp_ReadErrorLog.
> Here's a good article about Anti-Blocking strategies :-
> http://vyaskn.tripod.com/anti_blocking_strategies.htm
>
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
> message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> > thanks for your feedback.
> >
> > I have seen the article on deadlock but any simpler way to handle it.
> > like a sp_configure statement
> >
> > "Ryan" wrote:
> >
> >> To see what version you're on issue the following :-
> >>
> >> SELECT SERVERPROPERTY('Edition')
> >>
> >> This article may provide help with your deadlocking :-
> >>
> >> http://support.microsoft.com/kb/271509/
> >>
> >> --
> >> HTH. Ryan
> >>
> >>
> >> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> >> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> >> > we have installed sql server 2000 enterprise edition on our erp server.
> >> > We are facing frequent deadlock problem ie one process blocks the other
> >> > process frequently.
> >> > The compatibility of the databases has been set to 80.
> >> > First of all whether the version is that of enterprise edition ?
> >> > secondly any particular setting to resolve the deadlock issues ?
> >> >
> >> >
> >>
> >>
> >>
>
>sql

deadlock issue in sql server 2000 enterprise edition version 8.00.

we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>

DDL from XSD?

Apologize if this is a frequent question. Is there some tool that can read
an XSD and create a set of table definitions into which the corresponding
XML doc could be shredded?
Conceptually, like this:
XSD defines a parent-child relationship (cust-order); output from tool would
be 2 tables with a FK definition
Thanks, SteveHi Steve,
XMLSpy? 2007 Professional Edition can handle this request for you. Please
feel free to download and evaluate any of our products.
More details on this feature can be found here,
http://www.altova.com/features_database.html
Best regards,
... Jerry Sheehan
... Pre-Sales Engineer
... Altova, Inc.
"Steve Mc" wrote:

> Apologize if this is a frequent question. Is there some tool that can rea
d
> an XSD and create a set of table definitions into which the corresponding
> XML doc could be shredded?
> Conceptually, like this:
> XSD defines a parent-child relationship (cust-order); output from tool wou
ld
> be 2 tables with a FK definition
> Thanks, Steve
>
>|||Look at the schema gen option on the SQLXML Bulkload object.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:unEHSgQRHHA.4060@.TK2MSFTNGP03.phx.gbl...
> Apologize if this is a frequent question. Is there some tool that can
> read an XSD and create a set of table definitions into which the
> corresponding XML doc could be shredded?
> Conceptually, like this:
> XSD defines a parent-child relationship (cust-order); output from tool
> would be 2 tables with a FK definition
> Thanks, Steve
>|||On Jan 31, 2:18 am, "Steve Mc" <stev...@.zillow.com> wrote:
> Apologize if this is a frequent question. Is there some tool that can rea
d
> an XSD and create a set of table definitions into which the corresponding
> XML doc could be shredded?
> Conceptually, like this:
> XSD defines a parent-child relationship (cust-order); output from tool wou
ld
> be 2 tables with a FK definition
> Thanks, Steve
XML Differencing: http://www.stylusstudio.com/xml_differencing.html
XML Differencing Tutorial: http://www.stylusstudio.com/videos/xmldiff1/
xmldiff1.html
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.com|||Whoops sorry ignore that last post. Wrong thread. Sorry!!!

DDL from XSD?

Apologize if this is a frequent question. Is there some tool that can read
an XSD and create a set of table definitions into which the corresponding
XML doc could be shredded?
Conceptually, like this:
XSD defines a parent-child relationship (cust-order); output from tool would
be 2 tables with a FK definition
Thanks, Steve
Hi Steve,
XMLSpy? 2007 Professional Edition can handle this request for you. Please
feel free to download and evaluate any of our products.
More details on this feature can be found here,
http://www.altova.com/features_database.html
Best regards,
... Jerry Sheehan
... Pre-Sales Engineer
... Altova, Inc.
"Steve Mc" wrote:

> Apologize if this is a frequent question. Is there some tool that can read
> an XSD and create a set of table definitions into which the corresponding
> XML doc could be shredded?
> Conceptually, like this:
> XSD defines a parent-child relationship (cust-order); output from tool would
> be 2 tables with a FK definition
> Thanks, Steve
>
>
|||Look at the schema gen option on the SQLXML Bulkload object.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:unEHSgQRHHA.4060@.TK2MSFTNGP03.phx.gbl...
> Apologize if this is a frequent question. Is there some tool that can
> read an XSD and create a set of table definitions into which the
> corresponding XML doc could be shredded?
> Conceptually, like this:
> XSD defines a parent-child relationship (cust-order); output from tool
> would be 2 tables with a FK definition
> Thanks, Steve
>
|||On Jan 31, 2:18 am, "Steve Mc" <stev...@.zillow.com> wrote:
> Apologize if this is a frequent question. Is there some tool that can read
> an XSD and create a set of table definitions into which the corresponding
> XML doc could be shredded?
> Conceptually, like this:
> XSD defines a parent-child relationship (cust-order); output from tool would
> be 2 tables with a FK definition
> Thanks, Steve
XML Differencing: http://www.stylusstudio.com/xml_differencing.html
XML Differencing Tutorial: http://www.stylusstudio.com/videos/xmldiff1/
xmldiff1.html
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.com
|||Whoops sorry ignore that last post. Wrong thread. Sorry!!!