Results 1 to 14 of 14

Thread: NOLOCK sentence

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: NOLOCK sentence

    Hello !!

    I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.

    Is It possible ???? How to do It (step by step) ?


    Thanks !!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can set transaction isolation level for your connection:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    "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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Choosing to not use locking in inherantly dangerous. It means that you can have all kinds of strange problems due to interactions with other spids (users) that can be impossible to diagnose because they are impossible to recreate.

    If you want to desend into the madness, all you need to do is: Be sure to read Customizing Locking AND all of the sections under it before you do this!

    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I wouldn't exaggerate the dangers of dirty reads. In a busy OLTP database there are hundreds of calls made to static tables and there is NO NEED to allow default READ COMMITTED behavior. Of course, when a DML is relying on a SELECT, then this should be seriously taken into account. Personally, I wouldn't use the SET statement to control transaction isolation level. It takes less than 10 characters to explicitly state how you want the data to be accessed by using table hints.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From my perspective, I don't exaggerate the dangers of dirty reads. I see them as a serious problem that lots of people overlook because allowing dirty reads is easier than solving the underlying problems in their code.

    While there are reasons that dirty reads are necessary, and many cases where they are convenient, I feel very strongly that dirty reads are both dangerous and overused.

    Most of the time when I encounter dirty reads, it is because of financial statements that don't balance consistantly in production systems, but almost always balance in test. The test system may only have a simulated load of 50 or 100 users, which may not be enough to cause the report to be unbalanced. It will never cause it to be unbalanced the same way twice. This leads to lots and lots of hair loss if someone doesn't think to check the locking to see that the developer specified that the statements don't need balance!

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.
    In the ideal world that is true, but I can't even get a vistor's pass for there anymore! I have to live and code in the real world.

    You are correct that final statements need to be done after the period is closed, but working statements are generated from shortly after the period starts until sometime after the period ends. When the bean counters get a statement that doesn't balance, they don't think about why it might not balance, they just scream that it doesn't. You can explain to them, and they understand that "work papers" might not always balance, but those papers have the same format/appearance as final papers and that can make the users crazy.

    In some ways, this is a training issue. The users need to realize that a statement for YE 12/31 isn't complete on 06/18, and if they stopped to think about it they'd know that it wasn't complete, but that still doesn't make them willing to excuse a statement that doesn't balance as of the time it was run.

    I can control what the developers do (to some extent). I have little or no control over what the users do. I fix the problems where I can.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I wash windows....

    And string up my dbas who use (NOLOCK)

    EDIT: And if you want, why not pin the code tables?

    EDIT2: And if you notice they want to do that for the ENTIRE db...

    WOW...holy corruption bartman!
    Last edited by Brett Kaiser; 06-23-04 at 14:46.
    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.

  9. #9
    Join Date
    Jun 2003
    Posts
    294
    I think I must continue using NOLOCK clause !

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Unhappy

    Well, I think it's more an app design issue rather than users training or NOLOCK. App design will be reconciled with db design which should have the same set of business rules as the foundation, just like the app design must. And all this results from a sound system analysis where each data/info flow is accounted for and projected onto app/db design...But, as you said, - that's an ideal world, and "they" don't pay us enough to dedicate several years of our lives to creating one
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2003
    Posts
    294
    The application development finished, and they are having locking problems, and I can't change it !

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't sound finished to me....

    If they have that many problems...you can bet the wheels are gonna fly right off when you change the ISOLATION LEVEL

    They may be complaining now...soon they will be blaimng you and the database for screwing up the data

    Oh

    MOO
    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.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jortiz
    The application development finished, and they are having locking problems, and I can't change it !
    Have they finished, or are they having locking problems? The two are mutually exclusive, they can't have both.

    Changing the locking level would be what we call a "Class 2 CLM (Career Limiting Move)". It might not get you fired, but whether it does or not you'll wish that it had!

    I think that life is too short to volunteer to sign up for that kind of problems.

    -PatP

  14. #14
    Join Date
    Jun 2003
    Posts
    294
    Thanks everybody !

Posting Permissions

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