quote:

---

Originally posted by Kristen

UPDATE U SET UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id

We do this all the time and I've never had a deadlock with it - I doubt we've been lucky...

---

drop table junk
go

create table junk (rowid int, threadID int, TotalCallCount int, updatedDate datetime)
go

insert junk values (1, 0, 0, getdate())
insert junk values (2, 0, 0, getdate())
go

create trigger trJunk on junk for update
as
begin
	update a set
		a.updatedDate = getdate()
		,a.TotalCallCount = isNull(a.TotalCallCount,0) + 1
	from	junk a
	join	inserted b
			on a.rowid = b.rowid
end
go

--after running the test:
select * from junk

rowid       threadID    TotalCallCount updatedDate             
----------- ----------- -------------- ------------------------
1           0           0              2005-07-11 11:04:28.370
2           3           60             2005-07-11 11:04:35.480

quote:

---

Originally posted by eyechart

two suggestions:
1. try using a rowlock hint
2. Assign the value of getdata() to a variable before your update. Make sure to include a SET NOCOUNT statement if you do this.

---

CREATE  TRIGGER Trig_Dead_Update
ON dbo.DeadTable
FOR INSERT, UPDATE
AS 
SET NOCOUNT ON -- Solves deadlock issues
IF @@ROWCOUNT=0 RETURN
UPDATE U SET 
	Updated = GETDATE()
FROM DeadTable U INNER JOIN INSERTED I ON U.AutoId = I.AutoId

quote:

---

Originally posted by Kristen

Sorry, don't know anything about your "C# .Net Win Form" combination to be able to run the test

Kristen

---

quote:

---

Originally posted by Kristen
Without NOCOUNT SQL would be sending an additional recordset to your application. Possibly your application was not processing it (you would specifically have to iterate all resultsets in order to process it). So maybe that was creating a further timedelay, and that was causing the timeout.

Best to have the NOCOUNT stuff at the top of ALL your Sprocs and Triggers.

---

quote:

---

Originally posted by TG

hmmm, I got slightly different results. After running your test code as well as mine, I found that what seems to consistently be the difference is having a nonclustered primary key on the table. If the PK is clustered, no deadlocking. If no PK, no deadlocking. Set nocount made no difference for me. Having a clustered index on a date column didn't matter either for me. I can't explain it. I'll have to do a little more digging.

---

quote:

---

Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. This count is made available when application calls SQLRowCount. ODBC 3.x applications must either call SQLRowCount to retrieve the result set or SQLMoreResults to cancel it. When an application executes a batch or stored procedure containing multiple INSERT, UPDATE, or DELETE statements, the result set from each modification statement must be processed using SQLRowCount or cancelled using SQLMoreResults. These counts can be cancelled by including a SET NOCOUNT ON statement in the batch or stored procedure

---

quote:

---

This variable is set to 0 by any statement that does not return rows, such as an IF statement

---