nested views lock management

Hi, I have a question about nested views. Is there a way for the view not to place any locks on the underlying tables? I tried to re-write my views and its nested views with "with (nolock)" but when I view the enterprise manager, I still see exclusive and share lock on the tables. Any help would be appreciated. Thanks.

I have wanted to do this before and have tried it; however, views do not work that way. If you want to code with the NOLOCK hint you will need to code it with the actual queries. One alternative, although not an efficient one, would be to create a multi-line TVF instead of a view in which the function consists of a query that contains the NOLOCK hint. I have never used this alternative and I guess I don't really recommend it.

|||

Hi , Thanks for the reply.

I was also wondering how about putting the select from view in a store procedure, and set the transaction level to read uncommited. Would that work? This is because I think I am getting a dead lock on a table. for some reason, the select is placing a share lock and an exclusive lock on the underlaying table which make no sense to me, I thought exclusive lock is only for insert, update and delete. Thanks for any response again.

|||

Which SQL Server version do you use?

If you use SQL Server 2005, you could use READ_COMMITTED_SNAPSHOT option for you database.

Using snapshot isolation could decrease number of deadlocks, because transaction isolation was implemented with usage row versions.