How to eliminate this cursor (causing a deadlock)

Question

We have got a stored procedure. Part of it is executing a cursor over a select statement that involves 6 different tables with inner joins.

In the cursor body we execute 3 other stored procedures with the parameters from the cursor.

Is there any way to get rid of a cursor in this situation?

Thanks all!

  DECLARE myCursor CURSOR FOR
              SELECT x,y,z
              FROM   a
                     INNER JOIN .....
                     INNER JOIN .....
                     INNER JOIN .....
                     INNER JOIN .....
                     INNER JOIN .....

            OPEN myCursor

            FETCH NEXT ...

            WHILE @@FETCH_STATUS = 0
              BEGIN

                    EXECUTE xy @cursor_variable

                    EXECUTE yz @cursor_variable

                    EXECUTE abc @cursor_variable

                  FETCH NEXT FROM myCursor INTO ...
              END

            CLOSE myCursor

            DEALLOCATE myCursor
        END

Answer 1

The cursor I use on some of my data which helps to prevent deadlocks is:

declare cursor YOURCURSORNAME cursor local static read_only forward_only for  

But this is not always effective at preventing dead locks as tables can still be locked when you query them, especially when simultaneous cursors query the same tables are used.

What @JeffB recommends is to copy your data to a temp table which will help.

Alternatively, you can perform dirty reads, if your scenario allows you to do so by making use of (nolock) on each table you query, or by setting the following flag at the beginning of your query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Answer 2

Try a static cursor, that won't hold on to locks.

Answer 3

Something you might want to do is select your joins into a temp table and then use that in the cursor. At least then the base tables will be free.