How can two invocations of the same trigger be deadlocking against one
another?

The trigger is updating the SAME row that caused the trigger to fire
in the first place, so presumably this update should NOT be asking for
MORE locks. And if it's not asking for more locks, presumably it
shouldn't be deadlocking... a catch 22?

The trigger is updating only one column, in the same row that caused
the trigger to fire, and no, there is no index on that column.

Any ideas are welcome. I have attached errorlog and DDL stuff. This is
ASE 11.5.1.1 for NT.

Breck

00:00000:00081:2000/07/07 13:15:38.67 server Deadlock Id 10 detected.
Deadlock Id 10: Detected 1 deadlock chain(s) involved.

Deadlock Id 10: Process (Familyid 92, Spid 92, Suid 4) was executing a
UPDATE command in the trigger 'tru_person_response'.
Deadlock Id 10: Process (Familyid 81, Spid 81, Suid 4) was executing a
UPDATE command in the trigger 'tru_person_response'.
Deadlock Id 10: Process (Familyid 81, Spid 81) was waiting for a
'update page' lock on page 2171144 of the 'person_response' table in
database 5 but process (Familyid 92, Spid 92) already held a
'exclusive page' lock on it.
Deadlock Id 10: Process (Familyid 92, Spid 92) was waiting for a
'exclusive page' lock on page 2058157 of the 'person_response' table
in database 5 but process (Familyid 81, Spid 81) already held a
'exclusive page' lock on it.

Deadlock Id 10: Process (Familyid 92, Spid 92) was chosen as the
victim. End of deadlock information.

create trigger tru_person_response on person_response
for UPDATE as
/* ERwin Builtin Wed Jun 09 14:25:16 1999 */
/* See tru_template.sql for original template source code. */
/* MODIFIED to update only one column: updated_date_time. */
begin
declare @current_person_id int

-- This trigger assumes "set self_recursion off" (the default)
because
-- it does a nested update on the same table.

-- Initialize only the updated_date_time column.
-- This action will not cause the update trigger to fire again
-- because of the "set self_recursion off" setting.

update person_response
set person_response.updated_date_time = getdate()
from inserted
where person_response.person_test_id = inserted.person_test_id and

person_response.response_item_id = inserted.response_item_id


end

go



CREATE TABLE dbo.person_response (
person_test_id /* PK FK U X */ numeric ( 10 ) NOT
NULL,
response_item_id /* PK FK U */ varchar ( 20 ) NOT
NULL,
test_id /* FK U */ numeric ( 10 ) NOT
NULL,
field_score numeric ( 8, 3 )
DEFAULT 0 NOT NULL,
response_provided /* U */ char ( 1 ) DEFAULT
'N' NOT NULL,
actual_response_stored_here char ( 1 ) DEFAULT
'N' NOT NULL,
short_actual_response_text varchar ( 255 )
DEFAULT ' ' NOT NULL,
actual_response_text text NULL,
actual_response_number numeric ( 31, 6 )
DEFAULT 0 NOT NULL,
actual_response_high_number numeric ( 31, 6 )
DEFAULT 0 NOT NULL,
display_order int DEFAULT 0 NOT
NULL,
updated_date_time datetime DEFAULT
getdate() NOT NULL )

-- ***** Parents of dbo.person_response *****
-- dbo.person_test
-- dbo.response_item

-- ***** Primary Key for dbo.person_response *****
ALTER TABLE dbo.person_response ADD CONSTRAINT XPKperson_response
PRIMARY KEY NONCLUSTERED (
person_test_id,
response_item_id )

-- ***** Foreign Key from dbo.person_response to Parent
dbo.person_test *****
ALTER TABLE dbo.person_response ADD CONSTRAINT fk_person_test1 FOREIGN
KEY (
person_test_id )
REFERENCES dbo.person_test (
person_test_id )

-- ***** Foreign Key from dbo.person_response to Parent
dbo.response_item *****
ALTER TABLE dbo.person_response ADD CONSTRAINT fk_response_item1
FOREIGN KEY (
test_id,
response_item_id )
REFERENCES dbo.response_item (
test_id,
response_item_id )

-- ***** Unique Index for dbo.person_response *****
CREATE UNIQUE INDEX XIE2person_response ON dbo.person_response (
test_id,
response_item_id,
response_provided,
person_test_id )

-- ***** Non-Unique Index for dbo.person_response *****
CREATE CLUSTERED INDEX XIE1person_response ON dbo.person_response (
person_test_id )