CODEWITHSUNDEEP
sql-server
Ricky
Ricky

Reputation: 35833

SQL Server NOLOCK keyword

When a SQL client issues the following command:

select * into tbl2
FROM tbl1 (nolock)
WHERE DateCreated < '2009/01/01'

does it mean that the command won't lock tbl1 or it won't be blocked by other uncommitted transactions made to tbl1?

Update:

[NOLOCK]: 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. REF: MSDN

Upvotes: 1

Views: 3034

Answers (2)

martin clayton
martin clayton

Reputation: 78105

It means the first; you're not taking out any locks and therefore the second; you wont be blocked by other open transactions. See MSDN docs on table hints.

Here's a link to the MSDN docs on transaction isolation levels - might be useful if you're considering using NOLOCK. NOLOCK puts the SQL statement at isolation level read uncommitted. If you have a multi-statement transaction you may be able to set the isolation level at a lower level for the majority of the transaction and raise it where needed, rather than lowering it just on one or more statements in the transaction.

Upvotes: 4

KristoferA
KristoferA

Reputation: 12397

Both. And it will also read uncommitted data from other [uncommitted] transactions (if any).

Upvotes: 2

Related Questions