If Exists (Select 'X' from Hyp_QED_Amort.dbo.TRG_TblDealMatrix (nolock))
or 
IF Exists (

select * from dbo.amt_currentgpsdata_PRV (nolock)
except 
select * from dbo.amt_currentgpsdata (nolock)
)

	Begin
	    --ITD Running process
		print 'exists'
		
	End

quote:

---

Originally posted by visakh16

IF exists with NOLOCk?
whats that for?
Hope you're aware of data inconsistencies that may cause due to NOLOCK usage



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

quote:

---

Originally posted by James K

If you didn't provide the NOLOCK hint, that doesn't mean that SQL Server will take an exclusive lock. For read operations such as the one you have, it takes only a shared lock. That means multiple processes can read the same data, each of them acquiring shared locks on the same resource. The only lock modes that will come into play here are IS and S (see here: http://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx )

But, if it is a readonly database, I guess having NOLOCK does not hurt anything. Perhaps you gain a tiny bit because SQL Server does not have to do the book keeping on locks, lock escalation etc. But as a matter of style and principle, I avoid NOLOCK hints if at all possible. (Just saying. You don't have to follow my style, even though I am a stylish guy).

---

quote:

---

Originally posted by James K

But I thought in your previous post you said that there are no DML operations on that database. If there are, i.e., if process 1 is modifying the data, then if you use NOLOCK hint, you are going to get what they call dirty reads. And, sometimes, they can be really dirty, in that you are reading uncommitted data.

If you don't use the lock hint, the default READ COMMITTED isolation level indeed will not read dirty pages. But that does not mean that if you are doing a DML operation on one row on a table, the whole table will be locked. SQL Server handles the locking in a smart and efficient way. If you are updating only one row, it would take an exclusive lock only on that single row. If you are updating a large number of rows, the lock may be escalated to the table level. Regardless, the question you want to ask yourself is whether do you really want to get inconsistent, uncommitted data in your reads. If the answer is no, don't use NOLOCK.

There is a feature called Read Committed Snapshot Isolation which you can enable in SQL 2008 or later (which has some performance and disk space implications, but) can alleviate some of the resource contention issues.

---

quote:

---

Originally posted by sent_sara

Thanks for your Detailed Explanation.

Now the answer is: the data should be consistent,now I will remove the NOLOCK hint whereever i have implemented.

the reason why I asked is some times i found my query takes long time and by checking the database consistency (using sp_who2 active) ,some blocks happened. so i thought to implement nolock concept.

quote:

---

Originally posted by James K

But I thought in your previous post you said that there are no DML operations on that database. If there are, i.e., if process 1 is modifying the data, then if you use NOLOCK hint, you are going to get what they call dirty reads. And, sometimes, they can be really dirty, in that you are reading uncommitted data.

If you don't use the lock hint, the default READ COMMITTED isolation level indeed will not read dirty pages. But that does not mean that if you are doing a DML operation on one row on a table, the whole table will be locked. SQL Server handles the locking in a smart and efficient way. If you are updating only one row, it would take an exclusive lock only on that single row. If you are updating a large number of rows, the lock may be escalated to the table level. Regardless, the question you want to ask yourself is whether do you really want to get inconsistent, uncommitted data in your reads. If the answer is no, don't use NOLOCK.

There is a feature called Read Committed Snapshot Isolation which you can enable in SQL 2008 or later (which has some performance and disk space implications, but) can alleviate some of the resource contention issues.

---

---