Re: Transaction Isolation Level
From: Matthew Bando (anonymous_at_discussions.microsoft.com)
Date: 09/10/04
- Next message: David G.: "Re: Triggers, performance, and distributed processing"
- Previous message: Azul: "Network drivers missing"
- In reply to: Toby Herring: "Re: Transaction Isolation Level"
- Next in thread: Stephen Dybing [MSFT]: "Re: Transaction Isolation Level"
- Reply: Stephen Dybing [MSFT]: "Re: Transaction Isolation Level"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 10 Sep 2004 09:28:15 -0700
You're clearly facing difficult problems, and your
suggestions would make good options, but you do realize
that you potentially sacrifice data integrity by changing
the default locknig/isolation level behavior?
While I sympathize with the waste of man hours you have
had to endure due to these options not being available,
the blame could also be laid on the initially improperly
designed system.
I have never seen replication suggested as a real time
mechanism for keeping multiple servers (or multiple
databases on the same server) in synch. Distributed
transactions provide this, but have a large amount of
overhead. Using replication for this would be like me
using my 2 door coupe as a public transportation system.
As long as the volume is low (very low), everyone gets to
where they're trying to go in a timely fashion, but if
there are more than 4 or 5 concurrent passnegers
involved, we're out of luck. Honestly, I've never found
a real world situation where replication was the
appropriate solution.
There is a website where you can submit suggestions of
enhancements to SQL Server to the Microsoft team. I
don't remember the URL, but I know that others here at
the newsgroup know it. Maybe these are things they can
implement in a future release.
Just out of curiosity, what size servers are you working
with?
I know this doesn't really help your dilemma, but perhaps
one day it can help someone.
Matthwe Bando
Matthew.Bando@ leave this out csctgi.com
>-----Original Message-----
>My biggest problems with locking are twofold:
>
>Due to the locking/deadlocking problems we have, we have
to use NOLOCK hints
>on almost every single SELECT query we write in our
procedures. It would be
>nice in our case if we could specify this as the default
for the entire
>database, and only have to specify locking hints when we
need locks. Due to
>the response we get to our replication-related problems,
I just find it
>slightly annoying that locks are also implemented with
the "This is the way
>we use it and nobody will ever need different default
actions" mentality
>that the locking system is designed with.
>
>Second, every INSERT/UPDATE/DELETE statement has a
ROWLOCK hint, to prevent
>lock escalation. It would be a great benefit to us if
we had a setting for
>the database that would globally turn off auto-
escalation, with a lock hint
>or a command similar to ISOLATION LEVEL that would allow
us to re-enable the
>system's auto-escalation on a query-by-query or proc-by-
proc basis.
>
>These two options would have saved us hundreds of man-
hours :-/
>
>--
>Toby Herring
>MCDBA, MCSD, MCP+SB
>Need a Second Life?
>http://secondlife.com/ss/?
u=03e0e5b303c234bf08e80ee40119a65e
>
>
>.
>
- Next message: David G.: "Re: Triggers, performance, and distributed processing"
- Previous message: Azul: "Network drivers missing"
- In reply to: Toby Herring: "Re: Transaction Isolation Level"
- Next in thread: Stephen Dybing [MSFT]: "Re: Transaction Isolation Level"
- Reply: Stephen Dybing [MSFT]: "Re: Transaction Isolation Level"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
