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
