Cursors and Transactions

Microsoft SQL Server supports setting either session or database options to control whether cursors are closed or left open on commits and rollbacks.

To set "Close on Commit" behavior you can use:

• the session option CURSOR_CLOSE_ON_COMMIT, or

• the database option CURSOR_CLOSE_ON_COMMIT in the ALTER DATABASE statement.

If the option is set that cursors are closed on commits or rollbacks, then all scroll locks are automatically freed when the cursor closes. If the option is set that cursors remain open on a commit, then any active scroll locks are kept until the next fetch or until the cursor closes. All transaction locks, even those on rows in a cursor, are freed when the transaction is committed or rolled back, regardless of whether the cursors stay open. Note that "Close on Commit" behavior does not affect cursor statement running under auto-commit mode.