SQL Cursor basics

use pubs
go

BEGIN TRAN
DECLARE my_curs SCROLL CURSOR FOR SELECT au_id, au_lname
FROM authors (UPDLOCK)
OPEN my_curs
FETCH ABSOLUTE 6 FROM my_curs
UPDATE authors SET au_lname='Kronenthal' WHERE CURRENT OF my_curs
COMMIT TRAN
CLOSE my_curs
DEALLOCATE my_curs
go
--------------------------------------------------


DECLARE my_curs INSENSITIVE CURSOR FOR SELECT au_id,au_lname FROM authors 
OPEN my_curs
SELECT @@CURSOR_ROWS
-- FETCH WILL FAIL - not a scrollable cursor so ABSOLUTE cannot be used.
FETCH ABSOLUTE 6 FROM my_curs
-- UPDATE HERE FAILS BECAUSE IT'S INSENSITIVE - i.e. READ ONLY
UPDATE authors SET au_lname='Donaldson' WHERE CURRENT OF my_curs
DEALLOCATE my_curs
go
-----------------------------------------------------------------------
DECLARE my_curs CURSOR SCROLL SCROLL_LOCKS FOR SELECT au_id, au_lname
    FROM authors
OPEN my_curs
FETCH ABSOLUTE 12 FROM my_curs
-- Pause to create window for other connection.
-- From other connection, try to update the row. E.g.,
-- UPDATE authors SET au_lname='Newest val' WHERE au_id='486-29-1786'.
-- The update will be blocked by a KEY UPDATE lock until this cursor is 
-- closed. Refetch the row next and see that it has not changed.
-- The CLOSE cursor will release the SH_PAGE lock.
 
FETCH RELATIVE 0 FROM my_curs
-- RELATIVE 0 = "freshen" current row
CLOSE my_curs
DEALLOCATE my_curs

/*
-- From other connection do this:
UPDATE authors
SET au_lname='Newest val'
WHERE au_id='341-22-1782'
*/
-------------------------------------------------------------
-- Be sure the default setting is in effect
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE my_curs CURSOR SCROLL FOR SELECT au_id, au_lname
    FROM authors
OPEN my_curs
BEGIN TRAN

FETCH FIRST FROM my_curs
IF (@@FETCH_STATUS <> 0) -- If not a valid fetch, get out
    PRINT 'Fetch is not valid'
-- Verify from another connection 
-- that no lock held using sp_lock
 
UPDATE authors SET au_lname='Row1 Name' WHERE CURRENT OF my_curs

IF (@@ERROR <> 0)
    PRINT 'Update not valid'

FETCH LAST FROM my_curs

IF (@@FETCH_STATUS <> 0) -- If not a valid fetch, discontinue
    PRINT 'Fetch not valid'

-- Verify from another connection
-- and see the exclusive KEY lock from previous update
 
UPDATE authors SET au_lname='LastRow Name' WHERE CURRENT OF my_curs
IF (@@ERROR <> 0)
    PRINT 'Update not valid'
ROLLBACK TRANCLOSE my_curs
DEALLOCATE my_curs

----------------------------------------

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
DECLARE my_curs CURSOR SCROLL FOR SELECT au_id, au_lname
    FROM authors
OPEN my_curs
BEGIN TRAN

FETCH FIRST FROM my_curs
IF (@@FETCH_STATUS <> 0) -- If not a valid fetch, discontinue
    PRINT 'Fetch not valid'
-- Check locks from another connection using sp_lock
 
UPDATE authors SET au_lname='Newer Row1 Name'
    WHERE CURRENT OF my_curs

IF (@@ERROR <> 0)
    PRINT 'Update not valid'

FETCH LAST FROM my_curs

IF (@@FETCH_STATUS <> 0) -- If not a valid fetch, discontinue
    PRINT 'Fetch not valid'

-- Verify from another connection
-- and see the exclusive KEY lock from previous update, and
-- shared KEY from most recent FETCH
 

UPDATE authors SET au_lname='Newer LastRow Name'
    WHERE CURRENT OF my_curs
IF (@@ERROR <> 0)
    PRINT 'Update not valid'

ROLLBACK TRAN

CLOSE my_curs
DEALLOCATE my_curs

-------------------------------------------------------------------

-- We will use SCROLL_LOCKS, which are held until the 
-- next row is fetched or until they convert into EXCLUSIVE locks.
-- So we do not require REPEATABLE READ. We can use the default isolation 
-- level of READ COMMITTED.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE my_curs CURSOR SCROLL SCROLL_LOCKS FOR SELECT au_id, au_lname
    FROM authors 
OPEN my_curs
BEGIN TRAN

FETCH FIRST FROM my_curs
IF (@@FETCH_STATUS <> 0) -- If not a valid fetch, get out
    GOTO OnError
-- Delay for 10 secs to increase chances of deadlocks
WAITFOR DELAY '00:00:10' 

UPDATE authors SET au_lname='Newer Row1 Name'
    WHERE CURRENT OF my_curs
 
IF (@@ERROR <> 0)
    GOTO OnError

FETCH LAST FROM my_curs

IF (@@FETCH_STATUS <> 0) -- If not a valid fetch, get out
    GOTO OnError

-- Delay for 10 secs to increase chances of deadlocks
WAITFOR DELAY '00:00:10' 

UPDATE authors SET au_lname='Newer LastRow Name'
    WHERE CURRENT OF my_curs
IF (@@ERROR <> 0)
    GOTO OnError

COMMIT TRAN
IF (@@ERROR=0)
    PRINT 'Committed Transaction'
GOTO Done

OnError:
PRINT 'Rolling Back Transaction'
ROLLBACK TRAN

Done:
CLOSE my_curs
DEALLOCATE my_curs