Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Locks

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Locks

    Hello There !!

    I have a very big problem, with SQL SERVER 2000. I want to know about the locks with select.

    When I execute a Select (so big), and I try to update or Insert into one of the tables that I invoke in the select, I get locked.

    Is there in SQL Server, something like a Select for update, that could be causing the problem ???

    Is there any way to select rows from a table without locking it ?

    I really have a big problem with this, and I don't know so much about sql server !

    Thank you so much !!!

  2. #2
    Join Date
    Feb 2004
    Posts
    6
    SELECT * FROM TABLE (NOLOCK)

    Keep in mind that this will read data that has not been committed.

  3. #3
    Join Date
    Jun 2003
    Posts
    294
    mmmmm !! It seems so good, but there's something I don't understand ..

    Does a Select lock a table by default ?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Any query that locks a resource (i.e. table) will try to lock just the rows it wants. If this amounts to a significant enough portion of the table, then the whole table gets locked. Check to make sure your large select query is making use of indexes by looking over the execution plan in Query Analyzer.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, but they're doing SELECT * FROM Table...

    No predicates...but what's the interface...if you do select * from vb and open the recordset, doesn't that escalate to a table lock?
    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.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I know recordsets can be opened with a "lock mode". Either optimistic, or pessimistic. I forget how the two of those affect the database. Pessimistic, may lock the table, and optimistic may just keep a local copy of the recordset. Either way, you are right, Brett. No where clause, or a vague where clause will equate to a table lock.

  7. #7
    Join Date
    Jun 2003
    Posts
    294
    Hey I have been reading about that, and I found that when you select a table, it get locked in shared mode. It means you can't insert, update or delete any record during the query,
    how do I change this ??? Is there any way to make a transaction during the query time ??

    Thanks !

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As was mentioned above, the select query could be run with the (nolock) hint, but that is dangerous. You may want to look over the design of the system, especially this large select. Is this for a report? If so, then you may want to offload reporting to a standby server that is updated by either replication or logshipping.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Everything should be quick..

    opne the rs

    acess data with a very strict set of criteria or cakk a sproc

    Save it to an array in the code

    close the rs

    process the data

    execute any dml using keyed and indexed columns

    close the connection

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

  10. #10
    Join Date
    Jun 2003
    Posts
    294
    Well, but in have another problem with the concurrency, when I execute the select many times (pushing F5 in the web application) and at the same time I try to update a record of the table, I get everything locked (as if I had the table locked) even if the query is so small, I can wait for a long time and I cotinue stuck.

    Does sql server unlock the table automatically when a query is finished ?
    Is there any way to force the unlock ?

    I'm really confused with SQL Server, cuase I always have worked with Oracle !

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oracle and MS-SQL take opposite approaches on locking. Oracle uses the methodology that readers can only block readers and writers can only block writers. Read and write operations never block each other in Oracle. If you need to ensure that it is safe to read (that the read will be repeatable or serializable), you are forced to explicitly state that in your code if you are using Oracle.

    MS-SQL uses the methodology that locking/blocking should be symetric. If you attempt a read that is potentially dangerous, you should acquire the appropriate locks before you do it. If you attempt any change to the database (insert/delete/update), you should acquire the appropriate locks before you do that. If a lock that you want requires you to wait for a resource, the server enforces that wait unless you explicitly choose to do without that service.

    The Oracle method is simpler, in that most users never need to worry about locking to do simple tasks. The MS-SQL method is much safer, in that you don't need to worry about whether it is safe to do something, since the server won't accidentally allow you to hurt yourself (and get an incorrect answer).

    You can make an argument for either method. I prefer the server to protect me unless I explicitly choose to do something that might be dangerous... Then I know when I'm unprotected and that I need to pay careful attention, instead of always having to think about it.

    -PatP

  12. #12
    Join Date
    Jun 2003
    Posts
    294
    Well, you said "...unless you explicitly choose to do without that service."

    I want to know ho to do it !

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you want to confine the risk to a single table in an SQL statement, use the (NOLOCK) hint. This is VERY DANGEROUS, but sometimes justifiable.

    If you want to "swim with the sharks" (not anything I'd recommend), then use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is a lot like cleaning outhouses with gasoline... It makes things simple at first because it disolves anything and disinfects whatever it touches, but it makes one heck of a mess when (that's when, not if) it blows up on you!

    -PatP

  14. #14
    Join Date
    Jun 2003
    Posts
    294
    is it possible to configure the dbms, for working in serializable mode by default ?

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure, if you want REAL problems

    Look up ISOLATION LEVEL in bol...

    and if you don't know what bol means, you shouldn't be playing in that arena...

    Your process model sounds flawed...it's not sql server that's the problem here...
    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
  •