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
Showing posts with label identifying. Show all posts
Showing posts with label identifying. Show all posts
Sunday, March 11, 2012
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 a
s
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 SPI
D:
88 ECID:0 Ec
0x4FB514F8) Value:0x503dc5e0 Cost0/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 SPI
D:
89 ECID:0 Ec0x4FC254F8) Value:0x503ddd40 Cost0/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 Ec0x4FB514F8) Value:0x503dc5e0 Cost0/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 fillfact
or,
> 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:0x
0
> Ref:2 Life:02000000 SPID:89 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDAT
E
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE siga
m.
> 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 S
PID:
> 88 ECID:0 Ec0x4FB514F8) Value:0x503dc5e0 Cost0/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:0x
0
> Ref:0 Life:00000001 SPID:88 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDAT
E
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE siga
m.
> 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 S
PID:
> 89 ECID:0 Ec0x4FC254F8) Value:0x503ddd40 Cost0/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 SPI
D:
> 88 ECID:0 Ec0x4FB514F8) Value:0x503dc5e0 Cost0/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/vie...latingADeadlock
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:
>[quoted text clipped - 94 lines]
>What does the full UPDATE statement look like?
>http://www.sql-server-performance.com/deadlocks.asp
>http://realsqlguy.com/twiki/bin/vie...latingADeadlock
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.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, sequenci
a,
id_agendamento columns, I dropped two indexes and deadlocks decreases.
Tracy McKibben wrote:
>[quoted text clipped - 41 lines]
>Are there update triggers on this table?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequen
cia,
> 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:
>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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> Okay, but how do i get performance without index fragmentation, cause thes
e
> 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
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 a
s
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 SPI
D:
88 ECID:0 Ec
0x4FB514F8) Value:0x503dc5e0 Cost0/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 SPI
D:
89 ECID:0 Ec
0x4FC254F8) Value:0x503ddd40 Cost0/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 Cost0/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 fillfact
or,
> 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:0x
0
> Ref:2 Life:02000000 SPID:89 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDAT
E
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE siga
m.
> 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 S
PID:
> 88 ECID:0 Ec
0x4FB514F8) Value:0x503dc5e0 Cost0/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:0x
0
> Ref:0 Life:00000001 SPID:88 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDAT
E
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE siga
m.
> 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 S
PID:
> 89 ECID:0 Ec
0x4FC254F8) Value:0x503ddd40 Cost0/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 SPI
D:
> 88 ECID:0 Ec
0x4FB514F8) Value:0x503dc5e0 Cost0/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/vie...latingADeadlock
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:
>[quoted text clipped - 94 lines]
>What does the full UPDATE statement look like?
>http://www.sql-server-performance.com/deadlocks.asp
>http://realsqlguy.com/twiki/bin/vie...latingADeadlock
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.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, sequenci
a,
id_agendamento columns, I dropped two indexes and deadlocks decreases.
Tracy McKibben wrote:
>[quoted text clipped - 41 lines]
>Are there update triggers on this table?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequen
cia,
> 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:
>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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> Okay, but how do i get performance without index fragmentation, cause thes
e
> 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
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec0x57DD9550) Value:0x6795cea0 Cost0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
Ravi
Could you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec0x57DD9550) Value:0x6795cea0 Cost0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
Ravi
|||==========Part of code of update operation================
SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =
es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =
ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> 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.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec0x57DD9550) Value:0x6795cea0 Cost0/3C9948)
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditions
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we are
> getting deadlocks.
> --
> Ravi
>
>
|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
Sanjay
"Sanjay" wrote:
[vbcol=seagreen]
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =
edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =
il.InitiativeID[vbcol=seagreen]
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
that[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
SELECT[vbcol=seagreen]
IX[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
UPDATE[vbcol=seagreen]
select[vbcol=seagreen]
conditions[vbcol=seagreen]
2[vbcol=seagreen]
are[vbcol=seagreen]
|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> edl.LanguageCode
> il.InitiativeID
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
> select
> conditions
> 2
> are
>
>
|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it
gets[vbcol=seagreen]
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
EGT.accessID[vbcol=seagreen]
ed.accessID[vbcol=seagreen]
l.LanguageCode[vbcol=seagreen]
> edl.LanguageCode
el.LocationID[vbcol=seagreen]
ec.CountryCode[vbcol=seagreen]
tzl.TimeZoneID[vbcol=seagreen]
st.CountryCode[vbcol=seagreen]
cl.CampaignID[vbcol=seagreen]
> il.InitiativeID
s.SubsidiaryID[vbcol=seagreen]
120)[vbcol=seagreen]
120)[vbcol=seagreen]
[vbcol=seagreen]
after[vbcol=seagreen]
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
S[vbcol=seagreen]
> select
> conditions
by[vbcol=seagreen]
> 2
we
> are
>
>
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec
0x57DD9550) Value:0x6795cea0 Cost0/3C9948)2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
Ravi
Could you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec
0x57DD9550) Value:0x6795cea0 Cost0/3C9948)2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
Ravi
|||==========Part of code of update operation================
SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =
es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =
ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> 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.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec
0x57DD9550) Value:0x6795cea0 Cost0/3C9948)> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditions
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we are
> getting deadlocks.
> --
> Ravi
>
>
|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
Sanjay
"Sanjay" wrote:
[vbcol=seagreen]
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =
edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =
il.InitiativeID[vbcol=seagreen]
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
that[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
SELECT[vbcol=seagreen]
IX[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
UPDATE[vbcol=seagreen]
select[vbcol=seagreen]
conditions[vbcol=seagreen]
2[vbcol=seagreen]
are[vbcol=seagreen]
|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> edl.LanguageCode
> il.InitiativeID
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
> select
> conditions
> 2
> are
>
>
|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it
gets[vbcol=seagreen]
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
EGT.accessID[vbcol=seagreen]
ed.accessID[vbcol=seagreen]
l.LanguageCode[vbcol=seagreen]
> edl.LanguageCode
el.LocationID[vbcol=seagreen]
ec.CountryCode[vbcol=seagreen]
tzl.TimeZoneID[vbcol=seagreen]
st.CountryCode[vbcol=seagreen]
cl.CampaignID[vbcol=seagreen]
> il.InitiativeID
s.SubsidiaryID[vbcol=seagreen]
120)[vbcol=seagreen]
120)[vbcol=seagreen]
[vbcol=seagreen]
after[vbcol=seagreen]
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
S[vbcol=seagreen]
> select
> conditions
by[vbcol=seagreen]
> 2
we
> are
>
>
Thursday, March 8, 2012
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec0x57DD9550) Value:0x6795cea0 Cost0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQ
L
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
RaviCould you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec0x57DD9550) Value:0x6795cea0 Cost0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
Ravi|||==========Part of code of update operation================
SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =
es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =
ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> 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.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x
0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELEC
T
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec0x57DD9550) Value:0x6795cea0 Cost0/3C9948)
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x
0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDAT
E
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec0x07B0D540) Value:0x658c3100 Cost0/0)
> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditio
ns
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we a
re
> getting deadlocks.
> --
> Ravi
>
>|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
[vbcol=seagreen]
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCo
de
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeI
D
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
>|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =
edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =
il.InitiativeID[vbcol=seagreen]
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
>
that[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
SELECT[vbcol=seagreen]
IX[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
UPDATE[vbcol=seagreen]
select[vbcol=seagreen]
conditions[vbcol=seagreen]
2[vbcol=seagreen]
are[vbcol=seagreen]|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it ge
ts
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
>
> edl.LanguageCode
> il.InitiativeID
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
> select
> conditions
> 2
> are
>
>|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it
gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
>
EGT.accessID[vbcol=seagreen]
ed.accessID[vbcol=seagreen]
l.LanguageCode[vbcol=seagreen]
> edl.LanguageCode
el.LocationID[vbcol=seagreen]
ec.CountryCode[vbcol=seagreen]
tzl.TimeZoneID[vbcol=seagreen]
st.CountryCode[vbcol=seagreen]
cl.CampaignID[vbcol=seagreen]
> il.InitiativeID
s.SubsidiaryID[vbcol=seagreen]
120)[vbcol=seagreen]
120)[vbcol=seagreen]
[vbcol=seagreen]
after[vbcol=seagreen]
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
S[vbcol=seagreen]
> select
> conditions
by[vbcol=seagreen]
> 2
we[vbcol=seagreen]
> are
>
>
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec
0x57DD9550) Value:0x6795cea0 Cost0/3C9948)2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQ
L
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
RaviCould you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec
0x57DD9550) Value:0x6795cea0 Cost0/3C9948)2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
Ravi|||==========Part of code of update operation================
SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =
es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =
ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> 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.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x
0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELEC
T
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec
0x57DD9550) Value:0x6795cea0 Cost0/3C9948)> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x
0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDAT
E
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec
0x07B0D540) Value:0x658c3100 Cost0/0)> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditio
ns
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we a
re
> getting deadlocks.
> --
> Ravi
>
>|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
[vbcol=seagreen]
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCo
de
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeI
D
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
>|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====
second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================
> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =
> ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID =
> es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =
edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =
> dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode =
> ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =
> est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =
> etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =
> estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =
il.InitiativeID[vbcol=seagreen]
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =
> ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========
> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
>
that[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
SELECT[vbcol=seagreen]
IX[vbcol=seagreen]
CleanCnt:1[vbcol=seagreen]
Flg:0x0[vbcol=seagreen]
UPDATE[vbcol=seagreen]
select[vbcol=seagreen]
conditions[vbcol=seagreen]
2[vbcol=seagreen]
are[vbcol=seagreen]|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it ge
ts
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
>
> edl.LanguageCode
> il.InitiativeID
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
> select
> conditions
> 2
> are
>
>|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====
> second SP in the meantime trying to select and place shared lock but it
gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
>
EGT.accessID[vbcol=seagreen]
ed.accessID[vbcol=seagreen]
l.LanguageCode[vbcol=seagreen]
> edl.LanguageCode
el.LocationID[vbcol=seagreen]
ec.CountryCode[vbcol=seagreen]
tzl.TimeZoneID[vbcol=seagreen]
st.CountryCode[vbcol=seagreen]
cl.CampaignID[vbcol=seagreen]
> il.InitiativeID
s.SubsidiaryID[vbcol=seagreen]
120)[vbcol=seagreen]
120)[vbcol=seagreen]
[vbcol=seagreen]
after[vbcol=seagreen]
> that
> CleanCnt:1
> Flg:0x0
> SELECT
> IX
> CleanCnt:1
> Flg:0x0
> UPDATE
S[vbcol=seagreen]
> select
> conditions
by[vbcol=seagreen]
> 2
we[vbcol=seagreen]
> are
>
>
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
--
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
--
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
--
RaviCould you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
--
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
--
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
--
Ravi|||==========Part of code of update operation================SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> 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.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditions
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we are
> getting deadlocks.
> --
> Ravi
>
>|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID => es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode => ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
> > Could you post the complete text of the procedures involved?
> >
> > Generally, if you are selecting data from a table, and then updating that
> > data later in the transaction, you are likely to have deadlocking
> > situations. In this case, selecting with UPDLOCK hint helps.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > 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.
> >
> > what i am really interested in knowing is
> > 1) is it cycle deadlock or conversion deadlock.
> > 2) is it really good to use NOLOCk hint in production env.
> > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > 4) IS page lock really good (this is happening now) .
> > 5) Probable reasons why deadlock is coming ?
> > 6) All the columns are indexed in tables.
> >
> > below is the log..
> > Deadlock encountered ... Printing deadlock information
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:1
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> > Mode: SIU Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> > Ref:1 Life:00000000 SPID:77 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> > Line #: 36
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:2
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> > Mode: IX Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> > Ref:2 Life:02000000 SPID:66 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> > Line #: 320
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > Events.dbo.UpdateDb;1
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> >
> > --
> > this is line which caused deadlock..
> >
> > UPDATE Line #: 320
> > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.access
> > FROM #Temp1 ae
> > )
> >
> > --
> > second part of select st which is victim SP. This procedure has all select
> > statements only.
> >
> > SELECT a.*
> > FROM dbo.access ec
> > WHERE ec.accessID = 1 -->(1=Active)
> > AND ec.access = 23
> >
> >
> > Please note : we are getting almost 10-15 deadlocks with similiar conditions
> > and same page lock with same set of sp's. Actually both sp's are run by 2
> > SQL
> > jobs which run after every 10 mins and i think this is the reason why we are
> > getting deadlocks.
> >
> > --
> > Ravi
> >
> >
> >|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID => es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode => ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
> > Could you post the complete text of the procedures involved?
> >
> > Generally, if you are selecting data from a table, and then updating
that
> > data later in the transaction, you are likely to have deadlocking
> > situations. In this case, selecting with UPDLOCK hint helps.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > 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.
> >
> > what i am really interested in knowing is
> > 1) is it cycle deadlock or conversion deadlock.
> > 2) is it really good to use NOLOCk hint in production env.
> > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > 4) IS page lock really good (this is happening now) .
> > 5) Probable reasons why deadlock is coming ?
> > 6) All the columns are indexed in tables.
> >
> > below is the log..
> > Deadlock encountered ... Printing deadlock information
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:1
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
CleanCnt:1
> > Mode: SIU Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
Flg:0x0
> > Ref:1 Life:00000000 SPID:77 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
SELECT
> > Line #: 36
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
IX
> > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:2
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
CleanCnt:1
> > Mode: IX Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
Flg:0x0
> > Ref:2 Life:02000000 SPID:66 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
UPDATE
> > Line #: 320
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > Events.dbo.UpdateDb;1
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> >
> > --
> > this is line which caused deadlock..
> >
> > UPDATE Line #: 320
> > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.access
> > FROM #Temp1 ae
> > )
> >
> > --
> > second part of select st which is victim SP. This procedure has all
select
> > statements only.
> >
> > SELECT a.*
> > FROM dbo.access ec
> > WHERE ec.accessID = 1 -->(1=Active)
> > AND ec.access = 23
> >
> >
> > Please note : we are getting almost 10-15 deadlocks with similiar
conditions
> > and same page lock with same set of sp's. Actually both sp's are run by
2
> > SQL
> > jobs which run after every 10 mins and i think this is the reason why we
are
> > getting deadlocks.
> >
> > --
> > Ravi
> >
> >
> >|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====> second SP in the meantime trying to select and place shared lock but it gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> > ==========Part of code of update operation================> > SELECT *
> > INTO #Activeaccesss
> > FROM dbo.access e (NOLOCK)
> > INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> > INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => > ecat.accessCategoryID
> > INNER JOIN dbo.accessStatus es ON e.accessStatusID => > es.accessStatusID
> > INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> > -- AND ed.LanguageCode = ed.LanguageCode
> > INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> >
> > INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode => edl.LanguageCode
> > AND l.LanguageID = edl.LanguageID
> > INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> >
> > LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> > AND ec.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> > AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => > dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> > LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> > And St.StateID
> > IN (Select StateID From StateLoc STL Where STL.LanguageCode => > ed.LanguageCode and STL.Description = el.StateProvince)
> > LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => > est.accessSubTypeID
> > LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => > etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => > estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> > LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> > AND cl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID => il.InitiativeID
> > AND il.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> > LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> >
> > LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> > LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> > WHERE e.IsWebPublishable = @.True
> > AND e.IsReadyForWebPublish = @.True
> > AND e.accessStatusID = 1
> > AND
> > (
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID = 1
> > AND e.accessEndDate >= GetDate()
> > AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> > AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> > )
> > OR
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID in (2, 4)
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > OR
> > (
> > e.accessCategoryID = 3
> > AND EGT.ArchivedStartDate <= @.CurDate
> > AND EGT.ArchivedEndDate >= @.CurDate
> > )
> > OR
> > (
> > e.accessCategoryID = 5
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > )
> >
> > SELECT @.RowCount = @.@.ROWCOUNT
> > -- some other code here
> >
> > UPDATE access
> > SET access.IsPublished = 0
> > -- , access.IsWebPublishable = 0 --geohey 2/14/03
> > WHERE accessID IN
> > (
> > SELECT es.accessID
> > FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => > ae.accessID
> > WHERE ae.accessID IS NULL
> > )
> > -- some other code here
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.accessID
> > FROM #Activeaccesss ae
> > )
> > ================================ENDS here===========> >
> > Another SP is SELECT STATEMENT both of these are in jobs and runs after
> > every 10 mins. Infact i also noticed that deadlocks are not coming after
> > every 5-10 mins but there is certain interval of 1 or 2 hours.
> >
> > Thanks,
> >
> >
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Could you post the complete text of the procedures involved?
> > >
> > > Generally, if you are selecting data from a table, and then updating
> that
> > > data later in the transaction, you are likely to have deadlocking
> > > situations. In this case, selecting with UPDLOCK hint helps.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > > 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.
> > >
> > > what i am really interested in knowing is
> > > 1) is it cycle deadlock or conversion deadlock.
> > > 2) is it really good to use NOLOCk hint in production env.
> > > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > > 4) IS page lock really good (this is happening now) .
> > > 5) Probable reasons why deadlock is coming ?
> > > 6) All the columns are indexed in tables.
> > >
> > > below is the log..
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:1
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
> CleanCnt:1
> > > Mode: SIU Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
> Flg:0x0
> > > Ref:1 Life:00000000 SPID:77 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
> SELECT
> > > Line #: 36
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
> IX
> > > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:2
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
> CleanCnt:1
> > > Mode: IX Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
> Flg:0x0
> > > Ref:2 Life:02000000 SPID:66 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
> UPDATE
> > > Line #: 320
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > > Events.dbo.UpdateDb;1
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > >
> > > --
> > > this is line which caused deadlock..
> > >
> > > UPDATE Line #: 320
> > > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> > >
> > > UPDATE access
> > > SET IsPublished = 1
> > > WHERE accessID IN
> > > (
> > > SELECT ae.access
> > > FROM #Temp1 ae
> > > )
> > >
> > > --
> > > second part of select st which is victim SP. This procedure has all
> select
> > > statements only.
> > >
> > > SELECT a.*
> > > FROM dbo.access ec
> > > WHERE ec.accessID = 1 -->(1=Active)
> > > AND ec.access = 23
> > >
> > >
> > > Please note : we are getting almost 10-15 deadlocks with similiar
> conditions
> > > and same page lock with same set of sp's. Actually both sp's are run by
> 2
> > > SQL
> > > jobs which run after every 10 mins and i think this is the reason why we
> are
> > > getting deadlocks.
> > >
> > > --
> > > Ravi
> > >
> > >
> > >
>
>|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====> second SP in the meantime trying to select and place shared lock but it
gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> > ==========Part of code of update operation================> > SELECT *
> > INTO #Activeaccesss
> > FROM dbo.access e (NOLOCK)
> > INNER JOIN dbo.accessGMTTime EGT On e.accessID =EGT.accessID
> > INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => > ecat.accessCategoryID
> > INNER JOIN dbo.accessStatus es ON e.accessStatusID => > es.accessStatusID
> > INNER JOIN dbo.accessDetail ed ON e.accessID =ed.accessID
> > -- AND ed.LanguageCode = ed.LanguageCode
> > INNER JOIN dbo.Language l ON ed.LanguageCode =l.LanguageCode
> >
> > INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode => edl.LanguageCode
> > AND l.LanguageID = edl.LanguageID
> > INNER JOIN dbo.accessLocation el ON e.LocationID =el.LocationID
> >
> > LEFT JOIN dbo.CountryLoc ec ON el.CountryCode =ec.CountryCode
> > AND ec.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID =tzl.TimeZoneID
> > AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => > dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> > LEFT JOIN dbo.State st ON e.CountryCode =st.CountryCode
> > And St.StateID
> > IN (Select StateID From StateLoc STL Where STL.LanguageCode => > ed.LanguageCode and STL.Description = el.StateProvince)
> > LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => > est.accessSubTypeID
> > LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => > etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => > estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> > LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID =cl.CampaignID
> > AND cl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID => il.InitiativeID
> > AND il.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> > LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID =s.SubsidiaryID
> >
> > LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> > LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> > WHERE e.IsWebPublishable = @.True
> > AND e.IsReadyForWebPublish = @.True
> > AND e.accessStatusID = 1
> > AND
> > (
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID = 1
> > AND e.accessEndDate >= GetDate()
> > AND e.WebPublishStartDate <= convert(varchar(10), Getdate(),
120)
> > AND e.WebPublishEndDate >= convert(varchar(10), Getdate(),
120)
> > )
> > OR
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID in (2, 4)
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > OR
> > (
> > e.accessCategoryID = 3
> > AND EGT.ArchivedStartDate <= @.CurDate
> > AND EGT.ArchivedEndDate >= @.CurDate
> > )
> > OR
> > (
> > e.accessCategoryID = 5
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > )
> >
> > SELECT @.RowCount = @.@.ROWCOUNT
> > -- some other code here
> >
> > UPDATE access
> > SET access.IsPublished = 0
> > -- , access.IsWebPublishable = 0 --geohey 2/14/03
> > WHERE accessID IN
> > (
> > SELECT es.accessID
> > FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => > ae.accessID
> > WHERE ae.accessID IS NULL
> > )
> > -- some other code here
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.accessID
> > FROM #Activeaccesss ae
> > )
> > ================================ENDS here===========> >
> > Another SP is SELECT STATEMENT both of these are in jobs and runs after
> > every 10 mins. Infact i also noticed that deadlocks are not coming
after
> > every 5-10 mins but there is certain interval of 1 or 2 hours.
> >
> > Thanks,
> >
> >
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Could you post the complete text of the procedures involved?
> > >
> > > Generally, if you are selecting data from a table, and then updating
> that
> > > data later in the transaction, you are likely to have deadlocking
> > > situations. In this case, selecting with UPDLOCK hint helps.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > > 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.
> > >
> > > what i am really interested in knowing is
> > > 1) is it cycle deadlock or conversion deadlock.
> > > 2) is it really good to use NOLOCk hint in production env.
> > > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > > 4) IS page lock really good (this is happening now) .
> > > 5) Probable reasons why deadlock is coming ?
> > > 6) All the columns are indexed in tables.
> > >
> > > below is the log..
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:1
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
> CleanCnt:1
> > > Mode: SIU Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
> Flg:0x0
> > > Ref:1 Life:00000000 SPID:77 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
> SELECT
> > > Line #: 36
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
> IX
> > > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:2
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
> CleanCnt:1
> > > Mode: IX Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
> Flg:0x0
> > > Ref:2 Life:02000000 SPID:66 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
> UPDATE
> > > Line #: 320
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > > Events.dbo.UpdateDb;1
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > >
> > > --
> > > this is line which caused deadlock..
> > >
> > > UPDATE Line #: 320
> > > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> > >
> > > UPDATE access
> > > SET IsPublished = 1
> > > WHERE accessID IN
> > > (
> > > SELECT ae.access
> > > FROM #Temp1 ae
> > > )
> > >
> > > --
> > > second part of select st which is victim SP. This procedure has all
> select
> > > statements only.
> > >
> > > SELECT a.*
> > > FROM dbo.access ec
> > > WHERE ec.accessID = 1 -->(1=Active)
> > > AND ec.access = 23
> > >
> > >
> > > Please note : we are getting almost 10-15 deadlocks with similiar
> conditions
> > > and same page lock with same set of sp's. Actually both sp's are run
by
> 2
> > > SQL
> > > jobs which run after every 10 mins and i think this is the reason why
we
> are
> > > getting deadlocks.
> > >
> > > --
> > > Ravi
> > >
> > >
> > >
>
>
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
--
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
--
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2 SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
--
RaviCould you post the complete text of the procedures involved?
Generally, if you are selecting data from a table, and then updating that
data later in the transaction, you are likely to have deadlocking
situations. In this case, selecting with UPDLOCK hint helps.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
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.
what i am really interested in knowing is
1) is it cycle deadlock or conversion deadlock.
2) is it really good to use NOLOCk hint in production env.
3) total db size is around 50 GB with 250 tables and hunderd of SP's.
4) IS page lock really good (this is happening now) .
5) Probable reasons why deadlock is coming ?
6) All the columns are indexed in tables.
below is the log..
Deadlock encountered ... Printing deadlock information
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Wait-for graph
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:1
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
Mode: SIU Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 0::
2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:77 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
Line #: 36
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
2005-07-13 23:11:15.90 spid3
2005-07-13 23:11:15.90 spid3 Node:2
2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
Mode: IX Flags: 0x2
2005-07-13 23:11:15.90 spid3 Grant List 1::
2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:66 ECID:0
2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 320
2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
Events.dbo.UpdateDb;1
2005-07-13 23:11:15.90 spid3 Requested By:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
--
this is line which caused deadlock..
UPDATE Line #: 320
Input Buf: RPC Event: Events.dbo.UpdateDb;1
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.access
FROM #Temp1 ae
)
--
second part of select st which is victim SP. This procedure has all select
statements only.
SELECT a.*
FROM dbo.access ec
WHERE ec.accessID = 1 -->(1=Active)
AND ec.access = 23
Please note : we are getting almost 10-15 deadlocks with similiar conditions
and same page lock with same set of sp's. Actually both sp's are run by 2
SQL
jobs which run after every 10 mins and i think this is the reason why we are
getting deadlocks.
--
Ravi|||==========Part of code of update operation================SELECT *
INTO #Activeaccesss
FROM dbo.access e (NOLOCK)
INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID =ecat.accessCategoryID
INNER JOIN dbo.accessStatus es ON e.accessStatusID =es.accessStatusID
INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
-- AND ed.LanguageCode = ed.LanguageCode
INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
AND l.LanguageID = edl.LanguageID
INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
AND ec.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE =dbo.GetEMSCountryCode(TZL.ISOCountryCode)
LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
And St.StateID
IN (Select StateID From StateLoc STL Where STL.LanguageCode =ed.LanguageCode and STL.Description = el.StateProvince)
LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID =est.accessSubTypeID
LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID =etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID =estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
AND cl.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
AND il.LanguageCode = ed.LanguageCode
LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
WHERE e.IsWebPublishable = @.True
AND e.IsReadyForWebPublish = @.True
AND e.accessStatusID = 1
AND
(
(
-- Included publishdate condition based on category check
e.accessCategoryID = 1
AND e.accessEndDate >= GetDate()
AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
)
OR
(
-- Included publishdate condition based on category check
e.accessCategoryID in (2, 4)
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
OR
(
e.accessCategoryID = 3
AND EGT.ArchivedStartDate <= @.CurDate
AND EGT.ArchivedEndDate >= @.CurDate
)
OR
(
e.accessCategoryID = 5
AND EGT.accessEndDate >= @.CurDateTime
AND EGT.WebPublishStartDate <= @.CurDateTime
AND EGT.WebPublishEndDate >= @.CurDateTime
)
)
SELECT @.RowCount = @.@.ROWCOUNT
-- some other code here
UPDATE access
SET access.IsPublished = 0
-- , access.IsWebPublishable = 0 --geohey 2/14/03
WHERE accessID IN
(
SELECT es.accessID
FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID =ae.accessID
WHERE ae.accessID IS NULL
)
-- some other code here
UPDATE access
SET IsPublished = 1
WHERE accessID IN
(
SELECT ae.accessID
FROM #Activeaccesss ae
)
================================ENDS here===========
Another SP is SELECT STATEMENT both of these are in jobs and runs after
every 10 mins. Infact i also noticed that deadlocks are not coming after
every 5-10 mins but there is certain interval of 1 or 2 hours.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> Could you post the complete text of the procedures involved?
> Generally, if you are selecting data from a table, and then updating that
> data later in the transaction, you are likely to have deadlocking
> situations. In this case, selecting with UPDLOCK hint helps.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> 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.
> what i am really interested in knowing is
> 1) is it cycle deadlock or conversion deadlock.
> 2) is it really good to use NOLOCk hint in production env.
> 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> 4) IS page lock really good (this is happening now) .
> 5) Probable reasons why deadlock is coming ?
> 6) All the columns are indexed in tables.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Wait-for graph
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:1
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> Mode: SIU Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 0::
> 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:77 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> Line #: 36
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> 2005-07-13 23:11:15.90 spid3
> 2005-07-13 23:11:15.90 spid3 Node:2
> 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> Mode: IX Flags: 0x2
> 2005-07-13 23:11:15.90 spid3 Grant List 1::
> 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> Ref:2 Life:02000000 SPID:66 ECID:0
> 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> Line #: 320
> 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> Events.dbo.UpdateDb;1
> 2005-07-13 23:11:15.90 spid3 Requested By:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> --
> this is line which caused deadlock..
> UPDATE Line #: 320
> Input Buf: RPC Event: Events.dbo.UpdateDb;1
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.access
> FROM #Temp1 ae
> )
> --
> second part of select st which is victim SP. This procedure has all select
> statements only.
> SELECT a.*
> FROM dbo.access ec
> WHERE ec.accessID = 1 -->(1=Active)
> AND ec.access = 23
>
> Please note : we are getting almost 10-15 deadlocks with similiar conditions
> and same page lock with same set of sp's. Actually both sp's are run by 2
> SQL
> jobs which run after every 10 mins and i think this is the reason why we are
> getting deadlocks.
> --
> Ravi
>
>|||To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID => es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode = edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode => ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID = il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
> > Could you post the complete text of the procedures involved?
> >
> > Generally, if you are selecting data from a table, and then updating that
> > data later in the transaction, you are likely to have deadlocking
> > situations. In this case, selecting with UPDLOCK hint helps.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > 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.
> >
> > what i am really interested in knowing is
> > 1) is it cycle deadlock or conversion deadlock.
> > 2) is it really good to use NOLOCk hint in production env.
> > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > 4) IS page lock really good (this is happening now) .
> > 5) Probable reasons why deadlock is coming ?
> > 6) All the columns are indexed in tables.
> >
> > below is the log..
> > Deadlock encountered ... Printing deadlock information
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:1
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456 CleanCnt:1
> > Mode: SIU Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S Flg:0x0
> > Ref:1 Life:00000000 SPID:77 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type: SELECT
> > Line #: 36
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:2
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457 CleanCnt:1
> > Mode: IX Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX Flg:0x0
> > Ref:2 Life:02000000 SPID:66 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type: UPDATE
> > Line #: 320
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > Events.dbo.UpdateDb;1
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> >
> > --
> > this is line which caused deadlock..
> >
> > UPDATE Line #: 320
> > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.access
> > FROM #Temp1 ae
> > )
> >
> > --
> > second part of select st which is victim SP. This procedure has all select
> > statements only.
> >
> > SELECT a.*
> > FROM dbo.access ec
> > WHERE ec.accessID = 1 -->(1=Active)
> > AND ec.access = 23
> >
> >
> > Please note : we are getting almost 10-15 deadlocks with similiar conditions
> > and same page lock with same set of sp's. Actually both sp's are run by 2
> > SQL
> > jobs which run after every 10 mins and i think this is the reason why we are
> > getting deadlocks.
> >
> > --
> > Ravi
> >
> >
> >|||Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
issue.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
To summarise
first
1) i am doing select into temporary tables depending on some conditions.
[should i used UPD Lock hint here instead of NOLOCk].
2) Update access table and making one column as 0
3) Update same access table and making column as 1
=====second SP in the meantime trying to select and place shared lock but it gets
stucked up as there is already a X lock.
any suggestion are more than welcome
--
Sanjay
"Sanjay" wrote:
> ==========Part of code of update operation================> SELECT *
> INTO #Activeaccesss
> FROM dbo.access e (NOLOCK)
> INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => ecat.accessCategoryID
> INNER JOIN dbo.accessStatus es ON e.accessStatusID => es.accessStatusID
> INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> -- AND ed.LanguageCode = ed.LanguageCode
> INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode =edl.LanguageCode
> AND l.LanguageID = edl.LanguageID
> INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> AND ec.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> And St.StateID
> IN (Select StateID From StateLoc STL Where STL.LanguageCode => ed.LanguageCode and STL.Description = el.StateProvince)
> LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => est.accessSubTypeID
> LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> AND cl.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID =il.InitiativeID
> AND il.LanguageCode = ed.LanguageCode
> LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> WHERE e.IsWebPublishable = @.True
> AND e.IsReadyForWebPublish = @.True
> AND e.accessStatusID = 1
> AND
> (
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID = 1
> AND e.accessEndDate >= GetDate()
> AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> )
> OR
> (
> -- Included publishdate condition based on category check
> e.accessCategoryID in (2, 4)
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> OR
> (
> e.accessCategoryID = 3
> AND EGT.ArchivedStartDate <= @.CurDate
> AND EGT.ArchivedEndDate >= @.CurDate
> )
> OR
> (
> e.accessCategoryID = 5
> AND EGT.accessEndDate >= @.CurDateTime
> AND EGT.WebPublishStartDate <= @.CurDateTime
> AND EGT.WebPublishEndDate >= @.CurDateTime
> )
> )
> SELECT @.RowCount = @.@.ROWCOUNT
> -- some other code here
> UPDATE access
> SET access.IsPublished = 0
> -- , access.IsWebPublishable = 0 --geohey 2/14/03
> WHERE accessID IN
> (
> SELECT es.accessID
> FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => ae.accessID
> WHERE ae.accessID IS NULL
> )
> -- some other code here
> UPDATE access
> SET IsPublished = 1
> WHERE accessID IN
> (
> SELECT ae.accessID
> FROM #Activeaccesss ae
> )
> ================================ENDS here===========> Another SP is SELECT STATEMENT both of these are in jobs and runs after
> every 10 mins. Infact i also noticed that deadlocks are not coming after
> every 5-10 mins but there is certain interval of 1 or 2 hours.
> Thanks,
>
> "Narayana Vyas Kondreddi" wrote:
> > Could you post the complete text of the procedures involved?
> >
> > Generally, if you are selecting data from a table, and then updating
that
> > data later in the transaction, you are likely to have deadlocking
> > situations. In this case, selecting with UPDLOCK hint helps.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > 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.
> >
> > what i am really interested in knowing is
> > 1) is it cycle deadlock or conversion deadlock.
> > 2) is it really good to use NOLOCk hint in production env.
> > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > 4) IS page lock really good (this is happening now) .
> > 5) Probable reasons why deadlock is coming ?
> > 6) All the columns are indexed in tables.
> >
> > below is the log..
> > Deadlock encountered ... Printing deadlock information
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:1
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
CleanCnt:1
> > Mode: SIU Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
Flg:0x0
> > Ref:1 Life:00000000 SPID:77 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
SELECT
> > Line #: 36
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
IX
> > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > 2005-07-13 23:11:15.90 spid3
> > 2005-07-13 23:11:15.90 spid3 Node:2
> > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
CleanCnt:1
> > Mode: IX Flags: 0x2
> > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
Flg:0x0
> > Ref:2 Life:02000000 SPID:66 ECID:0
> > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
UPDATE
> > Line #: 320
> > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > Events.dbo.UpdateDb;1
> > 2005-07-13 23:11:15.90 spid3 Requested By:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> >
> > --
> > this is line which caused deadlock..
> >
> > UPDATE Line #: 320
> > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.access
> > FROM #Temp1 ae
> > )
> >
> > --
> > second part of select st which is victim SP. This procedure has all
select
> > statements only.
> >
> > SELECT a.*
> > FROM dbo.access ec
> > WHERE ec.accessID = 1 -->(1=Active)
> > AND ec.access = 23
> >
> >
> > Please note : we are getting almost 10-15 deadlocks with similiar
conditions
> > and same page lock with same set of sp's. Actually both sp's are run by
2
> > SQL
> > jobs which run after every 10 mins and i think this is the reason why we
are
> > getting deadlocks.
> >
> > --
> > Ravi
> >
> >
> >|||one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====> second SP in the meantime trying to select and place shared lock but it gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> > ==========Part of code of update operation================> > SELECT *
> > INTO #Activeaccesss
> > FROM dbo.access e (NOLOCK)
> > INNER JOIN dbo.accessGMTTime EGT On e.accessID = EGT.accessID
> > INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => > ecat.accessCategoryID
> > INNER JOIN dbo.accessStatus es ON e.accessStatusID => > es.accessStatusID
> > INNER JOIN dbo.accessDetail ed ON e.accessID = ed.accessID
> > -- AND ed.LanguageCode = ed.LanguageCode
> > INNER JOIN dbo.Language l ON ed.LanguageCode = l.LanguageCode
> >
> > INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode => edl.LanguageCode
> > AND l.LanguageID = edl.LanguageID
> > INNER JOIN dbo.accessLocation el ON e.LocationID = el.LocationID
> >
> > LEFT JOIN dbo.CountryLoc ec ON el.CountryCode = ec.CountryCode
> > AND ec.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID = tzl.TimeZoneID
> > AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => > dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> > LEFT JOIN dbo.State st ON e.CountryCode = st.CountryCode
> > And St.StateID
> > IN (Select StateID From StateLoc STL Where STL.LanguageCode => > ed.LanguageCode and STL.Description = el.StateProvince)
> > LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => > est.accessSubTypeID
> > LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => > etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => > estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> > LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID = cl.CampaignID
> > AND cl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID => il.InitiativeID
> > AND il.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> > LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID = s.SubsidiaryID
> >
> > LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> > LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> > WHERE e.IsWebPublishable = @.True
> > AND e.IsReadyForWebPublish = @.True
> > AND e.accessStatusID = 1
> > AND
> > (
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID = 1
> > AND e.accessEndDate >= GetDate()
> > AND e.WebPublishStartDate <= convert(varchar(10), Getdate(), 120)
> > AND e.WebPublishEndDate >= convert(varchar(10), Getdate(), 120)
> > )
> > OR
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID in (2, 4)
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > OR
> > (
> > e.accessCategoryID = 3
> > AND EGT.ArchivedStartDate <= @.CurDate
> > AND EGT.ArchivedEndDate >= @.CurDate
> > )
> > OR
> > (
> > e.accessCategoryID = 5
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > )
> >
> > SELECT @.RowCount = @.@.ROWCOUNT
> > -- some other code here
> >
> > UPDATE access
> > SET access.IsPublished = 0
> > -- , access.IsWebPublishable = 0 --geohey 2/14/03
> > WHERE accessID IN
> > (
> > SELECT es.accessID
> > FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => > ae.accessID
> > WHERE ae.accessID IS NULL
> > )
> > -- some other code here
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.accessID
> > FROM #Activeaccesss ae
> > )
> > ================================ENDS here===========> >
> > Another SP is SELECT STATEMENT both of these are in jobs and runs after
> > every 10 mins. Infact i also noticed that deadlocks are not coming after
> > every 5-10 mins but there is certain interval of 1 or 2 hours.
> >
> > Thanks,
> >
> >
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Could you post the complete text of the procedures involved?
> > >
> > > Generally, if you are selecting data from a table, and then updating
> that
> > > data later in the transaction, you are likely to have deadlocking
> > > situations. In this case, selecting with UPDLOCK hint helps.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > > 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.
> > >
> > > what i am really interested in knowing is
> > > 1) is it cycle deadlock or conversion deadlock.
> > > 2) is it really good to use NOLOCk hint in production env.
> > > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > > 4) IS page lock really good (this is happening now) .
> > > 5) Probable reasons why deadlock is coming ?
> > > 6) All the columns are indexed in tables.
> > >
> > > below is the log..
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:1
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
> CleanCnt:1
> > > Mode: SIU Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
> Flg:0x0
> > > Ref:1 Life:00000000 SPID:77 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
> SELECT
> > > Line #: 36
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
> IX
> > > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:2
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
> CleanCnt:1
> > > Mode: IX Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
> Flg:0x0
> > > Ref:2 Life:02000000 SPID:66 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
> UPDATE
> > > Line #: 320
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > > Events.dbo.UpdateDb;1
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > >
> > > --
> > > this is line which caused deadlock..
> > >
> > > UPDATE Line #: 320
> > > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> > >
> > > UPDATE access
> > > SET IsPublished = 1
> > > WHERE accessID IN
> > > (
> > > SELECT ae.access
> > > FROM #Temp1 ae
> > > )
> > >
> > > --
> > > second part of select st which is victim SP. This procedure has all
> select
> > > statements only.
> > >
> > > SELECT a.*
> > > FROM dbo.access ec
> > > WHERE ec.accessID = 1 -->(1=Active)
> > > AND ec.access = 23
> > >
> > >
> > > Please note : we are getting almost 10-15 deadlocks with similiar
> conditions
> > > and same page lock with same set of sp's. Actually both sp's are run by
> 2
> > > SQL
> > > jobs which run after every 10 mins and i think this is the reason why we
> are
> > > getting deadlocks.
> > >
> > > --
> > > Ravi
> > >
> > >
> > >
>
>|||You can see the kind of locks held by an operation using sp_lock. For
example:
BEGIN TRAN
INSERT INTO x (i) SELECT 1
DECLARE @.I int
SET @.I = (SELECT @.@.SPID)
EXEC sp_lock @.@.SPID
Yes, I suggest you use a UPDLOCK hint as part of your SELECT, if you access
the data, before updating it inside a transaction.
Please check SQL Server Books Online. It has various topics on
understanding, troubleshooting and resolving deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:5C326390-4EAA-41B2-B210-6FA84A5690F5@.microsoft.com...
one more question,
what types of lock will be placed on access table when i am using below Tsql
statement.
SELECT *
INTO #Activeaccesss
FROM dbo.access e
and also you mean to say i should rewrite my query like below
SELECT *
INTO #Activeaccesss
FROM dbo.access e (UPDLOCk)
Please confirm
Sanjay
"Narayana Vyas Kondreddi" wrote:
> Yes, instead of NOLOCK, try using UPDLOCK and see if it helps resolve the
> issue.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:D9BA5B4D-1301-43F9-A086-96D42F60FBDC@.microsoft.com...
> To summarise
> first
> 1) i am doing select into temporary tables depending on some conditions.
> [should i used UPD Lock hint here instead of NOLOCk].
> 2) Update access table and making one column as 0
> 3) Update same access table and making column as 1
> =====> second SP in the meantime trying to select and place shared lock but it
gets
> stucked up as there is already a X lock.
> any suggestion are more than welcome
> --
> Sanjay
>
> "Sanjay" wrote:
> > ==========Part of code of update operation================> > SELECT *
> > INTO #Activeaccesss
> > FROM dbo.access e (NOLOCK)
> > INNER JOIN dbo.accessGMTTime EGT On e.accessID =EGT.accessID
> > INNER JOIN dbo.accessCategory ecat ON e.accessCategoryID => > ecat.accessCategoryID
> > INNER JOIN dbo.accessStatus es ON e.accessStatusID => > es.accessStatusID
> > INNER JOIN dbo.accessDetail ed ON e.accessID =ed.accessID
> > -- AND ed.LanguageCode = ed.LanguageCode
> > INNER JOIN dbo.Language l ON ed.LanguageCode =l.LanguageCode
> >
> > INNER JOIN dbo.LanguageLoc edl ON ed.LanguageCode => edl.LanguageCode
> > AND l.LanguageID = edl.LanguageID
> > INNER JOIN dbo.accessLocation el ON e.LocationID =el.LocationID
> >
> > LEFT JOIN dbo.CountryLoc ec ON el.CountryCode =ec.CountryCode
> > AND ec.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.TimeZoneLOC tzl ON e.TimeZoneID =tzl.TimeZoneID
> > AND ed.LanguageCode = tzl.LanguageCode AND E.CountryCODE => > dbo.GetEMSCountryCode(TZL.ISOCountryCode)
> > LEFT JOIN dbo.State st ON e.CountryCode =st.CountryCode
> > And St.StateID
> > IN (Select StateID From StateLoc STL Where STL.LanguageCode => > ed.LanguageCode and STL.Description = el.StateProvince)
> > LEFT JOIN dbo.accessSubType est ON e.accessSubTypeID => > est.accessSubTypeID
> > LEFT JOIN dbo.accessTypeLoc etl ON est.accessTypeID => > etl.accessTypeID AND etl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.accessSubTypeLoc estl ON e.accessSubTypeID => > estl.accessSubTypeID AND estl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.Campaign c ON e.CampaignID = c.CampaignID
> > LEFT JOIN dbo.CampaignLoc cl ON c.CampaignID =cl.CampaignID
> > AND cl.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.InitiativeLoc il ON c.InitiativeID => il.InitiativeID
> > AND il.LanguageCode = ed.LanguageCode
> > LEFT JOIN dbo.District d ON e.DistrictID = d.DistrictID
> > LEFT JOIN dbo.Subsidiary s ON d.SubsidiaryID =s.SubsidiaryID
> >
> > LEFT JOIN dbo.Region r ON s.RegionID = r.RegionID
> > LEFT JOIN dbo.Area a ON r.AreaID = a.AreaID
> > WHERE e.IsWebPublishable = @.True
> > AND e.IsReadyForWebPublish = @.True
> > AND e.accessStatusID = 1
> > AND
> > (
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID = 1
> > AND e.accessEndDate >= GetDate()
> > AND e.WebPublishStartDate <= convert(varchar(10), Getdate(),
120)
> > AND e.WebPublishEndDate >= convert(varchar(10), Getdate(),
120)
> > )
> > OR
> > (
> > -- Included publishdate condition based on category check
> > e.accessCategoryID in (2, 4)
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > OR
> > (
> > e.accessCategoryID = 3
> > AND EGT.ArchivedStartDate <= @.CurDate
> > AND EGT.ArchivedEndDate >= @.CurDate
> > )
> > OR
> > (
> > e.accessCategoryID = 5
> > AND EGT.accessEndDate >= @.CurDateTime
> > AND EGT.WebPublishStartDate <= @.CurDateTime
> > AND EGT.WebPublishEndDate >= @.CurDateTime
> > )
> > )
> >
> > SELECT @.RowCount = @.@.ROWCOUNT
> > -- some other code here
> >
> > UPDATE access
> > SET access.IsPublished = 0
> > -- , access.IsWebPublishable = 0 --geohey 2/14/03
> > WHERE accessID IN
> > (
> > SELECT es.accessID
> > FROM Searchaccess es LEFT JOIN #Activeaccesss ae ON es.accessID => > ae.accessID
> > WHERE ae.accessID IS NULL
> > )
> > -- some other code here
> >
> > UPDATE access
> > SET IsPublished = 1
> > WHERE accessID IN
> > (
> > SELECT ae.accessID
> > FROM #Activeaccesss ae
> > )
> > ================================ENDS here===========> >
> > Another SP is SELECT STATEMENT both of these are in jobs and runs after
> > every 10 mins. Infact i also noticed that deadlocks are not coming
after
> > every 5-10 mins but there is certain interval of 1 or 2 hours.
> >
> > Thanks,
> >
> >
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Could you post the complete text of the procedures involved?
> > >
> > > Generally, if you are selecting data from a table, and then updating
> that
> > > data later in the transaction, you are likely to have deadlocking
> > > situations. In this case, selecting with UPDLOCK hint helps.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > > news:47AFE257-AA9E-4B4C-90AF-5220E70C1E91@.microsoft.com...
> > > 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.
> > >
> > > what i am really interested in knowing is
> > > 1) is it cycle deadlock or conversion deadlock.
> > > 2) is it really good to use NOLOCk hint in production env.
> > > 3) total db size is around 50 GB with 250 tables and hunderd of SP's.
> > > 4) IS page lock really good (this is happening now) .
> > > 5) Probable reasons why deadlock is coming ?
> > > 6) All the columns are indexed in tables.
> > >
> > > below is the log..
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Wait-for graph
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:1
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195456
> CleanCnt:1
> > > Mode: SIU Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 0::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x658c3540 Mode: S
> Flg:0x0
> > > Ref:1 Life:00000000 SPID:77 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 77 ECID: 0 Statement Type:
> SELECT
> > > Line #: 36
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event: GetProcDB;1
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
> IX
> > > SPID:66 ECID:0 Ec:(0x57DD9550) Value:0x6795cea0 Cost:(0/3C9948)
> > > 2005-07-13 23:11:15.90 spid3
> > > 2005-07-13 23:11:15.90 spid3 Node:2
> > > 2005-07-13 23:11:15.90 spid3 PAG: 8:1:2195457
> CleanCnt:1
> > > Mode: IX Flags: 0x2
> > > 2005-07-13 23:11:15.90 spid3 Grant List 1::
> > > 2005-07-13 23:11:15.90 spid3 Owner:0x6be35dc0 Mode: IX
> Flg:0x0
> > > Ref:2 Life:02000000 SPID:66 ECID:0
> > > 2005-07-13 23:11:15.90 spid3 SPID: 66 ECID: 0 Statement Type:
> UPDATE
> > > Line #: 320
> > > 2005-07-13 23:11:15.90 spid3 Input Buf: RPC Event:
> > > Events.dbo.UpdateDb;1
> > > 2005-07-13 23:11:15.90 spid3 Requested By:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode:
S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > > 2005-07-13 23:11:15.90 spid3 Victim Resource Owner:
> > > 2005-07-13 23:11:15.90 spid3 ResType:LockOwner Stype:'OR' Mode: S
> > > SPID:77 ECID:0 Ec:(0x07B0D540) Value:0x658c3100 Cost:(0/0)
> > >
> > > --
> > > this is line which caused deadlock..
> > >
> > > UPDATE Line #: 320
> > > Input Buf: RPC Event: Events.dbo.UpdateDb;1
> > >
> > > UPDATE access
> > > SET IsPublished = 1
> > > WHERE accessID IN
> > > (
> > > SELECT ae.access
> > > FROM #Temp1 ae
> > > )
> > >
> > > --
> > > second part of select st which is victim SP. This procedure has all
> select
> > > statements only.
> > >
> > > SELECT a.*
> > > FROM dbo.access ec
> > > WHERE ec.accessID = 1 -->(1=Active)
> > > AND ec.access = 23
> > >
> > >
> > > Please note : we are getting almost 10-15 deadlocks with similiar
> conditions
> > > and same page lock with same set of sp's. Actually both sp's are run
by
> 2
> > > SQL
> > > jobs which run after every 10 mins and i think this is the reason why
we
> are
> > > getting deadlocks.
> > >
> > > --
> > > Ravi
> > >
> > >
> > >
>
>
Subscribe to:
Posts (Atom)