SQL Server Performance

Best practice to auto-update a datetime field?

Discussion in 'General DBA Questions' started by jfmenard, Jul 7, 2005.

  1. jfmenard New Member

    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
  2. mmarovic Active Member

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

    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".
  4. dineshasanka Moderator

    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
  5. jfmenard New Member

    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...
  6. mmarovic Active Member

    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.
  7. Adriaan New Member

    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?
  8. jfmenard New Member

    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,
  9. mmarovic Active Member

    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.
  10. jfmenard New Member

    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.
  11. FrankKalis Moderator

    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)
  12. jfmenard New Member

    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 Field1=@Field1 WHERE (Id=@Value1)
    (Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)
  13. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jfmenard</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Strange. Consider this just a WAG, but what happens when you change<br />IF @@ROWCOUNT=0 RETURN<br />to<br />IF @@ROWCOUNT&gt;0 <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Same thing:<br /><br />SQL : UPDATE MyTable SET Field1=@Field1 WHERE (Id=@Value1)<br /> (Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />[xx(] <br />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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  14. FrankKalis Moderator

    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)
  15. jfmenard New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />[xx(] <br />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=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I have not found any link to send you a private email, so here is my email address:<br />jean-francois.menard [at-sign] traf-park.com<br /><br />Thanks,
  16. FrankKalis Moderator

    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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  17. jfmenard New Member

    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
  18. mmarovic Active Member

    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.
  19. mmarovic Active Member

    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.
  20. FrankKalis Moderator

    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)
  21. FrankKalis Moderator

    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)
  22. jfmenard New Member

    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.
  23. FrankKalis Moderator

    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)
  24. jfmenard New Member

    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

Share This Page