CODEWITHSUNDEEP
sql-servert-sqlexception
feO2x
feO2x

Reputation: 5728

Close and Deallocate Cursor and Try-Catch in TSQL

I'm writing a stored procedure in TSQL which uses a transaction and a cursor. I'm wondering if I should place CLOSE and DEALLOCATE within a TRY block - can these statements throw?

My code is structured like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DECLARE myCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT -- Select statement left out for brevity's sake

BEGIN TRY
    OPEN myCursor
    FETCH NEXT FROM myCursor -- INTO statement left out for brevity's sake

    WHILE @@FETCHSTATUS = 0
    BEGIN
        -- Here I process each item in the cursor
        -- and then fetch the items for the next loop run
        FETCH NEXT FROM myCursor
    END;

    CLOSE myCursor;
    DEALLOCATE myCursor;
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    CLOSE myCursor;
    DEALLOCATE myCursor;
    ROLLBACK TRANSACTION;
    THROW;

END CATCH

I have the following specific questions:

Update after Martin Smith's comment:

According to this SO answer, I could declare a cursor variable which is automatically closed and deallocated when the variable goes out of scope. I'm not a 100% sure if this is actually happening: according to the DEALLOCATE docs, DEALLOCATE is called, but in the CLOSE docs, nothing is stated regarding automatic behavior.

Additional questions:

Upvotes: 3

Views: 9873

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

Can CLOSE and DEALLOCATE throw an exception in this specific case?

I don't see how either one could throw an error in any case. They simply release the resources. It would certainly be possible that down-stream operations that are expecting the cursor to exist can error if the cursor has been closed or deallocated (depending on what was expected)

Should I remove the ROLLBACK TRANSACTION statement in the CATCH block? Is the transaction automatically rolled-back when an exception is thrown?

No, don't remove the ROLLBACK. You will need that no matter what, although that is something that is usually wrapped in a test for IF (@@TRANCOUNT > 0).

If XACT_ABORT is OFF (the default) then the transaction could likely still be open so you will need to do the ROLLBACK. If XACT_ABORT is ON, then the transaction is already rolled-back, BUT the transaction is also in an uncommittable state at which point the only thing you can do is issue a ROLLBACK. So, either way, you need the ROLLBACK.

Is there any way to avoid the duplicate CLOSE and DEALLOCATE statements in this piece of code?

No, not really. BUT, you could get away with only having the DEALLOCATE statement as it will close the cursor first. (see example below)

Does using a variable really automatically close and deallocate the cursor?

Not sure about the variable, but I do know that it isn't necessary. All you need is for the cursor to be LOCAL (which you are already doing) and it will automatically deallocate at the end of the batch. (see example below)

If it really works, why call CLOSE at all on a local cursor that is only referenced once?

Probably because most people aren't aware that local cursors are automatically deallocated at the end of the batch (I didn't until I did the test so that I could answer this question), and the default scope for cursors (unless changed at the DB level and I doubt most people mess with that setting, not knowing how it will impact built-in stored procedures) is GLOBAL, so they did need to be deallocate them.

The following tests show the difference between LOCAL and GLOBAL (default on most systems) cursors, and that DEALLOCATE by itself is just fine (assuming you don't need to re-run the cursor query again):

DECLARE myLocalCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT TOP (10) [object_id] FROM sys.objects;

SELECT 'DECLARE (local)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- 1 row
GO

SELECT 'NEXT BATCH (local)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- no rows

DECLARE myGlobalCursor CURSOR GLOBAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT TOP (10) [object_id] FROM sys.objects;

SELECT 'DECLARE (global)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- 1 row
GO

SELECT 'NEXT BATCH (global)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- 1 row (same cursor_id as previous SELECT)

DEALLOCATE myGlobalCursor;

SELECT 'DEALLOCATE (global)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- no rows
GO

And:

-- open cursor then deallocate (do not close first):
DECLARE CursorToDeallocate CURSOR GLOBAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT TOP (10) [object_id] FROM sys.objects;

SELECT 'DECLARE (global2)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- 1 row

OPEN CursorToDeallocate;
SELECT 'OPEN (global2)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- 1 row

DEALLOCATE CursorToDeallocate;

SELECT 'DEALLOCATE (global2)' AS [Step], @@CURSOR_ROWS AS [Rows], *
FROM   sys.dm_exec_cursors(@@SPID);
-- no rows
GO

From your documentation issue:

Do you have to call CLOSE and then DEALLOCATE? Is there an automatic mechanism that does this?

Generally speaking, yes, you CLOSE first which keeps the cursor allocated, and then you can DEALLOCATE. If you call DEALLOCATE first, then the cursor is automatically closed. By merely closing a cursor, it is still defined and can be re-run simply by executing OPEN on it again (no need to re-declare it).

Regarding automatically CLOSE or DEALLOCATE:

  1. A LOCAL cursor is automatically closed and deallocated when the batch ends
  2. Please see SET CURSOR_CLOSE_ON_COMMIT

Can you provide information what DEALLOCATE actually does internally?

As far as I know, DEALLOCATE just de-lists the cursor definition from sys.dm_exec_cursors (well, wherever that data is stored). And, of course, closes the cursor if it's not already closed (at least in the sense that no cursor means that there is no cursor to be either open or closed, or maintain any locks or any resources; to be fair, this merely describes the end-result, not the path that the code took to get there, so I can't say technically if DEALLOCATE actually calls CLOSE or merely duplicates some functionality handled by CLOSE, nor am I sure if that distinction even matters to anyone outside of someone who would be maintaining that code).

I did some testing and found that both DEALLOCATE by itself and CLOSE have the same effect: the "CURSOR"-based locks on the table are removed. I found no evidence of DEALLOCATE by itself leaving any locks in place. I tested with a sensitive / scrollable cursor with SCROLL_LOCKS enabled in a transaction while doing an UPDATE ... WHERE CURRENT OF Cursor. Each FETCH took an IU lock on the page, a U lock on the key, & an IX lock on the table, all with a lock owner type of CURSOR. The UPDATE itself duplicated those 3 locks with TRANSACTION as the owner type (well, minor differences in the Page and Key lock-types). After either DEALLOCATE or CLOSE, only the transaction locks remained.

Upvotes: 7

Related Questions