Post reply

Lock

  • June 25, 2008 at 7:59 am

    #94298

    Paralley executing insert and select statements for a table, the table is locked by insert statement. At the same time my Select statement is blocked

    How prevent this (only in select statement)

  • June 25, 2008 at 8:02 am

    #833993

    is this a homework or interview question? 🙂

    ---------------------------------------
    elsasoft.org

  • June 25, 2008 at 8:12 am

    #834008

    I facing the problem in my production db??

  • June 25, 2008 at 8:26 am

    #834026

    you can select from the table while the update has an exclusive lock only if you do a dirty read. This is done with the NOLOCK keyword. Have a look in BOL at NOLOCK.

    select a,b,c from YourTable (NOLOCK)

    note that you will be reading uncommitted data if you do this though. it's not always appropriate - depends on the business. for financial apps you definitely don't want to use NOLOCK 🙂

    ---------------------------------------
    elsasoft.org

  • June 25, 2008 at 8:34 am

    #834036

    Is the table locked, a page locked, or a record locked? preventing a "dirty read" is what the database is supposed to do. While you are inserting, other users cannot read the records you are inserting until they have been committed. If you did not have this behavior, you would see inconsistent data.

    Others may suggest you use NOLOCK or READ UNCOMMITTED, but these are dangerous solutions that can cause you to miss records that are in the table, read the same record multiple times, or simply read data that will be rolled back.

    The first real step for you is to minimize the conflict. INSERTS, UPDATES, and DELETES can take row, page, or table level locks and lock escalation to a page or a table will impact the number of records other cannot read while the operation is happening. So, you want to minimize the numbers of records being locked during your operations by ensuring you have not over-indexed a table, you are not unnecessarily updating records (say to the original value again), and you are not leaving transactions open for large number of records unnecessarily. In addition, you want to minimize the conflict on the SELECT side and only select columns you need (possibly making an index satisfy your statements) and only select rows you need rather than filtering them out at the client application.

    In addition to these, look into Snapshot Isloation. This was added in SQL 2005 and can elminate lock conflicts entirely (at what can be a pretty high cost though).

    Usually, blocking issues are a result of a poorly written application that does not understand the SQL locking mechanism. Make sure you understand it well before taking action.

  • June 25, 2008 at 8:35 am

    #834038

    ...and as I was writing, up popped that NOLOCK suggestion.

    Make sure you really understand this option before using it. I have rarely seen it used correctly.

  • June 25, 2008 at 9:16 am

    #834083

    I would agree with the comment about NOLOCK,

    only use this if you are absolutely sure what you are doing esp in a production db.

    Have a look at your code and what is going on in the db to cause the lock and try and rewrite your code to be more efficent.

  • June 25, 2008 at 11:40 pm

    #834409

    besides returning data that is bogus, reading NOLOCK can also simply error out. this happens if the process that has the exclusive lock moves the row on a page while you are reading from that page.

    more here: http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx

    ---------------------------------------
    elsasoft.org

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply