SQL Server Performance Forum – Threads Archive

Best practice to auto-update a datetime field?

I use an UpdateDT datetime field on all tables I want to track the last modification date. This field is updated by a trigger in my Sql 2000 database: CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable FOR INSERT, UPDATE
AS
IF @@ROWCOUNT=0 RETURN
UPDATE U SET UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id This looked to me as a simple way of accomplishing what I needed. However this trigger is source of deadlocks. I made a simple .NET application that start 2 threads that each repetedly update the same row in MyTable. It takes a few seconds and a deadlock appears. My understanding is:
– Thread-1: Call Update on a row.
– Thread-2: Call Update on same row, but waits that Thread-1 finishes.
– Thread-1: Trigger gets called to update same row, waits that Thread-2 finishes.
– Sql Server detects deadlock and terminate one of the queries (let say Thread-2).
– Thread-2: Aborts and raise an error.
– Thread-1: Completes trigger Update statement.
– Thread-1: Commits main Update and terminates. I successfully solved this issue by using a pair of INSTEAD OF triggers like this (only UPDATE shown below): CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable INSTEAD OF UPDATE
AS
IF @@ROWCOUNT=0 RETURN
UPDATE U SET
U.Field1 = I.Field1,
U.Field2 = I.Field2,
U.Field3 = I.Field3,
U.Field4 = I.Field4,
U.Field5 = I.Field5,
U.Field6 = I.Field6,
U.Field7 = I.Field7,
U.Field8 = I.Field8,
U.Field9 = I.Field9,
U.UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id But this becomes just too complicated for a simple timestamping thing:
– Other (AFTER) triggers will think that all columns are updated when querying the UPDATED(field) function.
– If we change the table structure, we must not forget to update the INSTEAD OF trigger which complicates maintenance. Is there something I missed? Thanks, J-F

How about using default (getDate()) instead? I guess all you want is to set date of last change.

quote:Originally posted by mmarovic How about using default (getDate()) instead? I guess all you want is to set date of last change.

Precisely, I want the date of "last change" not "first insert".

Then, I don’t think there is a option other than the Update Trigger,
or else you have to handled this by the front end script code

quote:Originally posted by dineshasanka Then, I don’t think there is a option other than the Update Trigger,
or else you have to handled this by the front end script code

No, we want to keep track of rows that where altered using SQL tools like Enterprise Manager. I guess the main question is: > Is it normal that an AFTER trigger should never update its source table? If this is true, then I know a lot of DBA that do this mistake on a daily basis, like I did…

I see. I prefer performing all modifications using stored procedures. In that case I would just add updateDT = getDate() in update procedures. Performs much better then trigger. If it is not the option then you have to use the trigger afaik.

The trigger script that you posted is OK in itself – is it the complete trigger, or could it be there’s nothing else in the trigger causing the deadlock?

quote:Originally posted by Adriaan The trigger script that you posted is OK in itself – is it the complete trigger, or could it be there’s nothing else in the trigger causing the deadlock?

Finally someone who understand my concerns! Yes this simple trigger: CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable FOR INSERT, UPDATE
AS
IF @@ROWCOUNT=0 RETURN
UPDATE U SET UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id
is the source of all my deadlocks! I couldn’t beleive it when it occured so I made a simple application with many threads that did: UPDATE MyTable SET Field1 = 123 WHERE Id=1
many times in parallel and the deadlock appears after a few seconds! Can someone have a technical explanation for this? Thanks,

As Adriaan already asked, is that the only trigger on that table? I hope so, otherwise I would have to admit that I don’t understand.

quote:Originally posted by mmarovic As Adriaan already asked, is that the only trigger on that table? I hope so, otherwise I would have to admit that I don’t understand.

Yes. It is the ONLY trigger on that table.

Strange. Consider this just a WAG, but what happens when you change
IF @@ROWCOUNT=0 RETURN
… to
IF @@ROWCOUNT>0
…
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

quote:Originally posted by FrankKalis Strange. Consider this just a WAG, but what happens when you change
IF @@ROWCOUNT=0 RETURN
to
IF @@ROWCOUNT>0

Same thing: SQL : UPDATE MyTable SET [email protected] WHERE ([email protected])
(Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by jfmenard <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Strange. Consider this just a WAG, but what happens when you change IF @@ROWCOUNT=0 RETURN to IF @@ROWCOUNT>0 <hr height="1" noshade id="quote"></blockquote id="quote"> Same thing: SQL : UPDATE MyTable SET [email protected] WHERE ([email protected]) (Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.) <hr height="1" noshade id="quote"></blockquote id="quote"> [xx(] But this is interesting. If you want me to, I’ll ask this in the private MVP newsgroups. Contact me via PM here in the forum. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Btw, what is the output of trace flag 1204 as described in BOL under "troubleshooting deadlocks"? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis [xx(] But this is interesting. If you want me to, I’ll ask this in the private MVP newsgroups. Contact me via PM here in the forum. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> I have not found any link to send you a private email, so here is my email address: jean-francois.menard [at-sign] traf-park.com Thanks,

Sorry. Should have mentioned that you find this here, when you look at the profile of a member. Anyway, mail is on its way. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Ok I got news for everyone. Here is the test application I did to reproduce the deadlock constantly (C# .Net Win Form application with sources):
http://www.streamload.com/wizik/temp/DbDeadlockTester.zip Feel free to test this on your database to test your UPDATE triggers (you supply the command to execute and the connection string). I discovered that the problem arise when the clustered index is not the same as the primary key. i.e. I have a large table with an ID (identity) primary key field (where clustered index has been unchecked) and I created a separate clustered index on a datetime field for faster queries. So when the initial UPDATE and the trigger UPDATE where clauses are both NOT on the clustered index, then the deadlock is almost immediate with DbDeadlockTester. That is my conclusion for the moment. Any comments? Thanks, J-F

When you have clustered index on dateTime column but subsequent rows usually don’t contain dateTime column value in the same order you can expect a lot of page splits.
That causes data fragmentation so it even makes selects slower.
I guess in your case deadlocks can be related with page splits… However I don’t think we can figure it out for sure. Anyway, I think you have the solution, just create clustered index on identity column.

Can you recreate (in test environmnet) your clustered index on dateTimeColumn to have fillFactor = 50 and then repeat your tests? I’m curious what would happen in that case.

I am still interested in a repro script that I can post on the private MVP newsgroups.
And I’ve seen, that you also posted this on SQLTeam.com. Recently Paul Randal joined there. Paul is dev lead of the SQL Server storage engine. Maybe you can make him aware of your problem by sending him a PM via his profile. If he doesn’t know an answer, he maybe pass this to some guys from the dev team who will know. Nothing else would I do with the repro script. Even if you think you solved your problem for the moment, think about it again. PRIMARY KEY and CLUSTERED INDEX are not always the same. In fact, most of the time, one would want to save the CLUSTERED INDEX for a better suited combination of columns. So, you only have a kludgy workaround at the moment. Not very satisfying, I’d say. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Okay, as promised here’s the replies I got so far. This one comes from SQL Server MVP Erland Sommarskog:

quote:
OK, so here is a repro which is somewhat more digestable for us SQL
people. First create this: SELECT * INTO Orders FROM Northwind..Orders
go
CREATE CLUSTERED INDEX clust_ix ON Orders (ShippedDate)
ALTER TABLE Orders ADD CONSTRAINT pk_orders
PRIMARY KEY NONCLUSTERED (OrderID)
go
CREATE TRIGGER Trig_Orders_Update ON [dbo].[Orders]
FOR INSERT, UPDATE
AS
WAITFOR DELAY ’00:00:05′
UPDATE U SET OrderDate = GETDATE()
FROM Orders U INNER JOIN inserted I ON U.OrderID = I.OrderID Then run this statement from two query windows: UPDATE Orders SET EmployeeID=5 WHERE OrderID=10254 So why this deadlock? I was actually lucky to be able catch the deadlock
with aba_lockinfo. (I think the first time I’ve ever caught a deadlock
in the act.) I’ve attached an Excel book with the output. The first process holds an exclusive lock on the key it has updated. This
blocks the second process. That’s the easy part. But then for some reason, both processes thinks they need an UPDATE lock
on pk_orders. Now, this lock is held only for the duration of the UPDATE
statement, and the released. Thus, the second process gets its UPDATE
lock for the first UPDATE statement, because the first have already
released its. But then the first process needs it a second time, is now
block by the first process => deadlock. But what good is this lock on pk_orders, and why is it released? Recall
that UPDATE is really only a read lock, but with the special characterestic
that only one process at a time can hold an UPDATE lock. I guess the
lock is needed to prevent other processes changing the index that is
accessed for the update. Really why it is a shared lock, I don’t know,
but probably it has to do with what would happen if the update affected
the index. In this case the shared lock must be converted to exclusive.
If two processes does this at the same time, there is a conversion deadlock. This reasoning also leads to a hint how avoid the deadlock: set the
transaction isolation level to REPEATABLE READ. —
Erland Sommarskog, SQL Server MVP

Will this help you? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

How do I set the transaction isolation level to REPEATABLE READ? Can this be handeled in the trigger? Because I don’t normally start explicit transactions.

Have a look at "SET TRANSACTION ISOLATION LEVEL" in BOL. You need to do in your UPDATE batch, not in the trigger. That way it works fine for me. Another option might be to specify an UPDLOCK hint to avoid the conversion deadlock. I know this is not completely satisfying, but at least it’s working. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

THANKS! Using a transaction isolation level of Repeatable Read actually prevents the deadlock. The only drawback is I have to start a transaction in my application, but I can live with that! Thanks again to everyone that contributed to this issue. J-F

]]>