I have a cursor that reads records from a table. I do not want any changes to the table data to be reflected in my cursor.  My cursor does not do any direct updating. I do use an UPDATE on the base table, but do not update via the cursor. My cursor/sproc/job should be the only proces controlling/updating/inserting records into this table. But it would mess up my process if someone manually inserted records while my process was running. I would read the records out of sequence and it would have bad side effects.

I have read read-only, static, local and am confused about them.

Brenda,

  So, once the transaction(cursor\proc) starts- there should not be any insert or update to table data?

That means you need take an exclusive lock on the table and hold it till your transaction completes.

so, the obvious downside, you are preventing other transactions to access the data(without nolocks)\insert\update and doing the transaction though cursor, makes it worse because, cursor(loops) generally tend to be slow.

also, is it really required not to have any changes(new\update) made to the table while the job is running? 

there are other isolation levels(read committed snapshot,read committed,repeatable read) that provide can you better concurrency and yet always only -committed data. read committed snapshot provides better at this - allows other applications make changes and at the same time lets you read the new committed data without blocking.

but, to acheive what you said(or what i understood) is possible but in most cases, not a good way.

to acheive what you said, you need either - serializable isloation level or take exculsive lock. exuclsive lock, takes complete control on the table where as in serializable, it takes control on the data, so it allows new\update on the data that is outside of the cursor select data set range..

try this example with exculsive lock :

--session 1
Drop table test
go
create table test(sno int primary key,sname varchar(20))
go
insert into test values(1,'stan'),(2,'william')
go

Begin Tran
declare @sno int
declare @sname varchar(20)

DECLARE db_cursor CURSOR FOR  
SELECT sname,sno 
FROM dbo.test  with (Tablockx)
where sno<=2

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @sname,@sno  

WHILE @@FETCH_STATUS = 0   
BEGIN   
  select @sno,@sname 
  waitfor delay '00:00:05'
       FETCH NEXT FROM db_cursor INTO @sname,@sno   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
commit

---Session 2
insert into test values(3,'nathan')

try this example with serializable isolation

Drop table test
go
create table test(sno int primary key,sname varchar(20))
go
insert into test values(1,'stan'),(2,'william')
go
Set Transaction Isolation Level Serializable
Begin Tran
declare @sno int
declare @sname varchar(20)

DECLARE db_cursor CURSOR FOR  
SELECT sname,sno 
FROM dbo.test
where sno<=2

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @sname,@sno  

WHILE @@FETCH_STATUS = 0   
BEGIN   
  select @sno,@sname 
  waitfor delay '00:00:05'
       FETCH NEXT FROM db_cursor INTO @sname,@sno   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
Commit
--session 2. will wait till session 1 is finished
update test set sname='qaz' where sno=2
--session 3 - will insert, since the data range falls outside the cursoe data set.
insert into test values(3,'nathan')

I will look at your example tomorrow. But I thought that I could add a clause or clauses to the Cursor that will take a copy of the records at that point in time (like a temp table) and only read from the copy. So that any newly added records would not get picked up by the cursor. That is what I was looking for, but maybe did no clearly express.

I will look at your example tomorrow. But I thought that I could add a clause or clauses to the Cursor that will take a copy of the records at that point in time (like a temp table) and only read from the copy. So that any newly added records would not get picked up by the cursor. That is what I was looking for, but maybe did no clearly express.

in that case, you need to get the data first into a intermediate table and have the cursor read from that temp table

cursor reads the data row by row , so, if you could also use snapshot isolation level where you can all  read the but the rows could be added\updated by other processes but you get the 'snapshot data' that is present when your transaction began. but this will fail if you update the data using cursor that was updated outside of the cursor transaction. so, if your cursor transaction does not update but only reads the data, you can use snapshot isolation. this requires setting change on the database.

so, if your cursor really just reads but does not update. your can use snapshot isolation. this causes less 'damage' to other users. you need enabled snapshot isolation on the database first.

try this .

Drop table test
go
create table test(sno int primary key,sname varchar(20))
go
insert into test values(1,'stan'),(2,'william')
go

Set Transaction Isolation Level Snapshot
Begin Tran
declare @sno int
declare @sname varchar(20)

DECLARE db_cursor CURSOR FOR  
SELECT sname,sno 
FROM dbo.test
where sno<=2

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @sname,@sno  

WHILE @@FETCH_STATUS = 0   
BEGIN   
  select @sno,@sname 
  waitfor delay '00:00:05'
       FETCH NEXT FROM db_cursor INTO @sname,@sno   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
Commit
--session 2. will succeed
update test set sname='qaz' where sno=2
--session 3 - will insert succeed
insert into test values(3,'nathan')