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