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
