Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    6

    Question Unanswered: Select with NO_LOCK

    Is there a way to select without locking the table? If I use NO_LOCK then
    un-committed data gets retrieved as well, is there a way around it?

    thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No

    What's the problem?
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    6
    Inherited a system that causes a lot of dead locks because of the IX locks and IS locks on the system. It will be dead easy if simple sql didn't
    attempt any lock on a sql when you only want a snapshot of the data.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why?

    What's the front end written in? Access?
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    6
    nope, VB. The transactions are too large. With 20 or more users the
    intentional locks clashes with the active locks on the same table, this
    causes dead locks or blocks.

    Thing is, in SQL Server even a simple select on a table results in a lock.
    Now I remember why I prefer Oracle all off the sudden. But hey, no
    budget.

    Back to the issue. If there was a way of selecting from a table without
    locking it and without reading dirty data, it will be a great help.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by beltzaser
    nope, VB. The transactions are too large. With 20 or more users the
    intentional locks clashes with the active locks on the same table, this
    causes dead locks or blocks.
    How would this be different in Oracle?

    Damn, now I'm in the unenviable position of having to defend M$ (Old IBM big iron DB2 background)

    How is this SQL Servers fault?

    You seems to already know that very short transaction that are in and out ASAP are the way to go.

    Sounds like the designer should be shot.

    Sorry to hear you inhertied this (I've had quite a few of my own)

    And when you mean "simple sql", you mean SELECT, right?

    A SELECT will only hold the lock if they've opened a record set and grabs lots of data.

    It's the open record set that's the problem I believe...don't quote me...haven't don't front end in a while.

    The way to handle it is to read the entire record set (which is probably more data than they need) in to an array and close the connection...

    But there's always that damn time and money thing...

    Guys, any thoughts?

    Also, if they can't do their work with the existing system, then what's the point (This is a sales point to management)
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    6
    Oracle handles locks in a different way.Better in some regards....

    simply put, no action transaction, no lock, and no uncommitted data
    read unless specified otherwise.

    http://www.schemamania.org/jkl/books...l/2_005_15.htm

    U are right, the designer(s) should be lined up and shot. They knew the
    system should run on SQL Server, and should've designed it like such...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Exactly!

    Great thread

    Releasing locks quickly for applications that support high numbers of users is more important in SQL Server than in Oracle. Releasing locks quickly is usually a matter of keeping transactions short. If possible, a transaction should neither span multiple round-trips to the server nor wait for the user to respond. You also need to code your application to fetch data as quickly as possible because unfetched data scans can hold share locks at the server and thus block updaters.



    But I would shoot any developer who codes long transaction in ANY platform.

    Have you looked at the VB Code?

    I bet it's dynamic SQL, openening a rs connection with SELECT * FROM yourTable...

    And then, the guy/gal goes to lunch...about right?
    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
    Feb 2004
    Location
    UK
    Posts
    6
    worse, stored procs with EXEC and then then they pass the WHERE clause as a parameter, a lot of compiler locks.

    Dunno why they implemented stored procs if they can't use it properly.
    To make things worse, my company bought this product with it's source
    before anyone evaluated it because this people took it to Microsoft for
    their stamp of approval, and then claim it comforms to MS DNS fs
    standards. Makes me sick...

    Thanks for the input anyway, seems a rewrite is the only option...

  10. #10
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Cool

    One way you could get around this is use ADO to connect to the server

    Then use what is called a disconnected recordset.

    http://support.microsoft.com/default...b;EN-US;184397

    Check it out

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No Sh-t...almost looks like a silver bullet...

    aldo..how does it perform?

    how does it work?

    when you re-establish the connection, how does it know what set it belongs?

    Gotta read up on this.

    Thank you
    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
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    been a while since Ive done any ADO but basically it just creates an offline recordset i.e it closes the connection

    you then do your work on the recordset

    once done reestablish the connection and commit your work to the database.

    for developing systems - its the biz

    I've got a book in the house - I'll have a deek over the weekend and get back to you ....

    cheers

  13. #13
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    In terms of performance - OK i think

    Have had a few people say it can cause memory problems

    Depends on the size of your recordset ......

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn....learned something new again...
    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.

  15. #15
    Join Date
    Feb 2004
    Location
    UK
    Posts
    6
    Yes, using the detach method u can use the local recordset and
    manipulate it. However, my problem is with the large selects and
    multiple inserts that happends before the detachment.

    A deadlock occurs when one process locks a page or a table while another process needs it, and the second process has a lock that the first process needs. And the reverse if that make sense.

    To avoid this I need a snapshot of the data without locking the data for a
    simple select. Like I said before Oracle is simple, it doesn't lock unless
    you say so.

Posting Permissions

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