deadlocks

[pardhi via SQLMonster.com]

Hi Everybody

I am new to sqlserver 2000.I know basics of locks.but i dont know how to
resolve deadlock issues.I am cofusing by reading articles with 90%
information and remaining 10% missing.Can any one help me which is the good
site to learn and resolve deadlocks.

Note: I create deadlock. when i try to trace deadlock using dbcc traceon
(1205,3604,-1).In error log showing nothing about the deadlock.

showing created traceon........ .

Any help would be appreciated.

--
Message posted via http://www.sqlmonster.com

[Dan Guzman]

Re: deadlocks

Specify trace flag 3605 instead of 3604 to write the deadlock info to the
SQL Server error log,

DBCC TRACEON (1205,3605,-1)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"pardhi via SQLMonster.com" <forum@nospam.S QLMonster.com> wrote in message
news:5cc8e2b282 894cce84e1ab206 f439136@SQLMons ter.com...[color=blue]
> Hi Everybody
>
> I am new to sqlserver 2000.I know basics of locks.but i dont know how to
> resolve deadlock issues.I am cofusing by reading articles with 90%
> information and remaining 10% missing.Can any one help me which is the
> good
> site to learn and resolve deadlocks.
>
> Note: I create deadlock. when i try to trace deadlock using dbcc traceon
> (1205,3604,-1).In error log showing nothing about the deadlock.
>
> showing created traceon........ .
>
> Any help would be appreciated.
>
> --
> Message posted via http://www.sqlmonster.com[/color]

[Erland Sommarskog]

Re: deadlocks

Dan Guzman (guzmanda@nospa m-online.sbcgloba l.net) writes:[color=blue]
> Specify trace flag 3605 instead of 3604 to write the deadlock info to the
> SQL Server error log,
>
> DBCC TRACEON (1205,3605,-1)[/color]

And to make it even better, use 1204, not 1205. 1205 writes deadlock
information as well I believe, but on an extremely detailed level.

Unfortunately, the output from 1204 is cryptic, and far from trivial
to understand.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: deadlocks

Hi

Have you looked at:

http://support.microsoft.com/kb/271509/EN-US/

Also "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 is a good source for understanding and resolving blocking.

John

"pardhi via SQLMonster.com" <forum@nospam.S QLMonster.com> wrote in message
news:5cc8e2b282 894cce84e1ab206 f439136@SQLMons ter.com...[color=blue]
> Hi Everybody
>
> I am new to sqlserver 2000.I know basics of locks.but i dont know how to
> resolve deadlock issues.I am cofusing by reading articles with 90%
> information and remaining 10% missing.Can any one help me which is the
> good
> site to learn and resolve deadlocks.
>
> Note: I create deadlock. when i try to trace deadlock using dbcc traceon
> (1205,3604,-1).In error log showing nothing about the deadlock.
>
> showing created traceon........ .
>
> Any help would be appreciated.
>
> --
> Message posted via http://www.sqlmonster.com[/color]

[reddy via SQLMonster.com]

Re: deadlocks

Hi Dan

I Created deadlock and opened new page and typed the command
DBCC TRACEON (1205,3605,-1).
I didn't see any deadlock message except

(End deadlock search 9232 a deadlock was not found)

but in enterprise manager showing spid 54 blocking and spid 55 blocked.

even tried (1204).

Can you please tell step by step how to see locking.

i am using standard vresion

Thanks

--
Message posted via http://www.sqlmonster.com

[reddy via SQLMonster.com]

Re: deadlocks

Hi Eland

I Created deadlock and opened new page and typed the command
DBCC TRACEON (1205,3605,-1).
I didn't see any deadlock message except

(End deadlock search 9232 a deadlock was not found)

but in enterprise manager showing spid 54 blocking and spid 55 blocked.

even tried (1204).

Can you please tell step by step how to see locking.

i am using standard version

Thanks

--
Message posted via http://www.sqlmonster.com

[reddy via SQLMonster.com]

Re: deadlocks

Hi John

Little bit confusing that article if any sent to me please.

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: deadlocks

reddy via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> I Created deadlock and opened new page and typed the command
> DBCC TRACEON (1205,3605,-1).
> I didn't see any deadlock message except
>
> (End deadlock search 9232 a deadlock was not found)
>
> but in enterprise manager showing spid 54 blocking and spid 55 blocked.
>
> even tried (1204).[/color]

Then you are probably not having a deadlock, just blocking. A deadlock
is when two processes block each other in a way so that none of them
can proceed without one of them being rolled back.

To produce a deadlock do this:

CREATE TABLE x(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
INSERT x (a, b) VALUES (1, 1)

And then run this from two windows in Query Analyzer:

BEGIN TRANSACTION
SELECT * FROM x WITH (HOLDLOCK)
WAITFOR DELAY '00:00:05'
UPDATE x SET b = 12
ROLLBACK TRANSACTION

One of these process will become a deadlock victim.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: deadlocks

Hi

Confusing in what way?

John

[Dan Guzman]

Re: deadlocks

As Erland mentioned, it seems you are experiencing blocking rather than
deadlocks. The deadlock trace flags are won't help in resolving a blocking
problem.

Long-term blocking is a symptom of long-running queries or transactions.
Blocking may be caused by poor application design or an indication that
tuning is needed. The key is to keep transactions and queries as short as
possible so that blocking locks are held only for short periods. It's a
good practice that one never waits on user response in an open transaction.

You can identify the resource blocking spid 55 with EXEC sp_lock 55. This
will show a status of WAIT for the resource in question. You can find the
name of an object by specifying the reported ObjId in the query SELECT
OBJECT_NAME(<Ob jId>). Use DBCC INPUTBUFFER or fn_get_sql to determine the
SQL statements involved in the blocking.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"reddy via SQLMonster.com" <forum@SQLMonst er.com> wrote in message
news:8e97e01550 4a44bf96d11d443 d671bea@SQLMons ter.com...[color=blue]
> Hi Dan
>
> I Created deadlock and opened new page and typed the command
> DBCC TRACEON (1205,3605,-1).
> I didn't see any deadlock message except
>
> (End deadlock search 9232 a deadlock was not found)
>
> but in enterprise manager showing spid 54 blocking and spid 55 blocked.
>
> even tried (1204).
>
> Can you please tell step by step how to see locking.
>
> i am using standard vresion
>
> Thanks
>
> --
> Message posted via http://www.sqlmonster.com[/color]

[reddy via SQLMonster.com]

Re: deadlocks

Thanks a lot Dan.That helps me.

--
Message posted via http://www.sqlmonster.com

[reddy via SQLMonster.com]

Re: deadlocks

Thanks a lot Erland.That helps me.

--
Message posted via http://www.sqlmonster.com

[reddy via SQLMonster.com]

Re: deadlocks


Hi John

Nevermind i got my problem fixed
Thanks a lot.

--
Message posted via http://www.sqlmonster.com

[John Bell]

Re: deadlocks

Hi

I am glad to hear that.

You may want to try out the deadlock examples in "Inside SQL Server
2000" in conjunction with the blocker script.

John