It would appear that I am having a strange deadlock error. I have two
different stored procedures that are both inserting into the same table.
Other than that they have nothing in common. One of the procs has a locking
hint of (TABLOCK, HOLDLOCK) on the insert so that the table isn't modified
until after the tranaction is completed. I thought that deadlocking was the
locking of two tables by two processes that that the other wanted? I didn't
think that dead locks could happen with two processes and one table.Hi Wilbur
There are two major / common deadlock
scenarios. "Cyclical" deadlocking is where two connections
acquire locks on objects in reverse order, deadlocking
each other. "Conversion" deadlocking occurs when two
connections acquire shared locks on the same resource (eg
table) but then both want to convert the shared lock to
exclusive. Because they both already hold a shared lock on
that resource, who's to say either should let go first -
therefore they deadlock.
Your scenario sounds to me like a conversion deadlock
scenario. Your use of HOLDLOCK indicates you're acquiring
a shared lock (eg select). If you insert into that same
table, the connections will try to convert that shared
lock they're already holding to exclusive & deadlock each
other.
In short, what you might really want is UPDLOCK, which
acquires a lock that always be converted to exclusive
(almost like acquiring an exclusive in the first place) &
therefore avoids the conversion deadlock scenario.
HTH
Regards,
Greg Linwood
SQL Server MVP
>--Original Message--
>It would appear that I am having a strange deadlock
error. I have two
>different stored procedures that are both inserting into
the same table.
>Other than that they have nothing in common. One of the
procs has a locking
>hint of (TABLOCK, HOLDLOCK) on the insert so that the
table isn't modified
>until after the tranaction is completed. I thought that
deadlocking was the
>locking of two tables by two processes that that the
other wanted? I didn't
>think that dead locks could happen with two processes and
one table.
>
>.
>
Showing posts with label strange. Show all posts
Showing posts with label strange. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
deadlock with Win2003 and Clustered SQL Server 2000
Hi!
We've encountered a strange deadlock problem when migrating to a loadbalance
d Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on
two Windows 2000 servers.
The scenario is that we receive 10.000 records in XML which need to be inser
ted into - or updated in the database depending on whether we already know t
he record or not. The problem is that the system suddenly deadlocks while ru
nning a simpel UPDATE. The
UPDATE does however results in triggering an update trigger which in turn tr
iggers another trigger. This has not been a problem on 3 other production se
rvers running the same software and receiving the same amount of data. The o
nly difference is the use o
f Win2003 as frontend and a clustered SQL Server.
We do not currently use Explicit Transactions - but has tried it without any
luck. We've done traces which show the records involved in the deadlock has
n't got any obvious relations - other than being in the same table (differen
t primary keys al around).
A friend of mine has experienced a similary problem - and said it had someth
ing to do with Win2003 and SQL2000 together, but can't find any information
regarding this particullar problem...
Any hint would be greatly appriciated
Best regards,
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs s
ome fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND d
eletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Lef
t(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupi
d;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap
a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two diff
erent records that you are sure of doesn't link to any shared records throug
h foreign keys!
Regards,
Michael B. Hansen
-- Michael B. Hansen wrote: --
Update(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...
different records that you are sure of doesn't link to any shared records
through foreign keys!
and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.
SELECTs some fields from the updated row and UPDATEs another row:
personid=797 AND deletetime IS NULL AND userpoolid=0
50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;
and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
We've encountered a strange deadlock problem when migrating to a loadbalance
d Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on
two Windows 2000 servers.
The scenario is that we receive 10.000 records in XML which need to be inser
ted into - or updated in the database depending on whether we already know t
he record or not. The problem is that the system suddenly deadlocks while ru
nning a simpel UPDATE. The
UPDATE does however results in triggering an update trigger which in turn tr
iggers another trigger. This has not been a problem on 3 other production se
rvers running the same software and receiving the same amount of data. The o
nly difference is the use o
f Win2003 as frontend and a clustered SQL Server.
We do not currently use Explicit Transactions - but has tried it without any
luck. We've done traces which show the records involved in the deadlock has
n't got any obvious relations - other than being in the same table (differen
t primary keys al around).
A friend of mine has experienced a similary problem - and said it had someth
ing to do with Win2003 and SQL2000 together, but can't find any information
regarding this particullar problem...
Any hint would be greatly appriciated
Best regards,
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs s
ome fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND d
eletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Lef
t(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupi
d;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap
a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two diff
erent records that you are sure of doesn't link to any shared records throug
h foreign keys!
Regards,
Michael B. Hansen
-- Michael B. Hansen wrote: --
Update(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...
quote:
> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two
different records that you are sure of doesn't link to any shared records
through foreign keys!
quote:
>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -
and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.
quote:
> The way to reproduce the problem is to have an UPDATE-trigger that
SELECTs some fields from the updated row and UPDATEs another row:
quote:
> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE
personid=797 AND deletetime IS NULL AND userpoolid=0
quote:
>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,
50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;
quote:
> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections
and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
quote:sql
> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen
deadlock with Win2003 and Clustered SQL Server 2000
Hi
We've encountered a strange deadlock problem when migrating to a loadbalanced Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on two Windows 2000 servers
The scenario is that we receive 10.000 records in XML which need to be inserted into - or updated in the database depending on whether we already know the record or not. The problem is that the system suddenly deadlocks while running a simpel UPDATE. The UPDATE does however results in triggering an update trigger which in turn triggers another trigger. This has not been a problem on 3 other production servers running the same software and receiving the same amount of data. The only difference is the use of Win2003 as frontend and a clustered SQL Server
We do not currently use Explicit Transactions - but has tried it without any luck. We've done traces which show the records involved in the deadlock hasn't got any obvious relations - other than being in the same table (different primary keys al around)
A friend of mine has experienced a similary problem - and said it had something to do with Win2003 and SQL2000 together, but can't find any information regarding this particullar problem...
Any hint would be greatly appriciated :
Best regards
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two different records that you are sure of doesn't link to any shared records through foreign keys
Regards
Michael B. Hanse
-- Michael B. Hansen wrote: --
Update(!
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row
initial UPDATE
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=
trigger
(...) start of trigger (...
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email
BEGI
SELECT fname, lname, groupid, email INTO #tmp FROM inserte
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid
END
(...) end of trigger (...
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE
while 1=1 begi
UPDATE gc_persons ...
en
Do anyone know of a fix for this
Regards
Michael B. Hansen|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...
> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two
different records that you are sure of doesn't link to any shared records
through foreign keys!
>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -
and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.
> The way to reproduce the problem is to have an UPDATE-trigger that
SELECTs some fields from the updated row and UPDATEs another row:
> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE
personid=797 AND deletetime IS NULL AND userpoolid=0
>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,
50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;
> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections
and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen|||I've found a workaround for the problem
It seems that SP3 has some kind of a bug with regards to update-triggers
1) The 'UPDATE(field)' function doesn't seem to give do the check correctly - it seems to always return true
eg.
IF UPDATE(field1
BEGI
END
is always execute
2) The "UPDATE <table> SET field=value FROM inserted" seems to lock a whole page (or something like that) instead of the individual rows it updates. The workaround to this is to include WITH (UPDLOCK) in the UPDATE-clause
eg.
UPDATE <table> WITH (UPDLOCK) SET field=value FROM inserte
These two issues first showed themselves after(!) we updated to SP3 - and we've only been able to reproduce the second issue on a clustered SQLServer2000 running on a Windows Server 2003
Regards
Michael B. HAnsen
We've encountered a strange deadlock problem when migrating to a loadbalanced Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on two Windows 2000 servers
The scenario is that we receive 10.000 records in XML which need to be inserted into - or updated in the database depending on whether we already know the record or not. The problem is that the system suddenly deadlocks while running a simpel UPDATE. The UPDATE does however results in triggering an update trigger which in turn triggers another trigger. This has not been a problem on 3 other production servers running the same software and receiving the same amount of data. The only difference is the use of Win2003 as frontend and a clustered SQL Server
We do not currently use Explicit Transactions - but has tried it without any luck. We've done traces which show the records involved in the deadlock hasn't got any obvious relations - other than being in the same table (different primary keys al around)
A friend of mine has experienced a similary problem - and said it had something to do with Win2003 and SQL2000 together, but can't find any information regarding this particullar problem...
Any hint would be greatly appriciated :
Best regards
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two different records that you are sure of doesn't link to any shared records through foreign keys
Regards
Michael B. Hanse
-- Michael B. Hansen wrote: --
Update(!
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row
initial UPDATE
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=
trigger
(...) start of trigger (...
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email
BEGI
SELECT fname, lname, groupid, email INTO #tmp FROM inserte
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid
END
(...) end of trigger (...
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE
while 1=1 begi
UPDATE gc_persons ...
en
Do anyone know of a fix for this
Regards
Michael B. Hansen|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...
> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two
different records that you are sure of doesn't link to any shared records
through foreign keys!
>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -
and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.
> The way to reproduce the problem is to have an UPDATE-trigger that
SELECTs some fields from the updated row and UPDATEs another row:
> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE
personid=797 AND deletetime IS NULL AND userpoolid=0
>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,
50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;
> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections
and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen|||I've found a workaround for the problem
It seems that SP3 has some kind of a bug with regards to update-triggers
1) The 'UPDATE(field)' function doesn't seem to give do the check correctly - it seems to always return true
eg.
IF UPDATE(field1
BEGI
END
is always execute
2) The "UPDATE <table> SET field=value FROM inserted" seems to lock a whole page (or something like that) instead of the individual rows it updates. The workaround to this is to include WITH (UPDLOCK) in the UPDATE-clause
eg.
UPDATE <table> WITH (UPDLOCK) SET field=value FROM inserte
These two issues first showed themselves after(!) we updated to SP3 - and we've only been able to reproduce the second issue on a clustered SQLServer2000 running on a Windows Server 2003
Regards
Michael B. HAnsen
Monday, March 19, 2012
Deadlock detection problem! help
Hello faculties,
i'm encountering a strange a deadlock problem on my remote server
which is used to give application demo to the client. It has happened
that on one of the databases a deadlock situation is taking place.
What is the most detailed way to detect such the cause of such a
deadlock to the innermost level of detail, like what statements, stored
procedures and locks are causing the deadlock to occur.
Guys! please help me out!
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***debian mojo (debian_mojo@.yahoo.com) writes:
> i'm encountering a strange a deadlock problem on my remote server
> which is used to give application demo to the client. It has happened
> that on one of the databases a deadlock situation is taking place.
> What is the most detailed way to detect such the cause of such a
> deadlock to the innermost level of detail, like what statements, stored
> procedures and locks are causing the deadlock to occur.
DBCC TRACEON (1204, 1)
DBCC TRACEON (3605, 1)
It's common to add these as start up parameters (best done from Enterprise
Manager).
This gives you a deadlock trace to the SQL Server log. Unfortunately,
though, it's a bit cryptic.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
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 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
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
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
Subscribe to:
Posts (Atom)