FIX: BEGIN TRAN After OPEN CURSOR May Not Commit |
Q138749
The information in this article applies to:
- Microsoft SQL Server version 6.0
BUG# NT: 11659 (6.00)
SYMPTOMS
When using Transact-SQL cursors, if BEGIN TRAN is issued after OPEN CURSOR, and then the cursor is closed, there can be an open transaction that cannot be removed with COMMIT TRAN or ROLLBACK TRAN.
WORKAROUND
Call BEGIN TRAN before you call OPEN CURSOR.
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.
MORE INFORMATION
The following script demonstrates the problem:
set nocount on go print "This works correctly - open cursor after begin tran" print "===================================================" go drop table A go create table A (col1 int primary key, col2 char(128),col3 int) go insert into A values (1,'d1',1) insert into A values (2,'d2',2) insert into A values (3,'d3',3) insert into A values (4,'d4',4) go select 'Values before opening cursor' select col1, col3 from A go declare c cursor for select * from a for update of col3 begin transaction open c fetch c update a set col3=9 where current of c close C select 'Values after closing cursor but before ROLLBACK TRAN' select col1, col3 from A rollback transaction select 'Values after closing cursor and after ROLLBACK TRAN' select col1, col3 from A deallocate c go print "This works incorrectly - open cursor before begin tran" print "===================================================" go drop table A go create table A (col1 int primary key, col2 char(128),col3 int) go insert into A values (1,'d1',1) insert into A values (2,'d2',2) insert into A values (3,'d3',3) insert into A values (4,'d4',4) go select 'Values before opening cursor' select col1, col3 from A go declare c cursor for select * from a for update of col3 open c begin transaction fetch c update a set col3=9 where current of c close C select 'Values after closing cursor but before ROLLBACK TRAN' select col1, col3 from A rollback transaction select 'Values after closing cursor and after ROLLBACK TRAN' select col1, col3 from A deallocate c go print 'Now ROLLBACK TRAN has no effect on @@trancount' go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go
Additional query words: SQL6 CURSOR TRANSACTION
Keywords : kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600
Last Reviewed: March 25, 2000 |