Deadlock (strange update deadlock)

hi,
Need help in identifying deadlock issue. we are almost getting 10-15
deadlock issues in a day and with almost same type of log, lock type.
I supposed that are fragmentation, rebuild the indexes and change fillfactor,
but the problem continues. The table has a lot of updates daily, the table as
4 nonclustered indexes and a clustered PK.
below is the log..
Deadlock encountered ... Printing deadlock information
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Wait-for graph
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Node:1
2006-07-11 19:22:43.57 spid3 PAG: 7:4:38320 CleanCnt:2
Mode: UIX Flags: 0x2
2006-07-11 19:22:43.57 spid3 Grant List 3::
2006-07-11 19:22:43.57 spid3 Owner:0x44239a00 Mode: UIX Flg:0x0
Ref:2 Life:02000000 SPID:89 ECID:0
2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
agenda_exame
SET id_agendamento=1567614,
id_paciente=5046281,
sequencia=2601052,
id_exame='UA02',
duracao_exame=20,
ind_multiplo='S',
ind_modulo='1',
id_usuario_agendador='PAULA',
data_agendamento=GETDATE(),
id_usuario_tran
2006-07-11 19:22:43.57 spid3 Requested By:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Node:2
2006-07-11 19:22:43.57 spid3 PAG: 7:4:36528 CleanCnt:2
Mode: U Flags: 0x2
2006-07-11 19:22:43.57 spid3 Grant List 2::
2006-07-11 19:22:43.57 spid3 Owner:0x472bbf40 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:88 ECID:0
2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
agenda_exame
SET id_agendamento=1567612,
id_paciente=5027055,
sequencia=2601051,
id_exame='AM01',
duracao_exame=10,
ind_multiplo='N',
ind_modulo='0',
id_usuario_agendador='CRISM',
data_agendamento=GETDATE(),
id_usuario_tran
2006-07-11 19:22:43.57 spid3 Requested By:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
89 ECID:0 Ec:(0x4FC254F8) Value:0x503ddd40 Cost:(0/3C8)
2006-07-11 19:22:43.57 spid3 Victim Resource Owner:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
Thanks for any help.renatofts wrote:
> hi,
> Need help in identifying deadlock issue. we are almost getting 10-15
> deadlock issues in a day and with almost same type of log, lock type.
> I supposed that are fragmentation, rebuild the indexes and change fillfactor,
> but the problem continues. The table has a lot of updates daily, the table as
> 4 nonclustered indexes and a clustered PK.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Wait-for graph
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Node:1
> 2006-07-11 19:22:43.57 spid3 PAG: 7:4:38320 CleanCnt:2
> Mode: UIX Flags: 0x2
> 2006-07-11 19:22:43.57 spid3 Grant List 3::
> 2006-07-11 19:22:43.57 spid3 Owner:0x44239a00 Mode: UIX Flg:0x0
> Ref:2 Life:02000000 SPID:89 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
> agenda_exame
> SET id_agendamento=1567614,
> id_paciente=5046281,
> sequencia=2601052,
> id_exame='UA02',
> duracao_exame=20,
> ind_multiplo='S',
> ind_modulo='1',
> id_usuario_agendador='PAULA',
> data_agendamento=GETDATE(),
> id_usuario_tran
> 2006-07-11 19:22:43.57 spid3 Requested By:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
> 88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Node:2
> 2006-07-11 19:22:43.57 spid3 PAG: 7:4:36528 CleanCnt:2
> Mode: U Flags: 0x2
> 2006-07-11 19:22:43.57 spid3 Grant List 2::
> 2006-07-11 19:22:43.57 spid3 Owner:0x472bbf40 Mode: U Flg:0x0
> Ref:0 Life:00000001 SPID:88 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
> agenda_exame
> SET id_agendamento=1567612,
> id_paciente=5027055,
> sequencia=2601051,
> id_exame='AM01',
> duracao_exame=10,
> ind_multiplo='N',
> ind_modulo='0',
> id_usuario_agendador='CRISM',
> data_agendamento=GETDATE(),
> id_usuario_tran
> 2006-07-11 19:22:43.57 spid3 Requested By:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
> 89 ECID:0 Ec:(0x4FC254F8) Value:0x503ddd40 Cost:(0/3C8)
> 2006-07-11 19:22:43.57 spid3 Victim Resource Owner:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
> 88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
> Thanks for any help.
What does the full UPDATE statement look like?
http://www.sql-server-performance.com/deadlocks.asp
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help, the updates look like that:
UPDATE sigam.agenda_exame
SET id_motiv_bloq_sala='$1',
id_usuario_agendador='EDSON',
data_agendamento=GETDATE()
WHERE id_posto=2
AND id_setor='AM'
AND id_sala='MAI1'
AND data='2006-08-11 15:00:00.0'
and
UPDATE sigam.agenda_exame
SET id_agendamento=1565410,
id_paciente=100100,
sequencia=2597022,
id_exame='AM02',
duracao_exame=60,
ind_multiplo='S',
ind_modulo='1',
id_usuario_agendador='RENATO',
data_agendamento=GETDATE(),
id_usuario_transferidor='*',
id_motiv_bloq_sala='01',
conselho_executor='URP1',
codigo_executor='025332SP',
conselho_acompanhante='*',
codigo_acompanhante='0',
conselho_solicitante='*',
codigo_solicitante='0',
tipo_convenio='P',
id_convenio='PAR',
ind_forcado='0',
ind_estouro='N',
id_fase='',
preco='{"0.0"}'
WHERE id_posto=2
AND id_setor='AM'
AND id_sala='MAI1'
AND data='2006-08-11 14:10:00.0'
How do the table get deadlocks on itself ? Can I simulate this ?
Tks
Tracy McKibben wrote:
>> hi,
>> Need help in identifying deadlock issue. we are almost getting 10-15
>[quoted text clipped - 94 lines]
>> Thanks for any help.
>What does the full UPDATE statement look like?
>http://www.sql-server-performance.com/deadlocks.asp
>http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||renatofts via SQLMonster.com wrote:
> Hi Tracy, thanks for help, the updates look like that:
> UPDATE sigam.agenda_exame
> SET id_motiv_bloq_sala='$1',
> id_usuario_agendador='EDSON',
> data_agendamento=GETDATE()
> WHERE id_posto=2
> AND id_setor='AM'
> AND id_sala='MAI1'
> AND data='2006-08-11 15:00:00.0'
> and
> UPDATE sigam.agenda_exame
> SET id_agendamento=1565410,
> id_paciente=100100,
> sequencia=2597022,
> id_exame='AM02',
> duracao_exame=60,
> ind_multiplo='S',
> ind_modulo='1',
> id_usuario_agendador='RENATO',
> data_agendamento=GETDATE(),
> id_usuario_transferidor='*',
> id_motiv_bloq_sala='01',
> conselho_executor='URP1',
> codigo_executor='025332SP',
> conselho_acompanhante='*',
> codigo_acompanhante='0',
> conselho_solicitante='*',
> codigo_solicitante='0',
> tipo_convenio='P',
> id_convenio='PAR',
> ind_forcado='0',
> ind_estouro='N',
> id_fase='',
> preco='{"0.0"}'
> WHERE id_posto=2
> AND id_setor='AM'
> AND id_sala='MAI1'
> AND data='2006-08-11 14:10:00.0'
> How do the table get deadlocks on itself ? Can I simulate this ?
> Tks
>
Are there update triggers on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, but there are 4 indexes that references to id_motiv_bloq_sala, sequencia,
id_agendamento columns, I dropped two indexes and deadlocks decreases.
Tracy McKibben wrote:
>> Hi Tracy, thanks for help, the updates look like that:
>[quoted text clipped - 41 lines]
>> Tks
>Are there update triggers on this table?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||renatofts via SQLMonster.com wrote:
> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequencia,
> id_agendamento columns, I dropped two indexes and deadlocks decreases.
>
Hmmm... You could be dealing with index fragmentation, or even disk
fragmentation, or just poor disk I/O overall, causing the index updates
to take longer than necessary.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Okay, but how do i get performance without index fragmentation, cause these
columns are important to perfmorm my queries. Thanks a lot.
Tracy McKibben wrote:
>> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequencia,
>> id_agendamento columns, I dropped two indexes and deadlocks decreases.
>Hmmm... You could be dealing with index fragmentation, or even disk
>fragmentation, or just poor disk I/O overall, causing the index updates
>to take longer than necessary.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||renatofts via SQLMonster.com wrote:
> Okay, but how do i get performance without index fragmentation, cause these
> columns are important to perfmorm my queries. Thanks a lot.
>
What does the execution plan look like for the two sample updates that
you posted? Also, post the output of sp_helpindex from this table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com