Use SET TRANSACTION ISOLATION LEVEL in Views

Question

Use SET TRANSACTION ISOLATION LEVEL in Views

Aruna Gubba

Ten Centuries

Points: 1049
More actions
July 13, 2006 at 11:43 am

#113723

Can someone please help me on how to use the "set transaction isolation level read uncommitted" statement in view definition.
EX:
Create View TEST as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Select * from products
When I execute the above, I get an error:
Server: Msg 156, Level 15, State 1, Procedure TEST, Line 3
Incorrect syntax near the keyword 'SET'.
The only workaround I found was to use (NOLOCK) hint.
TIA
Aruna

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

A view definition can only consist of a single (possibly very complicated) SQL statement.

IF (you have to avoid reserving or respecting locks AND you don't mind retrieving inconsistent data) OR the data is read-only,

You could use a table locking hint:

Select *
from products with(nolock)
join table2 with(nolock)
on col1 = col2

BUT if these conditions aren't met, you should be very reluctant to use NOLOCK or READ UNCOMMITTED...

[edit -

sorry just noticed that you already mention NOLOCK in your post - unless that was an edit too?

You can set the isolation level from whatever batch refers to the view, then set it back to serializable (or whatever you use normally) afterwards. There is no query-level NOLOCK hint.

]

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Aruna Gubba Ten Centuries Points: 1049 More actions · Answer 2

Thank you for the reply.

I am not sure how the views are being called, a friend of mine asked this question, how to use set transaction... in a view. This method in setting in the view definiton did not work. I will tell him what you suggested.

Aruna