Deadlocks?

hey can i get idea about deadlocks?
how to avoide deadlock actually?
what are the different stretegies used for this technique?> Continue with latest book by Kalen Delaney.
Which one is this Dejan? Are you refering to "Inside SQL Server" ?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Start with Books OnLine, searc for deadlock, there are quite a few topics.
> Continue with latest book by Kalen Delaney.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > hey can i get idea about deadlocks?
> > how to avoide deadlock actually?
> > what are the different stretegies used for this technique?
>|||Kim's book was very good on locking in general, but pretty sparse when it
came to dealing with deadlocks.
Just my opinion.
Bob Castleman
SuccessWare Software
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Start with Books OnLine, searc for deadlock, there are quite a few topics.
> Continue with latest book by Kalen Delaney.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > hey can i get idea about deadlocks?
> > how to avoide deadlock actually?
> > what are the different stretegies used for this technique?
>|||I should be more precise. It is "Hands-On SQL Server 2000 : Troubleshooting
Locking and Blocking" ebook, available at
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Vinodk" <vinodk_sct@.hotmail.com> wrote in message
news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > Continue with latest book by Kalen Delaney.
> Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > Start with Books OnLine, searc for deadlock, there are quite a few
topics.
> > Continue with latest book by Kalen Delaney.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > hey can i get idea about deadlocks?
> > > how to avoide deadlock actually?
> > > what are the different stretegies used for this technique?
> >
> >
>|||Vinod,
I guess Dejan is referring to..
Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking (ebook)
By Kalen Delaney
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Vinodk" <vinodk_sct@.hotmail.com> wrote in message
news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > Continue with latest book by Kalen Delaney.
> Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > Start with Books OnLine, searc for deadlock, there are quite a few
topics.
> > Continue with latest book by Kalen Delaney.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > hey can i get idea about deadlocks?
> > > how to avoide deadlock actually?
> > > what are the different stretegies used for this technique?
> >
> >
>|||First of all I haven't read everything, for example I haven't read the last
book of Kalen.
But most documentation I have seen describe deadlocks which can be expected,
I haven't seen (or missed them) descriptions of deadlocks that are totally
unexpected.
We had a deadlock between one transaction and a single select statement not
run in a transaction. The statement selected only one row of one table, but
still deadlocked with the transaction. From a select you can not see which
locks are used during the transaction, because it is so fast. Trapping the
deadlock and seeing which locks where used was difficult because when the
deadlock is detected by sql-server the information (read locks) disappears.
Because we did not suspect the 'select' to cause any problems it
took a long time before we could determine the cause of the deadlock
and solve the problem. (The deadlock was difficult to produce to begin
with).
In the end, with the database we had then, I could recreate the deadlock at
will within the QA. Outside that database even with the same data I could
not recreate the problem.
So do not exclude simple select statements when hunting down the culprits of
deadlocks.
Ben Brugman
"hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> hey can i get idea about deadlocks?
> how to avoide deadlock actually?
> what are the different stretegies used for this technique?|||Thankx Dinesh and Dejan ... Looks an good book to have ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eBLD0fWbDHA.2960@.tk2msftngp13.phx.gbl...
> Vinod,
> I guess Dejan is referring to..
> Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking (ebook)
> By Kalen Delaney
>
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Vinodk" <vinodk_sct@.hotmail.com> wrote in message
> news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > > Continue with latest book by Kalen Delaney.
> >
> > Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD
> > http://www.extremeexperts.com
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > Start with Books OnLine, searc for deadlock, there are quite a few
> topics.
> > > Continue with latest book by Kalen Delaney.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > > Please reply only to the newsgroups.
> > > PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > > hey can i get idea about deadlocks?
> > > > how to avoide deadlock actually?
> > > > what are the different stretegies used for this technique?
> > >
> > >
> >
> >
>sql

deadlock victim

Here is the exception I am getting, any idea why this might be happening:

Exception: ThisMethod is done: let this go: Exception:

Transaction (Process ID 74) was deadlocked on lock |

communication buffer resources with another process and has been chosen as the deadlock victim.

Rerun the transaction.Stack Trace:at

Jim,

Please check the following article for some insight:
http://support.microsoft.com/kb/224453|||

deadlock is a contention by two process on a resource

in a deadlock situtation one is choose as a deadlock victim and are rolled back

and the other rolls forward

you just ave to rerun the deadlock victim

|||

Thanks for the reply.

While users are working in the user interface I am inserting data to the table with another that is back end for the user interface. Although we do insert and update I do not think that user are dealing with the same data row, so I am assuming I should not be receiving deadlock. Does SQL server do RowLock or TableLock. How can I get my insert and update work on the RowLock. Can you give me sample sql statement.

|||sqlserver will escalate the lock (from row/page to table) as it sees

fit. While deadlock is not desired, it's necessary in order to allow

the system to proceed forward. Imagine a case where the system just

sits in blocking/contention. That would not be good for either user,

would it. So, the best you can do is to follow the recommendations in

the KB I've posted.

Here is some info on sqlserver locking:

http://msdn2.microsoft.com/en-us/library/ms190615.aspx

And some example of implementing rowlock (again, sql can escalate the lock if it wants to):

http://groups.google.com/groups?q=sqlserver+oj+rowlock+update

deadlock victim

Here is the exception I am getting, any idea why this might be happening:

Exception: ThisMethod is done: let this go: Exception:

Transaction (Process ID 74) was deadlocked on lock |

communication buffer resources with another process and has been chosen as the deadlock victim.

Rerun the transaction. Stack Trace: at

This previous thread on the deadlock topic, along with the links it contains, might help you: Deadlock|||

Thanks for the reply.

While users are working in the user interface I am inserting data to the table with another that is back end for the user interface. Although we do insert and update I do not think that user are dealing with the same data row, so I am assuming I should not be receiving deadlock. Does SQL server do RowLock or TableLock. How can I get my insert and update work on the RowLock. Can you give me sample sql statement.

Deadlock question

I recently started seeing a new description in the deadlock event. Anyone
have any idea what the underlying issue might be?
"Parallel query worker thread was involved in a deadlock"
Thanks
Richard
Hi Richard
"Richard Douglass" wrote:

> I recently started seeing a new description in the deadlock event. Anyone
> have any idea what the underlying issue might be?
> "Parallel query worker thread was involved in a deadlock"
> Thanks
> Richard
>
Your query has been chosen to be be run in parallel, check out the topic
"Degree of Parallelism" in books online for more background.
From http://msdn2.microsoft.com/en-us/library/ms178104(SQL.90).aspx
"Coordinator, producer, or consumer threads associated with an exchange port
may block each other causing a deadlock usually when including at least one
other process that is not a part of the parallel query. Also, when a parallel
query starts execution, SQL Server determines the degree of parallelism, or
the number of worker threads, based upon the current workload. If the system
workload unexpectedly changes, for example, where new queries start running
on the server or the system runs out of worker threads, then a deadlock could
occur."
If you use the MAXDOP option to specify fewer processors you should not get
this, changing settings such as "cost threshold for parallelism" would affect
the whole system so it is probably not a great idea to change that.
John

Deadlock question

I recently started seeing a new description in the deadlock event. Anyone
have any idea what the underlying issue might be'
"Parallel query worker thread was involved in a deadlock"
Thanks
RichardHi Richard
"Richard Douglass" wrote:
> I recently started seeing a new description in the deadlock event. Anyone
> have any idea what the underlying issue might be'
> "Parallel query worker thread was involved in a deadlock"
> Thanks
> Richard
>
Your query has been chosen to be be run in parallel, check out the topic
"Degree of Parallelism" in books online for more background.
From http://msdn2.microsoft.com/en-us/library/ms178104(SQL.90).aspx
"Coordinator, producer, or consumer threads associated with an exchange port
may block each other causing a deadlock usually when including at least one
other process that is not a part of the parallel query. Also, when a parallel
query starts execution, SQL Server determines the degree of parallelism, or
the number of worker threads, based upon the current workload. If the system
workload unexpectedly changes, for example, where new queries start running
on the server or the system runs out of worker threads, then a deadlock could
occur."
If you use the MAXDOP option to specify fewer processors you should not get
this, changing settings such as "cost threshold for parallelism" would affect
the whole system so it is probably not a great idea to change that.
Johnsql

Deadlock question

I recently started seeing a new description in the deadlock event. Anyone
have any idea what the underlying issue might be'
"Parallel query worker thread was involved in a deadlock"
Thanks
RichardHi Richard
"Richard Douglass" wrote:

> I recently started seeing a new description in the deadlock event. Anyone
> have any idea what the underlying issue might be'
> "Parallel query worker thread was involved in a deadlock"
> Thanks
> Richard
>
Your query has been chosen to be be run in parallel, check out the topic
"Degree of Parallelism" in books online for more background.
From http://msdn2.microsoft.com/en-us/library/ms178104(SQL.90).aspx
"Coordinator, producer, or consumer threads associated with an exchange port
may block each other causing a deadlock usually when including at least one
other process that is not a part of the parallel query. Also, when a paralle
l
query starts execution, SQL Server determines the degree of parallelism, or
the number of worker threads, based upon the current workload. If the system
workload unexpectedly changes, for example, where new queries start running
on the server or the system runs out of worker threads, then a deadlock coul
d
occur."
If you use the MAXDOP option to specify fewer processors you should not get
this, changing settings such as "cost threshold for parallelism" would affec
t
the whole system so it is probably not a great idea to change that.
John

Deadlock

I recently started seeing a new description in the deadlock event. Anyone
have any idea what the underlying issue might be?
"Parallel query worker thread was involved in a deadlock"
Thanks
Richard
Hi Richard
"Richard Douglass" wrote:

> I recently started seeing a new description in the deadlock event. Anyone
> have any idea what the underlying issue might be?
> "Parallel query worker thread was involved in a deadlock"
> Thanks
> Richard
>
There are quite a few posts for this message if you googled it, such as
http://tinyurl.com/2e346d
John

DeadLock

Hello
we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
dead lock issues.
any idea?
Regards
GopiHave you enabled
DBCC TRACEON (1204) ?
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||adding, error message.
Message
Save Profile Failed Transaction (Process ID 75) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
Source
MicroClear.EnterpriseSolutions.DataProfileServices
Stack Trace
at
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileService.SaveProfileFromXmlDocument(XmlDocument
profileDocument, ArrayList updateOtherProfiles, Boolean insertNotExist,
ProfileParam[] updateProfiles, String MainProfileFieldsList, Boolean
byPassSecurity) at
MicroClear.EnterpriseSolutions.Web.Services.ActionServices.Actor.ProfileHelpers.SaveProfile(ArrayList
updateOtherProfiles, Boolean insertNotExist) at
MicroClear.Actors.eFormsAdmin.AdmSubmitRequest.NewSetFlag() in
c:\eForms\Actors\eFormsAdmin\AdmSubmitRequest.cs:line 2528
Base Exception
Int32 SaveProfileFromXmlDocument(System.Xml.XmlDocument,
System.Collections.ArrayList, Boolean,
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileParam[],
System.String, Boolean)
Actor
SetFlag_2
Module
SubmissionService
Framework Msg
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||No, we didnt trace but using SQL profiler, trying to identify.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Have you enabled
> DBCC TRACEON (1204) ?
> "Gopi" <gopi2it@.gmail.com> wrote in message
> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
>> dead lock issues.
>> any idea?
>> Regards
>> Gopi
>|||Gopi
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
"Gopi" <gopi2it@.gmail.com> wrote in message
news:eH$LooSeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> No, we didnt trace but using SQL profiler, trying to identify.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Have you enabled
>> DBCC TRACEON (1204) ?
>> "Gopi" <gopi2it@.gmail.com> wrote in message
>> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are
>> facing dead lock issues.
>> any idea?
>> Regards
>> Gopi
>>
>

Deadlock

I recently started seeing a new description in the deadlock event. Anyone
have any idea what the underlying issue might be'
"Parallel query worker thread was involved in a deadlock"
Thanks
RichardHi Richard
"Richard Douglass" wrote:
> I recently started seeing a new description in the deadlock event. Anyone
> have any idea what the underlying issue might be'
> "Parallel query worker thread was involved in a deadlock"
> Thanks
> Richard
>
There are quite a few posts for this message if you googled it, such as
http://tinyurl.com/2e346d
John

Deadlock

I recently started seeing a new description in the deadlock event. Anyone
have any idea what the underlying issue might be'
"Parallel query worker thread was involved in a deadlock"
Thanks
RichardHi Richard
"Richard Douglass" wrote:

> I recently started seeing a new description in the deadlock event. Anyone
> have any idea what the underlying issue might be'
> "Parallel query worker thread was involved in a deadlock"
> Thanks
> Richard
>
There are quite a few posts for this message if you googled it, such as
http://tinyurl.com/2e346d
John

dbmail doesn't rely on IIS SMTP, how to set bounced email redirect email etc.? thanks

Under IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.

Any idea?

thanks

DB Mail sends to an SMTP server. You need to configure the SMTP server to return bounced emails.

Normally, this goes to "postmaster,sender" as a default.

|||

That makes sense, I just found I set dbmail profile to use relay server, which runs Kerio email, I guess it is Kerio SMTP which send out the email, then the setting need to be done on Kerio.

Thanks

DBException (Communication link failure)

Hi,
with SQL 2000 and Windows 2000, we're getting lots of this error message
"Communication link failure" from our C++ application.
Any idea why?

Thanks,neo (second714@.hotmail.com) writes:
> with SQL 2000 and Windows 2000, we're getting lots of this error message
> "Communication link failure" from our C++ application.
> Any idea why?

There are many possibilities.

One is of course that you have a network problem.

Another is that you have some SQL code that cause the SQL Server process
for the connection to crash - access violation, stack overflow - in which
case SQL Server closes the connection quite brutally. In this case, the
SQL Server error log will include a stack dump, which in time is correlated
with the application being disconnected. Such a crash, by the way, is due
to bugs in SQL Server, not in your SQL code.

There are probably several other reasons, but with the miniscule of
information you have posted, I stop here.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.
We have two SQL server machines both with problems.
one with Windows 2000 SP3 and SQL 2000 SP3
another one with XP and SQL 2000 SP3
We'll get the latest OS SP for both machines
and have to wait a couple of date to get IT.

I didn't want to put this information before
because I wanted to get all the possible answers.

we'll see after OS update and if it's not fixed,
then I'll come back to this news group.

Thanks a lot,