Recently, One of my colleague has reported an issue as below from production environment.
Msg 4138, Level 16, State 1, Line 19
Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.
Error message is very clear and we can easily understand there are some incompatible hints applied in the same query caused a conflict. Let me quickly reproduce the issue for all of us to understand better with a simple example.
-- Sample tables and data population
Drop Table if exists T1,T2
GO
create Table T1(Col1 int )
create Table T2(Col1 int )
GO
Insert into T1 Values(1)
Insert into T2 Values(1)
GO
Select * From T1
Select * From T2
The above code will create two tables – T1 and T2. I do not want to get complicated with more columns and its a simple and easy replication with a single column Col1. Now, Let us create a view using both of the tables. Please note, while creating the view we have given WITH (NOLOCK) on one of table called T2 as below.
--Sample view
Drop view if exists vw_test
GO
Create OR ALTER View vw_test
as
Select * From T1
where exists(Select 1 From T2 with (nolock) where t1.Col1 = t2.col1)
If you notice the VIEW, as mentioned above, NOLOCK has given to avoid any locking scenario deliberately. We are not going to cover whether NOLOCK is good or bad in this blog post, but this is a common bad practice that almost all people do, even experience people would do it at times. A view is a generalized form that anyone can use it as they like later. So, in this case, let us see some of the usages of the view to explain the behavior.
-- SELECT with intended lock
Select * From vw_test A with (UPDLOCK,HOLDLOCK)
This is an example of intended lock on view. While executing the SELECT statement, we will end up with error message
“Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”
Another example:
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1 = A.Col1 + 2
From vw_test A with (ROWLOCK)
This is another example of ROW lock on view for an UPDATE query. While executing the UPDATE statement, we will end up with error message
“Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”
So, precisely, there are many combinations that NOLOCK could end up with this similar exception.
Few are:
ROWLOCK/TABLELOCK/UPDLOCK/SERIALIZABLE etc.
How to resolve this issue?
Two ways you can avoid issue as below:
1. Change the view definition to remove NOLOCK
Create OR ALTER View vw_test
as
Select * From T1
where exists(Select 1 From T2 where t1.Col1 = t2.col1)
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1 = A.Col1 + 2
From vw_test (TABLOCKX) A
2. Change the query that uses view to remove the conflict lock hints
--updlock will conflicts with NOLOCK
Update A Set A.Col1 = A.Col1 + 2
From vw_test A
Summary:
This is a simple error message and does not really require any extra thoughts to be put in to understand the error message or conflict scenario. This is
not just for NOLOCK, any incompatible hints would cause this issue , however, I would like to take an important point that many of us are using NOLOCK as a free tool without knowing its implications. With this example( its just an another example), I would like to reiterate DO NOT use NOLOCK as a free tool, use it with atmost careful evaluation and understanding!
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
