-2

I am building a stored procedure for reporting and I am using (NOLOCK) for all select statements.

There is no locking requirement for the scenario I am working on.

I am thinking to change the TRANSACTION ISOLATION LEVEL at the top of the stored procedure, and avoid adding (NOLOCK) to all of the select statements. Is there a TRANSACTION ISOLATION LEVEL that is equivalent with (NOLOCK) when I set it at the top of the store procedures?

Allan Xu
  • 7,998
  • 11
  • 51
  • 122
  • http://stackoverflow.com/q/27198020/3270427 – McNets Oct 27 '16 at 17:44
  • Probably a good idea to read this article. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ NOLOCK is far more sinister than most people realize. – Sean Lange Oct 27 '16 at 18:11
  • You could have gotten the answer by Googling 'Nolock transaction isolation level sql server'... – dfundako Oct 27 '16 at 19:06

1 Answers1

1

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Thank you for prompt help. Can you confirm that when I SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, it **only** affects the stored procedure running the code? I don't want to change the ISOLATION LEVEL of the rest of the application. – Allan Xu Oct 27 '16 at 17:57