What is with nolock in SQL Server

0 votes
Can someone explain the ramifications of using with (nolock) on queries, as well as when it should be used and when it shouldn't be used?

What sorts of queries would nolock be acceptable in if you have a banking application with high transaction rates and a lot of data in particular tables? Is there a time when you should always use it and when you should never use it?
Feb 21 in Database by Vaani
• 5,240 points
52 views

1 answer to this question.

0 votes
Using READ UNCOMMITED as a transaction isolation level is equal to using WITH (NOLOCK). As a result, you run the risk of reading an uncommitted row that is later rolled back, i.e. data that was never entered into the database. As a result, while it can avoid reads from being slowed down by other activities, it also poses a danger. It's probably not going to be the proper solution to whatever problem you're attempting to address with it in a banking application with high transaction rates, IMHO.

The question is whether a stalemate or an incorrect value is worse.
Deadlocks are significantly worse than incorrect values in financial databases. That may sound backwards, but bear with me. DB transactions are often used to update two rows by removing from one and adding to the other.

Business transactions are used in a financial database. That implies each account will have one row added to it. It's critical that these transactions finish and that the rows are correctly written.

It's not a major concern if the account balance is momentarily incorrect; that's what the end of day reconciliation is for. And an overdraft from an account is significantly more likely to occur as a result of two ATMs being used at the same time than as a result of an uncommitted database read.

However, SQL Server 2005 solved the majority of the problems that necessitated the use of NOLOCK. You shouldn't require it unless you're using SQL Server 2000 or older.
answered Feb 21 by Neha
• 6,260 points

Related Questions In Database

0 votes
1 answer

What is subquery in SQL?

A subquery is a query inside another ...READ MORE

answered Oct 12, 2018 in Database by DataKing99
• 8,240 points
831 views
0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,360 points
577 views
0 votes
1 answer

What is an index in SQL?

An index is used to speed up ...READ MORE

answered Feb 3 in Database by Vaani
• 5,240 points
29 views
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17 in Database by Vaani
• 5,240 points
32 views
0 votes
0 answers

What is "with (nolock)" in SQL Server?

Can someone explain the ramifications of using ...READ MORE

Feb 22 in Database by Vaani
• 5,240 points
70 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
286 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 5,240 points
39 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 5,240 points
14 views
0 votes
1 answer

What is the candidate key in SQL Server?

A Candidate Key is any column or ...READ MORE

answered Feb 23 in Database by Neha
• 6,260 points
17 views
0 votes
1 answer

What is the definition of cardinality in SQL?

Cardinality is defined as the "number of ...READ MORE

answered Feb 15 in Database by Neha
• 6,260 points
86 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP