Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Scope: The drastic caveat with Logon Triggers. Expand / Collapse
Author
Message
Posted Tuesday, December 16, 2008 11:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:52 PM
Points: 227, Visits: 699
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.



Post #620669
Posted Wednesday, December 17, 2008 5:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 7,001, Visits: 8,438
I didn't forget this thread !
I'm not at the office today.
I'll get back to this tomorrow.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #621103
Posted Wednesday, December 17, 2008 10:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 5:06 PM
Points: 69, Visits: 129
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
Post #621455
Posted Friday, December 19, 2008 3:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 7,001, Visits: 8,438
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

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #622713
Posted Friday, December 19, 2008 6:35 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #622860
Posted Friday, December 19, 2008 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 7,001, Visits: 8,438
The strange thing is that I get a @@trancount = 3 at execution time ...

So that would get me to writing another
while @@trancount > 0 
Begin
Commit Tran
End
Begin Tran


And then again, how would that behave in a MSDTC distributed transaction ??


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #622934
Posted Friday, December 19, 2008 8:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Hmm, you might want to figure out what kind of LOGON this is before you do that then...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #623015
Posted Saturday, December 20, 2008 3:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 7,001, Visits: 8,438
Maybe.

However, when using windows groups to grant security, we don't have control about the actual account being used.

So finding the "exceptions" would be a trial and error thing
and would only result in angry users and "unhappy customers".

Hence, the asynchrone solution is ideal


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #623446
Posted Saturday, December 20, 2008 7:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Yeah, I think that you're right.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #623534
Posted Tuesday, December 23, 2008 9:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
jeffrey yao (12/16/2008)
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.


++1

I still don't get why Isolation level is causing a server crash. I could guess that a "connection" crash could be possible due to timeout issues because of the table contention but "server" crash does not seems normal ...



* Noel
Post #624814
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse