Page 1 of 3 123 LastLast
Results 1 to 15 of 43

Thread: Isolation Level

  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Isolation Level

    Hi, folks. Please guide.
    I have a VB application that is used for production and reporting. I 've been having alerts for deadlocks that popup after every 2 or 3 minutes. I am planning to seperate reporing server by using transactional replication from production server to the reporting server. However some reports update and insert data so i need reporting server to be enabled for DML.

    Is there any option on the server-level where i can force each user to operate in READ-UNCOMMITTED mode instead of specifying WITH (NOLOCK) in the queries of my application. Dirty reads won't bother me in current situation, i guess the propotion of fast reads would be a better trade-off.
    New to SQL, Thanx for helping!!


    Howdy.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wait a little bit and Pat will tell you what he REALY thinks about it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What about x002548?

    Recipe for Disaster: Beginner+Isolation Levels

    What was that Molly Hatchet song?

    Anyway...if you're setting up a reporting server, which is a good idea, why do you need to updates?

    And how current does the reports need to be? up to the minute?

    Problem with reading dirty data is that people may take action on it or with it...that could cause a lot of problems and you could get to the point where you don't know what is real and what is not.

    I don't know of any case where I've ever done this...Anyone?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have not heard that using (nolock) makes the queries any faster. The overhead that the locks create is not a great deal, in the face of most queries. The real problem in the application is the deadlocks. See if you can address those directly. Microsoft has a page or two dedicated to solving deadlocking problems. There is also a decent article in the Books Online. Take a look at these, and see if you can modify the application to eliminate some or all of the deadlocks.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh, I forgot, Brett is also very passionate about it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    And since you didn't run by us the idea of putting that SET statement in your procedures only, we're assuming you're using inline code....on a web server.

    ?? Or, just a bad guess.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    [Set Transaction isolation level] is one option
    another is a optimizer\table hints area in the query
    it is held in the from clause.

    now that i told you, do not use either of these, as they are inherently evil. my suggestion is to fix the deadlock problem.
    by the way optimizer hints override isolation levels if you are using both in the same session.

  8. #8
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Quote Originally Posted by rdjabarov
    Wait a little bit and Pat will tell you what he REALY thinks about it
    What are you doing in the forum here.I guess Pat is your advisor or u r just marketing for him.


    [QUOTE=Brett Kaiser]What about x002548?
    "Recipe for Disaster: Beginner+Isolation Levels"
    Brett, how do people get YAK-CORRAL, are they so by birth? I suspect u didn't begin with SQL, you were just the best on SQL when u came into the world.
    No should start with SQL any more, for they would lead to Disasters. Tell me the secret how to take-over SQL without beginning with it.
    "What was that Molly Hatchet song?"
    You ought to join a theater brett, u r a good joker and ur skills shall b much more admired there.
    "Anyway...if you're setting up a reporting server, which is a good idea, why do you need to updates?"
    That's the point. In my reports, users are allowed to change the status of customers billing within the results displayed by reports. For example user runs a report for a customer having due balance, and rebill to customer by setting a specific column to BILLED for that particular records etc.

    Quote Originally Posted by MCrowley
    I have not heard that using (nolock) makes the queries any faster. The overhead that the locks create is not a great deal, in the face of most queries. The real problem in the application is the deadlocks. See if you can address those directly..... .
    I was reading a page:
    "Process1 sets the Shared lock on the page, because there are no another locks on this page.
    Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
    Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.
    Process4 cannot set Shared lock on the page before Process3 will be finished.
    This results in higher concurrency and in lower consistency."

    Since there's very heavy reporting, users have to wait for long when the press the SAVE button.
    When i run reports with NOLOCK hint, trace shows less READS and results get displayed earlier.
    The most interesting thing's that my user gets deadlock-error when they run reports; not on the forms.


    I have to post questions here no matter they are stupid. Please be generous and bear with the beginners.It's a part of learning for u to get Professional by bearing with juniors and answering their questions softly; rather than discouraging them to post questions.

    Love u all.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by thebeginner
    What are you doing in the forum here.I guess Pat is your advisor or u r just marketing for him.
    No, not hardly!

    I think the point that rdjabarov was trying to make is that I usually get rather excited when I respond to questions like this. When I see beginners trying to defeat the safeguards built into a product, I see it like someone learning to use a chainsaw wanting to take the guards off because they get in the way. Yeah, you can take them off, but unless you know EXACTLY what you are doing, and more importantly the possible consequences of that action, it is a bad idea.

    If you want your queries to be as fast as possible, prerun the query once and save the results. Then every time the query is rerun, serve back up the same results. The results will almost always be wrong, but they'll be quick and there will be few problems with locking/blocking/etc. Overriding the locking behavior will produce results that are just as wrong, but in much more subtle ways because they will be almost right and the errors will be different every time the query is run.

    Some folks need to find this out for themselves. They think that they've found a way to "beat the system" by turning off the locking instead of fixing the underlying problems within their application... That is basically like taking the guards off of the saw and marveling at how much easier it is to cut things, at least until some important part of your carcass or a friend's is what gets cut!

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by thebeginner
    It's a part of learning for u to get Professional by bearing with juniors and answering their questions softly; rather than discouraging them to post questions.
    Okely Dokely

    Here you go....knock yourself out:

    BOL
    Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read).
    Using Pubs sample database

    Code:
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO
    BEGIN TRANSACTION
    SELECT * FROM publishers
    SELECT * FROM authors
    ...
    COMMIT TRANSACTION
    Good Luck and let us know where you end up...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [speaking softly]
    Oh, and btw, that's the answer you want....not the answer you need
    [/speaking softly]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    [speaking softly]
    Oh, and btw, that's the answer you want....not the answer you need
    [/speaking softly]
    Yeah, the response that Brett gave you is kind of like helping the learner to take off the guards because they don't have enough fingers left to turn the bolts!

    -PatP

  13. #13
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    "Good Luck and let us know where you end up..."
    That's better than
    "Recipe for Disaster: Beginner+Isolation Levels"

  14. #14
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Talking

    Quote Originally Posted by Brett Kaiser
    [speaking softly]
    Oh, and btw, that's the answer you want....not the answer you need
    [/speaking softly]
    Guys, Which is the second biggest Commedy-THEATER in USA. I wanna Brett there too, for i couldn't been able to see him live yet.
    Oh Hollywood i guess!

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Yeah, the response that Brett gave you is kind of like helping the learner to take off the guards because they don't have enough fingers left to turn the bolts!

    -PatP
    I sure wish I knew what that meant
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •