事务ROLLBACK关闭游标CURSOR问题原因及解决方案

      大部分数据库,包括MS SqlServer, DB2, Oracle的事务都会影响游标.最常见的错误就是在游标中嵌入了一组事务,这时会导致数据库报错.
其原因是:
      数据库程序中很重要的一点就是事务处理(transaction或者the unit of work(UOW))。事务当中的任何一部分失败，整个事物就会失败。利用COMMIT和ROLLBACK进行适当的事务控制对于保证数据完整性来说是至关重要的。
      当在使用游标的时候使用COMMIT或者ROLLBACK语句时，游标的行动取决于是否在生命的时候加了WITH HOLD子句。如果一个游标在声明的时候没有指定WITH HOLD，那么它的所有资源（游标，锁，大对象数据类型或者LOB locators）都将在COMMIT或者ROLLBACK之后被释放。因此，如果需要在完成一个事务之后使用游标，就必须重新打开游标，并从第一行开始执行。如果定义了一个游标WITH HOLD，游标就会在事务之间保存它的位置和锁(lock)。需要明白的是，只有保证游标位置的锁被held了。
      锁(lock)是个数据库对象(a database object)，我们用它来控制多个应用访问同一个资源的方式。而一个LOB locator使存储在本地变量中的4字节的值，程序可以用它来查到数据库系统中的LOB对象的值

A：定义了WITH HOLD的游标在COMMIT之后
       1.仍然保证是打开(open)的
       2.游标指向下一个满足条件的行之前
       3.在COMMIT语句之后只允许FETCH和CLOSE
       4.Positioned delete和positioned update只在同一事务中fetch的行上可用
       5.所有的LOB locators会被释放
       6.除了保存声明为WITH HOLD的游标位置的锁，其他锁都会释放
       7.当执行了数据修改语句或者含有WITH HOLD游标的修改语句被commit的时候
B：所有定义为WITH HOLD的游标在ROLLBACK之后：
       1.所有游标会被关闭
       2.所有在该事务中的锁会被释放
       3.所有的LOB locators会被freed

无论是否使用with hold与否，rollback将释放session中的游标。commit只释放不带with hold的游标。
以SqlServer为例:
当 SET CURSOR_CLOSE_ON_COMMIT 为 ON 时，该设置遵从 SQL-92 标准，在提交或回滚时关闭任何打开的游标。
当 SET CURSOR_CLOSE_ON_COMMIT 为 OFF 时，提交事务时不关闭游标.
不论CURSOR_CLOSE_ON_COMMIT如何设置, ROLLBACK时都是关闭游标,释放资源

解决方案:
可以利用save transaction的方法来控制ROLLBACK提交的空间.

 

DROP PROCEDURE TEST
GO
CREATE PROCEDURE TEST
AS 
BEGIN
	DECLARE @column NVARCHAR(20)

	DECLARE #T CURSOR FOR
	SELECT 'DOG' AS COLUMN1
	UNION ALL
	SELECT 'CAT'
	
	OPEN #T
	FETCH #T INTO @column
	
	BEGIN TRANSACTION

	WHILE(@@FETCH_STATUS = 0) 
	BEGIN
		SAVE TRANSACTION tran_save
		INSERT INTO TEST_TABLE (COLUMN1)
		SELECT @column + 'AA'

		IF @column = 'CAT'
		BEGIN
			PRINT 'COMMIT SAVE ' + @column + 'AA'		
		END
		ELSE
		BEGIN
			PRINT 'ROLLBACK SAVE ' + @column + 'AA'
			ROLLBACK TRANSACTION tran_save
		END

		FETCH #T INTO @column
	END
	CLOSE #T
	DEALLOCATE #T
	
	COMMIT
		
END

这样即可解决问题