Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    4

    Unanswered: table locking during searches

    I manage a SQL server database for a manufacturing site. We collect data on all the units that we produce at our facility. Because we have a high volume manufacturing business, the SQL server 2000 database we use to collect the data is quite large. Our database is over 36Gb and one of the tables has over 143 million records in it.

    The issue I have is we use the same server for queries as we do for live production equipment. As a result when someone is using the website to search through the data, it sometimes affects the production lines, which is not a good thing!

    We heavily index our tables to suit the queries we run, but sometimes we have users that need to run queries that cover production data that covers a long period of time. Sometimes when this happens, the query optimizer will lock the table it is searching while it performs the query. when this happens, all inserts from the production floor are blocked by the query and everything just stops!

    I have tried running the queries with the nolock option, but it still locks the table!

    I am using ADO and the recordset object from ASP on the website for the queries that are causing the problems.

    Basically, the queries do not have to be critical. It is much more important to insert the data from the floor than it is to not miss a few records that the production line is processing.

    Is there any way that we can turn off the table locking features during the queries? This is becoming a real issue and is causing significant downtime!

    I know there used to be a lock escalation threashold that was configurable for SQL 7.0 but it appears that this has been removed.

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    We run a high volume database here, and have found that putting the (nolock) after each table listed in any from clause in a select statement fixed our problem.
    Another problem we found was that if someone has Enterprise Manager open with the results from a table up, it can cause a table page or row level lock as well.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Post

    One may accomplish the same effect (as inserting NoLock hints for all tables in select statements) within a given transaction via:

    Set Isolation level READ UNCOMMITTED

    In either case, the effect is the same i.e.(values in returned data sets may change, and additional rows may 'appear' while others 'disappear' in the returned data set before the end of the transaction).

    Some other set options that may be helpful:

    SET DEADLOCK_PRIORITY LOW

    SET LOCK_TIMEOUT 2000

    Some other Locking Hints that may be helpful:

    READPAST (ignore any rowlock level locked data)

    ROWLOCK (only apply rowlock level locks)

  4. #4
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    4
    I have tried Setting the Isolation level to READ UNCOMMITTED and using the nolock hint and it still seems to like to lock the table during queries.

    the other options DEADLOCK_PRIORITY LOW and SET LOCK_TIMEOUT seem to have been removed for SQL server 2000...

    I am worried that if I use the ROWLOCK hint then I will hit a memory wall as it tries to rowlock millions of records. Does anyone know if this is really the case?

  5. #5
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    4
    Here is the VBscript code I am using to perform the search. As you can see I am already using the nolock hint and setting the appropriate isolation level. For the life of me I cant figure out why it still insists on setting any locks at all for the search!

    sqltext = "SELECT audio_tester.measured_left, audio_tester.measured_right " & _
    " FROM serial_numbers WITH (NOLOCK) " & _
    " RIGHT JOIN audio_tester ON serial_numbers.ID = audio_tester.serial_num " & _
    " WHERE (serial_numbers.date_time > '" & request("month") & "/" & request("day") & "/" & request("year") & " " & request("hour") & "') and (serial_numbers.date_time < '" & request("month_end") & "/" & request("day_end") & "/" & request("year_end") & " " & request("hour_end") & "') " & _
    " and (serial_numbers.test_type = 1) and audio_tester.test_name = '" & request("test") & "' " & _
    " and (serial_numbers.serial_num like 'M" & request("model") & "%') " & _
    " and (serial_numbers.node_name like '" & request("station") & "%') "

    set conntemp=server.createobject("adodb.connection")
    conntemp.open application("CData_ConnectionString")
    conntemp.IsolationLevel = 256
    conntemp.CommandTimeout = 120
    Server.ScriptTimeout = 600
    set rs=conntemp.execute(sqltext)

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    What about
    RIGHT JOIN audio_tester (nolock)

  7. #7
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    4
    that did it! you guys are a lifesaver!

    I thought that the hint was for the WHOLE query.. not just one part....

Posting Permissions

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