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?)

There is no isolation level which forbids you to update or delete data. It's not what are isolation levels supposed to do. — TcKs, Jun 12 '17 at 17:53
Actually I want to select serialize level. But the whole table is locked. Is there a row-based serialize isolation level? — Nazlı, Jun 12 '17 at 18:16

score 1 · Accepted Answer · answered Jun 12 '17 at 17:56

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

answered Jun 12 '17 at 17:56

David Browne - Microsoft

80,331
6
39
67

I think, this is most sane variant. – TcKs Jun 12 '17 at 19:57

score 0 · Answer 2 · answered Jun 12 '17 at 17:50

0

You're looking for READ UNCOMMITTED. Learn more here. Pay mind that this will lead to dirty reads.

answered Jun 12 '17 at 17:50

PicoDeGallo

608
9
19

SQL Server Isolation Level issues

2 Answers2