SQL Server Isolation Level issues

Question

I looked at all the isolation types.

But I could not find the mode I wanted.

It can be read by other transaction during the transaction. But, it will not add update and delete data.

For example (pseudo code):

create table abc
(id uniqueidentifier primary key)

Create proc procMain
trans isolation level **??????**
insert abc (id) values (newid())
Waiting 10 minute
commit

Create proc procREAD
select * from abc

Create proc procAdd
insert abc (id) values (newid())

create proc procUpdate
update abc id = newid()

create proc procDelete
delete from abc


now;
exec procMain (abc table access read only and for other access: LOCKED)

(waiting...)

exec procRead (OK) (Readable)
exec procAdd (NO - never) (locked)
exec procUpdate (NO - never) (locked)
exec procDelete (NO - never) (locked)

Thanks...

Is there such an isolation level? (transaction isolation level?)

Answer 1

Sort of. If you set the READ COMMITTED SNAPSHOT database setting then READ COMMITTED sessions will not be blocked by in-flight transactions. But they will see the "last-known-good" version of the rows, ie the state before the current transaction started.

David