FAST_FORWARD cursor blocking issue

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    I have an application using a fast_forward cursor to loop through data in a table and perform other tasks in each cursor iteration.

    The cursor is defined as follows:

    DECLARE invoice_cursor CURSOR FAST_FORWARD FOR

    ...

    SELECT ...

    FROM [invoice]

    ...

    ...

    That statement is blocking inserts into the invoice table.

    Would the blocking be alleviated if I placed a NOLOCK hint in the SELECT statement above?

    Or would it be better if I used a STATIC cursor instead to alleviate the blocking?

    And one last question: are shared locks acquired on the underlying tables when a fast_forward cursor is first opened?

    I would think yes, but I would like to confirm (for my sanity's sake 🙂 ).

    (I already know that shared locks are obtained on each row as it is fetched).

    And, yes, I agree that cursors are "bad" and all that, but this is not my application. 🙂

    I'm just trying to manage the blocking without major code rewrites.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • GSquared

    SSC Guru

    Points: 260824

    If you have to use a cursor for this, make it STATIC instead of FAST_FORWARD. It'll probably be faster that way, and it will definitely release locks faster.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    GSquared (11/3/2011)


    If you have to use a cursor for this, make it STATIC instead of FAST_FORWARD. It'll probably be faster that way, and it will definitely release locks faster.

    Thank you.

    Won't STATIC take a longer time to open since it needs to load the whole resultset on tempdb?

    During that time it will block writers to the underlying tables.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • GSquared

    SSC Guru

    Points: 260824

    Try setting the Transaction Isolation Level to Snapshot (if the database will allow that). That will help with blocking during that time.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • r.dragoi-1095738

    SSCommitted

    Points: 1984

    I would use the hint first (nolock) .. if that still doesn't work I would select the rows first into a variable table and after that I would use the cursor (over the variable table) , I assume that you don't update anything while you are using the cursor -- since you are using FAST_FORWARD cursor.

  • GSquared

    SSC Guru

    Points: 260824

    r.dragoi-1095738 (11/4/2011)


    I would use the hint first (nolock) .. if that still doesn't work I would select the rows first into a variable table and after that I would use the cursor (over the variable table) , I assume that you don't update anything while you are using the cursor -- since you are using FAST_FORWARD cursor.

    If dirty reads are acceptable, then NoLock, or Read Uncommitted, are acceptable. If the data actually matters to anyone anywhere at all, then don't use those.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    r.dragoi-1095738 (11/4/2011)


    I would use the hint first (nolock) .. if that still doesn't work I would select the rows first into a variable table and after that I would use the cursor (over the variable table) , I assume that you don't update anything while you are using the cursor -- since you are using FAST_FORWARD cursor.

    Thank you. Storing the data in a table variable would ensure no row locks are taken on the underlying data while a row is fetched

    However, inserting the data into the table variable would still place shared locks until the base SELECT statement is completed (unless NOLOCK is used).

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    r.dragoi-1095738 (11/4/2011)


    I would select the rows first into a variable table and after that I would use the cursor (over the variable table)

    I would advise that as well.

    I may be talking nonsense but in my experience, when you isolate just the data you want (being in a table variable or temp table), the cursor will behave better and you'd not have to worry about locks.

    That works great when the data you're working on is not huge.

    That is, if you really need a cursor... 😀

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    codebyo (11/4/2011)


    r.dragoi-1095738 (11/4/2011)


    I would select the rows first into a variable table and after that I would use the cursor (over the variable table)

    I would advise that as well.

    I may be talking nonsense but in my experience, when you isolate just the data you want (being in a table variable or temp table), the cursor will behave better and you'd not have to worry about locks.

    That works great when the data you're working on is not huge.

    That is, if you really need a cursor... 😀

    Best regards,

    I agree that by storing the result set in a table variable or temp table you avoid the shared row locks on the rows being fetched.

    However, you still cannot avoid the locks taken up when the cursor is opened, ie when the cursor SELECT is executed, unless you run that SELECT in some form of READ-UNCOMMITTED isolation mode.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • GSquared

    SSC Guru

    Points: 260824

    You guys do realize that selecting into a table variable, and then running a cursor on the table variable, is pretty much what a Static cursor does all by itself, right?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    GSquared (11/8/2011)


    You guys do realize that selecting into a table variable, and then running a cursor on the table variable, is pretty much what a Static cursor does all by itself, right?

    Hmm... I was under the impression that selecting the data first would be faster in large tables.

    I remember having changed a code that way in the past and it became a faster query but maybe it was coincidential and it was because a different factor.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Back to step 0.

    DELETE the cursor and rewrite to set based.

    Post the code if you need help doing that.

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Ninja's_RGR'us (11/8/2011)


    Back to step 0.

    DELETE the cursor and rewrite to set based.

    Post the code if you need help doing that.

    😀 Of course, my friend. That will be arranged one day.

    The problem is we're short on personal and we don't have time to change much code right now. But I'm changing some code bit by bit but we're in a situation where almost all of our stored procedures (if not all of them) have one or more cursors and are very expensive on the resources side.

    Thanks for the advice and I'll probably have to get some help here when we're ready for that big step of destroying our highly RBAR'd code.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

Viewing 13 posts - 1 through 13 (of 13 total)

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