Sql – SELECT Statement – NOLOCK with SET TRANSACTION ISOLATION LEVEL READ COMMITTED

isolation-levelsqlsql serversql-server-2005tsql

My understanding is that when NOLOCK is used in SELECT statement, it could read uncommitted / dirty rows as well. But I wanted to take advantage of NOLOCK hint on table so that my SELECT query will run fast.

Now, does NOLOCK on table but along with "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" give me NOLOCK advantage and faster SELECT query (because of NOLOCK) with only committed rows (because of SET)?

Best Answer

yes a table hint overrides the isolation level setting, so you will still get dirty reads

easy to test

first run this

create table bla(id int)
insert bla values(1)



begin tran
select * from bla with (updlock, holdlock)

make sure not commit the tran!! open another window and run this

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from bla -- with (nolock)

you don't get anything back.

open another window and run this now

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from bla with (nolock)

as you can see you will get back the row

BTW, READ COMMITTED is the default isolation level, no need to set it

Take a look at Snapshot Isolation which won't give you back dirty data but still won't lock

Related Solutions

Sql – Transaction with Read Committed Isolation Level and Table Constraints

Check constraints are not enforced for delete operations, see http://msdn.microsoft.com/en-us/library/ms188258.aspx

CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results.

Edit - to answer your question on workaround, you can use a delete trigger to roll back if your function call shows an invariant is broken.

Edit #2 - @reticent, if you are adding rows then the function called by the check constraint should in fact see the rows. If it didn't, check constraints would be useless. Here is a simple example, you will find that the first 2 inserts succeed and the third fails as expected:

create table t1 (id int)
go
create function t1_validateSingleton () 
returns bit
as
begin
declare @ret bit
set @ret = 1
if exists (
    select count(*)
    from t1
    group by id
    having count(*) > 1
)
begin
    set @ret = 0
end
return (@ret)
end
go
alter table t1
add constraint t1_singleton
    check (dbo.t1_validateSingleton()=1)
go
insert t1 values (1)
insert t1 values (2)
insert t1 values (1)

Sql – NOLOCK vs. Transaction Isolation Level

They're the same thing, just scoped differently. NOLOCK is placed on a per table basis and SET Transaction... can be placed as a block.

Best Answer

Related Solutions

Sql – Transaction with Read Committed Isolation Level and Table Constraints

Sql – NOLOCK vs. Transaction Isolation Level

Related Topic