Cursors and Transactions
Microsoft® SQL Server™ 2000 supports setting either connection or database options to control whether cursors are closed or left open on commits and rollbacks.
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.