Nolock VS. Transaction Isolation Level

WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

They are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the connection, so if you only want a nolock on one or two tables use that; otherwise use the other.

Both will give you dirty reads. If you are okay with that, then use them. If you can't have dirty reads, then consider snapshot or serializable hints instead.

What is (are) difference between NOLOCK and UNCOMMITTED

NOLOCK : Is equivalent to READ UNCOMMITTED (source : MSDN)

NOLOCK or READ UNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions

READ UNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READ UNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table

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.

What is the TRANSACTION ISOLATION LEVEL that is equivalent with (NOLOCK) for all select statements?

TRANSACTION ISOLATION LEVEL : READ UNCOMMITTED

Specifies that statements can read rows that have been modified by
other transactions but not yet committed. Transactions running at the
READ UNCOMMITTED level do not issue shared locks to prevent other
transactions from modifying data read by the current transaction. READ
UNCOMMITTED transactions are also not blocked by exclusive locks that
would prevent the current transaction from reading rows that have been
modified but not committed by other transactions. When this option is
set, it is possible to read uncommitted modifications, which are
called dirty reads. Values in the data can be changed and rows can
appear or disappear in the data set before the end of the transaction.
This option has the same effect as setting NOLOCK on all tables in all
SELECT statements in a transaction. This is the least restrictive of
the isolation levels.

Note : This is not a recommended Isolation level as this can allows dirty reads

If you want to set the ISOLOATION LEVEL to the SP alone then try changing the SP

CREATE PROCEDURE PRC_SP AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--your statements 

END

Is it possible to set no lock or TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at database level?

The short answer is "no". The default isolation level in SQL Server is READ COMMITTED, and there is no way to change this to UNCOMMITTED, either globally or per-database. And that's a very good thing too.

WITH (NOLOCK) is a recipe for trouble when it comes to getting accurate results from your database, and in bad cases it can even cause timeouts from queries that run forever due to data getting moved (which NOLOCK cannot protect against). See Is the NOLOCK (Sql Server hint) bad practice? for some more discussion, and some good tips on alternatives.

In particular, many applications that are reader-heavy and want to proceed without blocking can benefit from snapshot isolation. Unlike UNCOMMITTED, you can make snapshot isolation the default with the READ_COMMITTED_SNAPSHOT option. Be sure to read up on the pros and cons of snapshot isolation before you do this -- or better yet, ask your DBA to do this, as any DBA who recommends a global use of WITH (NOLOCK) has some reading up to do. Query hints should be used only as a last resort.

SELECT Statement - NOLOCK with SET TRANSACTION ISOLATION LEVEL READ COMMITTED

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

Why no lock in MySQL for READ COMMITTED

T2 is running a non-locking SELECT statement. It doesn't need to wait for the lock held by T1, because T2 can read the row version that was committed at the time T2 start transaction happened.

If you run a locking SELECT statement, it would need to wait for the lock held by T1.

SELECT title FROM todo WHERE id=1 FOR UPDATE;

Both of the explanations above are true whether you use transaction isolation level READ COMMITTED or REPEATABLE READ.

NOLOCK in Hive and Impala

First of all, let's clarify what is NOLOCK. NOLOCK table hint allows to override default transaction isolation level(READ COMMITTED). NOLOCK = READ UNCOMMITTED transaction isolation level, that allows the query to see the data changes before committing the transaction which is changing the data.

Hive does not support such isolation level (READ UNCOMMITTED) even in transaction mode. Oracle also does not support READ UNCOMMITTED, so no need in NOLOCK hint in these databases.

In Hive you can turn off concurrency.