SQL Server Performance

Cursor and WITH (NOLOCK)

Discussion in 'General Developer Questions' started by frettmaestro, Jan 12, 2004.

  1. frettmaestro New Member

    I have recently had some deadlocking-problems in my database and I have come to belive that it is because of a cursor that is open a bit long. I really do need the cursor but the data in it doesn't change at all so I've now declared the cursor like this:

    SET @Cur2 = CURSOR FORWARD_ONLY FOR (SELECT ID FROM table WITH (NOLOCK) WHERE DescID = @DescID)

    Will this prevent *all* locking issues regarding this cursor? Can I now create two cursors reading the same data at the same time which normally would create a deadlock?

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  2. satya Moderator

    The transaction locks acquired by any SELECT statement, including the SELECT statement in a cursor definition, are controlled by:

    -The transaction isolation level setting for the connection.
    -Any locking hints specified in the FROM clause.

    What is the reason behind creating 2 different cursor to read same data?
    Cursors also support their own concurrency specifications, some of which generate additional locks on the rows in each fetch. These scroll locks are held until the next fetch operation or until the cursor is closed, whichever comes first. If the connection option to keep cursors open on a commit is set on, these locks will be kept across a commit or rollback operation.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Twan New Member


    Two select cursors should never be able to cause a deadlock... you may want to add FOR READONLY to the end of the cursor declaration (not sure if it makes a difference)

    easiest would be to run the cursor declaration, fetching and closing whilst having SQL Profiler trace for Lock activity (you might want to limit it to just your SPID to avoid swamping your Profiler session and the server)

    Cheers
    Twan
  4. frettmaestro New Member

    I found out that it probably isn't the cursor after all, but a much bigger and uglier part of the procedure. So much for easy solutions...but thanx for the enlightment anyway fellas <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"

Share This Page