How to honor locks but take none? 
Author Message
 How to honor locks but take none?
Anyone know a way to make a select statement NOT take any
locks of it's own?
I have some long running sprocs used for reporting. I
would like to prevent them from issuing any table or row
locks. They are currently delaying dml statements quite
frequently.


Sun, 27 Nov 2005 14:38:13 GMT
 How to honor locks but take none?

Jason

Have a look at isolation levels in books on line.

Regards

John



Sun, 27 Nov 2005 14:53:14 GMT
 How to honor locks but take none?
Hi Jason

It is possible to perform dirty reads with either
transaction isolation level set to read uncommitted, or
simply by using "WITH (NOLOCK)" on tables in the FROM
clause of SELECT statements.

These will allow SELECT statements to read without
acquiring locks, but unfortunately, you cannot do this
whilst at the same time honouring others' locks. A dirty
read is basically that - it will read data that might
logically never have existed in the database (dirty /
rolled back)

HTH

Regards,
Greg Linwood
SQL Server MVP

Quote:
>-----Original Message-----
>Anyone know a way to make a select statement NOT take any
>locks of it's own?
>I have some long running sprocs used for reporting. I
>would like to prevent them from issuing any table or row
>locks. They are currently delaying dml statements quite
>frequently.
>.



Sun, 27 Nov 2005 15:25:33 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Locks taken when running Update Statistics

2. update takes table level lock

3. ALTER table lock takes a long time

4. Taking an exclusive lock on a table?

5. Taking an exclusive lock on a table?

6. Honoring Security Permissions set in Analysis Services in OWC

7. SQL 2k not honoring READ UNCOMMITTED

8. SQL Server 7 doesn't appear to honor the Service dependency on NT shutdown

9. FULLTEXT Index not honoring Select TOP x query

10. DAO Failing To Honor ORDER BY

11. Environment vars honored by sqlplus

12. UpdateBatch not honoring "Update Criteria" setting


 
Powered by phpBB® Forum Software