Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thx for Geoff's quick reply.
But in my test env all resources seem to be in the same server (almost
certainly so), or even in the same db. So what things I may possibly
have overlooked?
BTW, deadlock detection in general woithout bounds is NP-complete, so
there is reason to think that either there are practical bounds on the
algorithm's capacity or it is infeasible in certain cases.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you give us an example of an unresolved/undetected deadlock? What
resources are involved? Are you sure it is a deadlock, and not a long
blocking chain?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"cyu" <cyu@.devdex.com> wrote in message
news:%23MBo8yLBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Thx for Geoff's quick reply.
> But in my test env all resources seem to be in the same server (almost
> certainly so), or even in the same db. So what things I may possibly
> have overlooked?
> BTW, deadlock detection in general woithout bounds is NP-complete, so
> there is reason to think that either there are practical bounds on the
> algorithm's capacity or it is infeasible in certain cases.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label case. Show all posts
Showing posts with label case. Show all posts
Monday, March 19, 2012
Is SQL server's deadlock detection complete
Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thx for Geoff's quick reply.
But in my test env all resources seem to be in the same server (almost
certainly so), or even in the same db. So what things I may possibly
have overlooked?
BTW, deadlock detection in general woithout bounds is NP-complete, so
there is reason to think that either there are practical bounds on the
algorithm's capacity or it is infeasible in certain cases.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you give us an example of an unresolved/undetected deadlock? What
resources are involved? Are you sure it is a deadlock, and not a long
blocking chain?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"cyu" <cyu@.devdex.com> wrote in message
news:%23MBo8yLBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Thx for Geoff's quick reply.
> But in my test env all resources seem to be in the same server (almost
> certainly so), or even in the same db. So what things I may possibly
> have overlooked?
> BTW, deadlock detection in general woithout bounds is NP-complete, so
> there is reason to think that either there are practical bounds on the
> algorithm's capacity or it is infeasible in certain cases.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thx for Geoff's quick reply.
But in my test env all resources seem to be in the same server (almost
certainly so), or even in the same db. So what things I may possibly
have overlooked?
BTW, deadlock detection in general woithout bounds is NP-complete, so
there is reason to think that either there are practical bounds on the
algorithm's capacity or it is infeasible in certain cases.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you give us an example of an unresolved/undetected deadlock? What
resources are involved? Are you sure it is a deadlock, and not a long
blocking chain?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"cyu" <cyu@.devdex.com> wrote in message
news:%23MBo8yLBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Thx for Geoff's quick reply.
> But in my test env all resources seem to be in the same server (almost
> certainly so), or even in the same db. So what things I may possibly
> have overlooked?
> BTW, deadlock detection in general woithout bounds is NP-complete, so
> there is reason to think that either there are practical bounds on the
> algorithm's capacity or it is infeasible in certain cases.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Is SQL server's deadlock detection complete
Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Friday, February 24, 2012
Is password case sensitive on SQL Server?
When I configured the System DSN, seems no matter what case I entered the pa
ssword in, they all worked. Does that mean, password is not case sensitive
on SQL Server?
BingThat depends on the collation or sort order of your server. Do a
sp_helpsort. If the server sort order is case-insensitive, then your
passwords are as well. Nice feature of SQL 2000, read this for more info.
Remember the default collation of SQL (1252) is not case-sensitive
http://www.microsoft.com/sql/techin...sesensitive.asp
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Bing Du" <anonymous@.discussions.microsoft.com> wrote in message
news:C93FFA88-A7B1-4D5E-8B38-BD4F8484B3A0@.microsoft.com...
password in, they all worked. Does that mean, password is not case
sensitive on SQL Server?
users, your applications and company can handle it, it is a great way to
secure SQL. Heck, SQL 2000 SP 3a tries to even force it. Gets you closer
to the goal of single sign-on, but then of course you have to worry about
everything being right so you don't get trusted connection login errors.
BTW, by sending that article about the sort order, I think that is
ridiculous. If you have databases with sort orders other than what the
master database has, you really have to watch it with temp tables and other
items in the tempdb. I've seen many a stored procedure have to be rewritten
because some database got attached to a server with a different sort order,
which caused a problem with the stored procedure making temp tables.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Bing Du" <anonymous@.discussions.microsoft.com> wrote in message
news:C93FFA88-A7B1-4D5E-8B38-BD4F8484B3A0@.microsoft.com...
password in, they all worked. Does that mean, password is not case
sensitive on SQL Server?
th MS SQL server, it will take me more time and learning to get your message
fully digested.
Bing
ssword in, they all worked. Does that mean, password is not case sensitive
on SQL Server?
BingThat depends on the collation or sort order of your server. Do a
sp_helpsort. If the server sort order is case-insensitive, then your
passwords are as well. Nice feature of SQL 2000, read this for more info.
Remember the default collation of SQL (1252) is not case-sensitive
http://www.microsoft.com/sql/techin...sesensitive.asp
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Bing Du" <anonymous@.discussions.microsoft.com> wrote in message
news:C93FFA88-A7B1-4D5E-8B38-BD4F8484B3A0@.microsoft.com...
quote:
> When I configured the System DSN, seems no matter what case I entered the
password in, they all worked. Does that mean, password is not case
sensitive on SQL Server?
quote:|||Here's a great suggestion to, go Windows Authentication. If you, your
> Bing
users, your applications and company can handle it, it is a great way to
secure SQL. Heck, SQL 2000 SP 3a tries to even force it. Gets you closer
to the goal of single sign-on, but then of course you have to worry about
everything being right so you don't get trusted connection login errors.
BTW, by sending that article about the sort order, I think that is
ridiculous. If you have databases with sort orders other than what the
master database has, you really have to watch it with temp tables and other
items in the tempdb. I've seen many a stored procedure have to be rewritten
because some database got attached to a server with a different sort order,
which caused a problem with the stored procedure making temp tables.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Bing Du" <anonymous@.discussions.microsoft.com> wrote in message
news:C93FFA88-A7B1-4D5E-8B38-BD4F8484B3A0@.microsoft.com...
quote:
> When I configured the System DSN, seems no matter what case I entered the
password in, they all worked. Does that mean, password is not case
sensitive on SQL Server?
quote:|||Thanks very much for the information, Andy. Since I'm not quite familiar wi
> Bing
th MS SQL server, it will take me more time and learning to get your message
fully digested.
Bing
Subscribe to:
Posts (Atom)