Consulta deadlock

29/08/2005 - 16:10 por Pao | Informe spam
Necesito que por favor me ayuden a determinar esto que me reporta mi log de
SQLserver 2000 con sp3 en windows 2000 con sp4:

COmo sé cual fue la sentencia o los objetos reportados.

2005-08-29 06:35:40.43 spid3 -
2005-08-29 06:35:40.43 spid3 Starting deadlock search 5358

2005-08-29 06:35:40.43 spid3 Target Resource Owner:
2005-08-29 06:35:40.43 spid3 ResType:ExchangeId Stype:'AND' SPID:260
ECID:0 Ec:(0x66ED3500) Value:0x42bea620
2005-08-29 06:35:40.43 spid3 Node:1 ResType:ExchangeId Stype:'AND'
SPID:260 ECID:0 Ec:(0x66ED3500) Value:0x42bea620
2005-08-29 06:35:40.43 spid3
2005-08-29 06:35:40.43 spid3 End deadlock search 5358 ... a deadlock was
not found.
2005-08-29 06:35:40.43 spid3 -

Preguntas similare

Leer las respuestas

#1 Mauro
29/08/2005 - 16:22 | Informe spam
tenes una bandera activada que logea los deadlooks pero en este caso no
existe ninguno "a deadlock was
not found."
preocupate cuando diga que lo encontro, y en ese caso el log te va a dar mas
informacion.

"Pao" wrote in message
news:
Necesito que por favor me ayuden a determinar esto que me reporta mi log


de
SQLserver 2000 con sp3 en windows 2000 con sp4:

COmo sé cual fue la sentencia o los objetos reportados.

2005-08-29 06:35:40.43 spid3 -
2005-08-29 06:35:40.43 spid3 Starting deadlock search 5358

2005-08-29 06:35:40.43 spid3 Target Resource Owner:
2005-08-29 06:35:40.43 spid3 ResType:ExchangeId Stype:'AND' SPID:260
ECID:0 Ec:(0x66ED3500) Value:0x42bea620
2005-08-29 06:35:40.43 spid3 Node:1 ResType:ExchangeId Stype:'AND'
SPID:260 ECID:0 Ec:(0x66ED3500) Value:0x42bea620
2005-08-29 06:35:40.43 spid3
2005-08-29 06:35:40.43 spid3 End deadlock search 5358 ... a deadlock


was
not found.
2005-08-29 06:35:40.43 spid3 -

Respuesta Responder a este mensaje
#2 Pao
29/08/2005 - 16:52 | Informe spam
Ahora si me dió uno, pero como lo interpreto me puedes ayudar:
2005-08-28 16:19:33.82 spid3 -
2005-08-28 16:19:33.82 spid3 Starting deadlock search 5299

2005-08-28 16:19:33.82 spid3 Target Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60
2005-08-28 16:19:33.84 spid3 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200
2005-08-28 16:19:33.84 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Deadlock cycle was encountered
verifying cycle
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200 Cost:(0/3C)
2005-08-28 16:19:33.84 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3

Deadlock encountered Printing deadlock information
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Wait-for graph
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Node:1
2005-08-28 16:19:33.84 spid3 RID: 5:1:302:2 CleanCnt:1
Mode: X Flags: 0x2
2005-08-28 16:19:33.84 spid3 Grant List 0::
2005-08-28 16:19:33.84 spid3 Owner:0x65e952c0 Mode: X Flg:0x0
Ref:1 Life:02000000 SPID:247 ECID:0
2005-08-28 16:19:33.84 spid3 SPID: 247 ECID: 0 Statement Type: DELETE
Line #: 1
2005-08-28 16:19:33.84 spid3 Input Buf: RPC Event: sp_executesql;1
2005-08-28 16:19:33.84 spid3 Requested By:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Node:2
2005-08-28 16:19:33.84 spid3 KEY: 5:1522104463:2 (6102b12ab08d)
CleanCnt:1 Mode: U Flags: 0x0
2005-08-28 16:19:33.84 spid3 Grant List 0::
2005-08-28 16:19:33.84 spid3 Owner:0x65e94440 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:181 ECID:0
2005-08-28 16:19:33.84 spid3 SPID: 181 ECID: 0 Statement Type: SELECT
Line #: 1
2005-08-28 16:19:33.84 spid3 Input Buf: RPC Event: sp_executesql;1
2005-08-28 16:19:33.84 spid3 Requested By:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: X
SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200 Cost:(0/3C)
2005-08-28 16:19:33.84 spid3 Victim Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 End deadlock search 5299 ... a deadlock was
found.
2005-08-28 16:19:33.84 spid3 -

2005-08-28 16:19:33.84 spid3 -
2005-08-28 16:19:33.84 spid3 Starting deadlock search 5300

2005-08-28 16:19:33.84 spid3 Target Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:245 ECID:0 Ec:(0x4434D500) Value:0x65e94460
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
S SPID:245 ECID:0 Ec:(0x4434D500) Value:0x65e94460
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 End deadlock search 5300 ... a deadlock was
not found.
2005-08-28 16:19:33.84 spid3 -

2005-08-28 16:19:33.84 spid3 -
2005-08-28 16:19:33.84 spid3 Starting deadlock search 5301

2005-08-28 16:19:33.84 spid3 Target Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: X
SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 End deadlock search 5301 ... a deadlock was
not found.


"Mauro" wrote:

tenes una bandera activada que logea los deadlooks pero en este caso no
existe ninguno "a deadlock was
not found."
preocupate cuando diga que lo encontro, y en ese caso el log te va a dar mas
informacion.

"Pao" wrote in message
news:
> Necesito que por favor me ayuden a determinar esto que me reporta mi log
de
> SQLserver 2000 con sp3 en windows 2000 con sp4:
>
> COmo sé cual fue la sentencia o los objetos reportados.
>
> 2005-08-29 06:35:40.43 spid3 -
> 2005-08-29 06:35:40.43 spid3 Starting deadlock search 5358
>
> 2005-08-29 06:35:40.43 spid3 Target Resource Owner:
> 2005-08-29 06:35:40.43 spid3 ResType:ExchangeId Stype:'AND' SPID:260
> ECID:0 Ec:(0x66ED3500) Value:0x42bea620
> 2005-08-29 06:35:40.43 spid3 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:260 ECID:0 Ec:(0x66ED3500) Value:0x42bea620
> 2005-08-29 06:35:40.43 spid3
> 2005-08-29 06:35:40.43 spid3 End deadlock search 5358 ... a deadlock
was
> not found.
> 2005-08-29 06:35:40.43 spid3 -
>



Respuesta Responder a este mensaje
#3 Alejandro Mesa
29/08/2005 - 17:21 | Informe spam
2005-08-28 16:19:33.84 spid3 Node:1
2005-08-28 16:19:33.84 spid3 RID: 5:1:302:2

2005-08-28 16:19:33.84 spid3 SPID: 247 ECID: 0 Statement Type: DELETE
Line #: 1
2005-08-28 16:19:33.84 spid3 Input Buf: RPC Event: sp_executesql;1

2005-08-28 16:19:33.84 spid3 Node:2
2005-08-28 16:19:33.84 spid3 KEY: 5:1522104463:2 (6102b12ab08d)

2005-08-28 16:19:33.84 spid3 SPID: 181 ECID: 0 Statement Type: SELECT
Line #: 1
2005-08-28 16:19:33.84 spid3 Input Buf: RPC Event: sp_executesql;1




De la informacion que seleccione sobre el deadlock, podemos ver lo siguiente:

- Nodo 1 estava haciendo un "delete" e hizo un lock a nivel de fila, dato
que se saca de la info:

RID: 5:1:302:2 --> bd:file:page:row

En tu server, ejecuta lo siguiente usando QA:

use master
go

select db_name(5)
go

use ? -- aqui pones la bd de la consulta anterior
go

dbcc page (5,1,302,2)
go

si quieres posteas el resultado del comando dbcc, porque es de ahi de donde
sacaremos en que tabla se estava haciendo el delete. Digo que si quieres,
porque tambien lo podemos sacar de la info del nodo 2, que estava haciendo un
select e hizo un lock a nivel de llave en un indice al parecer de la misma
tabla referenciada por nodo 1.

KEY: 5:1522104463:2 --> db:object_id:indice_id

si usas QA y ejecutas:

use master
go

select db_name(5)
go

use ? -- bd resultado de la consulta anterior
go

select object_name(1522104463)
go

sabremos la tabla u objeto donde se produjo el lock. Como tu sabes, cuando
hacemos un delete en una tabla tambien los indices son actualizados.

Tracing Deadlocks
http://www.sqlservercentral.com/col...dlocks.asp


AMB


"Pao" wrote:

Ahora si me dió uno, pero como lo interpreto me puedes ayudar:
2005-08-28 16:19:33.82 spid3 -
2005-08-28 16:19:33.82 spid3 Starting deadlock search 5299

2005-08-28 16:19:33.82 spid3 Target Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60
2005-08-28 16:19:33.84 spid3 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200
2005-08-28 16:19:33.84 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Deadlock cycle was encountered
verifying cycle
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200 Cost:(0/3C)
2005-08-28 16:19:33.84 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
S SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3

Deadlock encountered Printing deadlock information
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Wait-for graph
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Node:1
2005-08-28 16:19:33.84 spid3 RID: 5:1:302:2 CleanCnt:1
Mode: X Flags: 0x2
2005-08-28 16:19:33.84 spid3 Grant List 0::
2005-08-28 16:19:33.84 spid3 Owner:0x65e952c0 Mode: X Flg:0x0
Ref:1 Life:02000000 SPID:247 ECID:0
2005-08-28 16:19:33.84 spid3 SPID: 247 ECID: 0 Statement Type: DELETE
Line #: 1
2005-08-28 16:19:33.84 spid3 Input Buf: RPC Event: sp_executesql;1
2005-08-28 16:19:33.84 spid3 Requested By:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 Node:2
2005-08-28 16:19:33.84 spid3 KEY: 5:1522104463:2 (6102b12ab08d)
CleanCnt:1 Mode: U Flags: 0x0
2005-08-28 16:19:33.84 spid3 Grant List 0::
2005-08-28 16:19:33.84 spid3 Owner:0x65e94440 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:181 ECID:0
2005-08-28 16:19:33.84 spid3 SPID: 181 ECID: 0 Statement Type: SELECT
Line #: 1
2005-08-28 16:19:33.84 spid3 Input Buf: RPC Event: sp_executesql;1
2005-08-28 16:19:33.84 spid3 Requested By:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: X
SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200 Cost:(0/3C)
2005-08-28 16:19:33.84 spid3 Victim Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:181 ECID:0 Ec:(0x444BF500) Value:0x65e94f60 Cost:(0/0)
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 End deadlock search 5299 ... a deadlock was
found.
2005-08-28 16:19:33.84 spid3 -

2005-08-28 16:19:33.84 spid3 -
2005-08-28 16:19:33.84 spid3 Starting deadlock search 5300

2005-08-28 16:19:33.84 spid3 Target Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:245 ECID:0 Ec:(0x4434D500) Value:0x65e94460
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
S SPID:245 ECID:0 Ec:(0x4434D500) Value:0x65e94460
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 End deadlock search 5300 ... a deadlock was
not found.
2005-08-28 16:19:33.84 spid3 -

2005-08-28 16:19:33.84 spid3 -
2005-08-28 16:19:33.84 spid3 Starting deadlock search 5301

2005-08-28 16:19:33.84 spid3 Target Resource Owner:
2005-08-28 16:19:33.84 spid3 ResType:LockOwner Stype:'OR' Mode: X
SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200
2005-08-28 16:19:33.84 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:247 ECID:0 Ec:(0x444A5500) Value:0x65e1a200
2005-08-28 16:19:33.84 spid3
2005-08-28 16:19:33.84 spid3 End deadlock search 5301 ... a deadlock was
not found.


"Mauro" wrote:

> tenes una bandera activada que logea los deadlooks pero en este caso no
> existe ninguno "a deadlock was
> not found."
> preocupate cuando diga que lo encontro, y en ese caso el log te va a dar mas
> informacion.
>
> "Pao" wrote in message
> news:
> > Necesito que por favor me ayuden a determinar esto que me reporta mi log
> de
> > SQLserver 2000 con sp3 en windows 2000 con sp4:
> >
> > COmo sé cual fue la sentencia o los objetos reportados.
> >
> > 2005-08-29 06:35:40.43 spid3 -
> > 2005-08-29 06:35:40.43 spid3 Starting deadlock search 5358
> >
> > 2005-08-29 06:35:40.43 spid3 Target Resource Owner:
> > 2005-08-29 06:35:40.43 spid3 ResType:ExchangeId Stype:'AND' SPID:260
> > ECID:0 Ec:(0x66ED3500) Value:0x42bea620
> > 2005-08-29 06:35:40.43 spid3 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:260 ECID:0 Ec:(0x66ED3500) Value:0x42bea620
> > 2005-08-29 06:35:40.43 spid3
> > 2005-08-29 06:35:40.43 spid3 End deadlock search 5358 ... a deadlock
> was
> > not found.
> > 2005-08-29 06:35:40.43 spid3 -
> >
>
>
>
Respuesta Responder a este mensaje
#4 Isaias
29/08/2005 - 17:26 | Informe spam
Bueno, yo tengo este store para detectar los Dead_locks, espero sirva:

CREATE PROCEDURE pr_WhoMakeDeadLock
AS

SET NOCOUNT ON

DECLARE @spid int,
@cuantos int

CREATE TABLE #BLOQUEADOS (blocked INT, Totales INT)

INSERT INTO #BLOQUEADOS
SELECT
blocked, count(blocked)
FROM master.dbo.sysprocesses
WHERE blocked <> 0
GROUP BY blocked

IF @@ROWCOUNT = 0
RETURN(0)

SELECT TOP 1 @spid = blocked, @cuantos = Totales
FROM #BLOQUEADOS
ORDER BY 2 DESC

SELECT ISNULL(@spid,0), ISNULL(@cuantos,0)
Respuesta Responder a este mensaje
#5 Pao
29/08/2005 - 18:03 | Informe spam
Ok, muchas gracias a todos por su ayuda.

"Isaias" wrote:

Bueno, yo tengo este store para detectar los Dead_locks, espero sirva:

CREATE PROCEDURE pr_WhoMakeDeadLock
AS

SET NOCOUNT ON

DECLARE @spid int,
@cuantos int

CREATE TABLE #BLOQUEADOS (blocked INT, Totales INT)

INSERT INTO #BLOQUEADOS
SELECT
blocked, count(blocked)
FROM master.dbo.sysprocesses
WHERE blocked <> 0
GROUP BY blocked

IF @@ROWCOUNT = 0
RETURN(0)

SELECT TOP 1 @spid = blocked, @cuantos = Totales
FROM #BLOQUEADOS
ORDER BY 2 DESC

SELECT ISNULL(@spid,0), ISNULL(@cuantos,0)



Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaSiguiente Respuesta Tengo una respuesta
Search Busqueda sugerida