December 6, 2008 at 6:39 am
Comments posted to this topic are about the item Scope: The drastic caveat with Logon Triggers.
Johan
Learn to play, play to learn !
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 but most of the time this is me
December 15, 2008 at 3:29 am
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
December 15, 2008 at 3:50 am
Nice article...
December 15, 2008 at 3:52 am
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
Learn to play, play to learn !
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 but most of the time this is me
December 15, 2008 at 11:15 am
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]
December 15, 2008 at 11:21 am
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]
December 15, 2008 at 11:21 am
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
December 15, 2008 at 12:16 pm
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
Learn to play, play to learn !
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 but most of the time this is me
December 15, 2008 at 12:39 pm
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.
December 16, 2008 at 2:10 am
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.
December 16, 2008 at 11:28 am
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.
December 17, 2008 at 5:21 am
I didn't forget this thread !
I'm not at the office today.
I'll get back to this tomorrow.
Johan
Learn to play, play to learn !
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 but most of the time this is me
December 17, 2008 at 10:38 am
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
December 19, 2008 at 3:08 am
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
Learn to play, play to learn !
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 but most of the time this is me
December 19, 2008 at 6:35 am
ALZDBA (12/19/2008)
Scenarion 1 : addbegin tran ... commit tranJust 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 > 0begin
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