When should a developer use the NOLOCK hint? What problems can happen

1.	When should a developer use the NOLOCK hint? What problems can happen when using this hint and how can these problems be addressed?
Answer» Yes! We can do that. Follow the below steps to publish a new article to the existing publication. There are two parameters that we need to change to “FALSE”. 1. Immediate SYNC and 2. Allow_Ananymous. Both the fields were set to ON by default. If the Immediate_sync is enabled every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone. Steps: Change the values to “True” for publication properties “Immediate_Sync” and “Allow_Anonymous” using SP_CHANGEPUBLICATION Add a new article to the publication using SP_AddArticle. While executing this PROCEDURE along with the required parameters also specify the parameter “@force_invalidate_snapshot=1”. Add the SUBSCRIPTIONS to the publication for the single table/article using “SP_ADDSUBSCRIPTION”. While executing this proc specify the parameter “@Reserved = Internal”. Generate a new snapshot which only includes the newly ADDED article.

When should a developer use the NOLOCK hint? What problems can happen when using this hint and how can these problems be addressed?

Answer»

Yes! We can do that. Follow the below steps to publish a new article to the existing publication.

There are two parameters that we need to change to “FALSE”. 1. Immediate SYNC and 2. Allow_Ananymous.

Both the fields were set to ON by default. If the Immediate_sync is enabled every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone.

Steps:

Change the values to “True” for publication properties “Immediate_Sync” and “Allow_Anonymous” using SP_CHANGEPUBLICATION
Add a new article to the publication using SP_AddArticle. While executing this PROCEDURE along with the required parameters also specify the parameter “@force_invalidate_snapshot=1”.
Add the SUBSCRIPTIONS to the publication for the single table/article using “SP_ADDSUBSCRIPTION”. While executing this proc specify the parameter “@Reserved = Internal”. Generate a new snapshot which only includes the newly ADDED article.

When should a developer use the NOLOCK hint? What problems can happen when using this hint and how can these problems be addressed?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment