Craig Farrell (10/6/2010)
WayneS (10/6/2010)
Craig Farrell (10/6/2010)
Answer: No and no.This code, as is, will not deadlock independently. There's no transaction calls.
What is the serverwide setting for isolation?
Craig, did you consider the effect of parallelism on the deadlocks? I can see this potentially being an issue. Again, the deadlock graphs will help show what's really going on.
Hm, I've seen parallelism cause timeouts, but I've never seen it cause a deadlock without an explicit two part transaction. You wouldn't happen to have some research links handy, would you?
Craig,
Just as a followup on this... if you look at the execution stack for this deadlock, you will see these two commands being run by the same process: UPDATE tsi_t_objects SET NRight = NRight + 2 WHERE NRight >= @parentRight
INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk, SortOrder, [Guid], ExternalId)
VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
Obviously, attempting an insert into the same table that you're updating could very well cause a deadlock. (However, since these are being run under the same thread (ECID), then parallelism isn't a factor here.) These statements could have been spawned off into separate processes.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes