Scope: The drastic caveat with Logon Triggers.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Comments posted to this topic are about the item Scope: The drastic caveat with Logon Triggers.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Phil Factor

    SSCoach

    Points: 19913

    Johan,

    Great article. That looks like quick thinking. How long did it take to figure out the Isolation-level problem? There is a moral for everyone in this story. DAC will, some day, save your bacon.

    Best wishes,
    Phil Factor
    Simple Talk

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article...

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Thank you for the comments.

    Fortunately it didn't take me that much time to figure out the issue :Whistling:

    I'm always using my version of sp_who to see what's going on in an instance.

    That proc shows me isolationlevels, ....

    I'm one of the happy few that has a mainframe background :w00t:,

    so one of the first points of interest is the isolation level of a connection.

    Also ... one of the error log messages stated : ...attempted to unlock a resource it does not own...

    which may point to a transaction related problem.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • RBarryYoung

    SSC Guru

    Points: 143327

    Hmm, I wonder if starting a local transaction and then committing it before the trigger exited would have resolved this?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung

    SSC Guru

    Points: 143327

    I agree with you Johan, it is important to remember that unlike their corresponding DDL events, DDL triggers do execute synchronously and in the source execution context. Consequently they do always have some effect on the source process and activity.

    My general rule of thumb is that you should always prefer to use DDL Event notification over DDL triggers, unless you actually do want to have some effect on the triggering process or command (such as intentionally rejecting certain Logins).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • noeld

    SSC Guru

    Points: 96590

    Excellent war story.

    I think Rbarryyoung is right on the money. I believe that the lack of explicit transaction demarcation is the real problem.

    Do you mind specifying explicit "begin tran"/"commit tran" ? It should work, right?


    * Noel

  • Johan Bijnens

    SSC Guru

    Points: 134265

    rbarryyoung (12/15/2008)


    I agree with you Johan, it is important to remember that unlike their corresponding DDL events, DDL triggers do execute synchronously and in the source execution context. Consequently they do always have some effect on the source process and activity.

    My general rule of thumb is that you should always prefer to use DDL Event notification over DDL triggers, unless you actually do want to have some effect on the triggering process or command (such as intentionally rejecting certain Logins).

    noeld (12/15/2008)


    Excellent war story.

    I think Rbarryyoung is right on the money. I believe that the lack of explicit transaction demarcation is the real problem.

    Do you mind specifying explicit "begin tran"/"commit tran" ? It should work, right?

    Well, I will try to test it in collaboration with my biztalk dev team.

    But I think it would be over simplified to just incorporate the explicit "begin tran"/"commit tran". E.g. when using implicit transactions, one should also have to check for the @@trancount !, and then basically violate the concept of the implicit transaction itself.:ermm:

    Secondly, considered the absence of the need to interfere in realtime, the only valid approach is the one using sqlserver events.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Daniel Ruehle

    SSC-Addicted

    Points: 411

    Could you not have just added:

    set transaction isolation level read committed

    after the "set nocount on" in the trigger to set the isolation level for the duration of the trigger execution. Surely using the events is ultimately better, but that should have fixed it.

  • alexander.plepler

    Grasshopper

    Points: 14

    Very useful and dramatic story!

    The asynchronous approach is always preferable for me so long as I don't like to do something immediately (e.g. rollback or blocking).

    Thanks for share.

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

    Informative article.

    But I am still trying to understand why the transaction isolation level plays a role here. Before a connection is established, there is no isolation level to that "would-be" connection. The only thing I can think of is that the user table used in the trigger have a table-scope lock (by either another existing connection or some other applications) that prevents the table being accessed when the trigger trying to update the user table when a new connection is being established. So this may not have anything to do with the isolation level, but because of something else, like lock escalation, or simply a table-wide lock on the hot table.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    I didn't forget this thread !

    I'm not at the office today.

    I'll get back to this tomorrow.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Richard Polichetti

    Old Hand

    Points: 315

    Hi,

    It takes a lot of time, effort, and courage to write about something that didn't work. We all thank you.

    A) it proves we're all human and we try hard at IT but sometimes we mess it up.

    B) we learn MORE about how we solve problems when we read about something that didn't go well than when something went perfectly.

    C) AND we learn more about the creative ways to escape problems (like the DAC) the we never would have learned about otherwise (I tried it and wow! it works! Who knew?).

    Richard

  • Johan Bijnens

    SSC Guru

    Points: 134265

    I'm glad one of our biztalk devs is very cooperative today 😉

    Scenarion 1 : add begin tran ... commit tran

    Just encapsulating the trigger with its own Begin tran ... commit tran, off course didn't do the trick.

    Because: that tran is just an augmentation of the @@trancount which - because of the "set implicit transactions on" >0 at that time.

    When I added a while loop :crazy:

    while @@trancount > 0

    begin

    commit tran

    end

    resulted in a failure of the biztalk apps.

    Scenario 2: set transaction isolation level read committed

    IMO this one would be plausible, but once again, it resulted in messages like

    Process ID 61 attempted to unlock a resource it does not own: DATABASE: 7 Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

    and sqlserver got itself messed up once again.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • RBarryYoung

    SSC Guru

    Points: 143327

    ALZDBA (12/19/2008)


    Scenarion 1 : add begin tran ... commit tran

    Just encapsulating the trigger with its own Begin tran ... commit tran, off course didn't do the trick.

    Because: that tran is just an augmentation of the @@trancount which - because of the "set implicit transactions on" >0 at that time.

    When I added a while loop :crazy:

    while @@trancount > 0

    begin

    commit tran

    end

    resulted in a failure of the biztalk apps.

    Try it like this:

    Commit Tran

    IF @@trancount = 1

    Begin

    Commit Tran

    Begin Tran

    End

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply