November 14, 2018 at 2:27 pm
Good afternoon everybody,
I need help from the Community.
For the purposes of this post I do not worry about dirty reads. In my example I have 3 SQL statements, that I am executing within a query, i.e.:
SELECT * FROM table1 WITH (NOLOCK) ;
GO
SELECT * FROM table2 WITH (NOLOCK);
GO
SELECT * FROM table3 WITH (NOLOCK);
GO
Could anyone please tell me if there is any performance if I would execute the following 3 scripts.
SELECT * FROM table1 WITH (READUNCOMMITTED) ;
GO
SELECT * FROM table2 WITH (READUNCOMMITTED);
GO
SELECT * FROM table3 WITH (READUNCOMMITTED);
GO
These scripts are being executed on the query level.
But I would also be very curious to figure out if there is any difference if we are placing the similar scripts in the stored procedures or other objects.
November 14, 2018 at 5:17 pm
NOLOCK Is equivalent to READUNCOMMITTED
READUNCOMMITTED
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).
READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement
November 14, 2018 at 6:19 pm
and you should really forget that those 2 options exist - pure evil.
If you have locking issues look into the possibility of enabling READ_COMMITTED_SNAPSHOT your database.
Depending on your application it may already support it, or it may not be important. Have a read at https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/
November 14, 2018 at 6:21 pm
AER - Wednesday, November 14, 2018 2:27 PMGood afternoon everybody,
I need help from the Community.For the purposes of this post I do not worry about dirty reads. In my example I have 3 SQL statements, that I am executing within a query, i.e.:
SELECT * FROM table1 WITH (NOLOCK) ;
GO
SELECT * FROM table2 WITH (NOLOCK);
GO
SELECT * FROM table3 WITH (NOLOCK);
GOCould anyone please tell me if there is any performance if I would execute the following 3 scripts.
SELECT * FROM table1 WITH (READUNCOMMITTED) ;
GO
SELECT * FROM table2 WITH (READUNCOMMITTED);
GO
SELECT * FROM table3 WITH (READUNCOMMITTED);
GOThese scripts are being executed on the query level.
But I would also be very curious to figure out if there is any difference if we are placing the similar scripts in the stored procedures or other objects.
Why not just do a test yourself?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2018 at 6:45 am
Let's start with this:
Both are evil!
That out of the way, you can only use NOLOCK by modifying code. You can use READ UNCOMITTED by changing the transaction isolation settings within the calling code (you absolutely do not have to hint it the way you've done, in fact, that's a horrific use of READ UNCOMMITTED). Here's some documentation on that.
While I truly hate the way these options get used, I strongly recommend that you don't use NOLOCK and instead use READ UNCOMMITTED. The reason I say this is not because one behaves differently or better than the other. The reason I say this is because you should be able to change your isolation levels in one location. This means, when you realize just how horrible the things you're doing to your data are, you can easily fix it by making a single change as opposed to having to change every single query, procedure and prepared statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2018 at 7:31 am
Isolation level determines how much time your query will spend waiting for other processes to commit their updates. In a transaction databases (where many users are querying and updating at the same time), a query running under the default READ COMMITTED isolation level will typically alternate in and out of a blocked state many times during the course of it's execution. In contrast, READ UNCOMMITTED isolation level will not wait for update locks to be released, it will read pages in whatever state they happen to be in at the moment. For a long running query, this could mean you'll be returning duplicated rows or only part of a batch update.
So, here is an analogy: Let's assume your boss asks you to count the number of people who show up for a conference, so you can then call the catering company and order the proper number of lunches. You want as accurate a count as possible, but maybe you don't want to wait too long to get that count either.
READ UNCOMMITTED isolation (also enabled using the NOLOCK hint) would simply do a quick head count while folks are still entering and leaving the convention hall. You would get the result sooner, but it may not be entirely accurate, depending on the degree of moving about.
READ COMMITTED (the default isolation level when none is specified) would wait until all attendees are settled into their seats and then count heads.
READ COMMITTED SNAPSHOT isolation would take a photo of the convention hall, so you would have an accurate count as of a specific moment in time.
SERIALIZABLE isolation would temporarily block anyone from entering or leaving the convention hall until your head count is completed. This is quickest isolation to run under (at least from your perspective), but you're causing other people to wait, because you're holding exclusive locks instead of shared locks.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 15, 2018 at 8:30 am
Hi again,
I know what are the isolation levels, and the differences between them. I was giving exanples for reports that do not need precise accuracy (does not mean that I am using these statements).
Developers often issuing similar scripts in pre-production environments without transactions. Some of them utilizing WITH (NOLOCK) in each 'select' statement, the others WITH (READUNCOMMITED).
My question is, if BEGIN TRANSACTION not set, then is issuing the WITH (READUNCOMMITED) on every select statement performance wise the same as WITH (NOLOCK)?
My feeling is that WITH (READUNCOMMITED) should take longer time. And also I beleive that issuing WITH (READUNCOMMITED) on every select statement is wrong?
I am not sure how to accurately test the performance differences.
Thank you
November 15, 2018 at 9:56 am
AER - Thursday, November 15, 2018 8:30 AMHi again,I know what are the isolation levels, and the differences between them. I was giving exanples for reports that do not need precise accuracy (does not mean that I am using these statements).
Developers often issuing similar scripts in pre-production environments without transactions. Some of them utilizing WITH (NOLOCK) in each 'select' statement, the others WITH (READUNCOMMITED).My question is, if BEGIN TRANSACTION not set, then is issuing the WITH (READUNCOMMITED) on every select statement performance wise the same as WITH (NOLOCK)?
My feeling is that WITH (READUNCOMMITED) should take longer time. And also I beleive that issuing WITH (READUNCOMMITED) on every select statement is wrong?I am not sure how to accurately test the performance differences.
Thank you
I can give you the answer, but to be sure, you should test it.
They're the same, but you should be able to easily validate that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2018 at 10:01 am
AER - Thursday, November 15, 2018 8:30 AMHi again,I know what are the isolation levels, and the differences between them. I was giving exanples for reports that do not need precise accuracy (does not mean that I am using these statements).
Developers often issuing similar scripts in pre-production environments without transactions. Some of them utilizing WITH (NOLOCK) in each 'select' statement, the others WITH (READUNCOMMITED).My question is, if BEGIN TRANSACTION not set, then is issuing the WITH (READUNCOMMITED) on every select statement performance wise the same as WITH (NOLOCK)?
My feeling is that WITH (READUNCOMMITED) should take longer time. And also I beleive that issuing WITH (READUNCOMMITED) on every select statement is wrong?I am not sure how to accurately test the performance differences.
Thank you
Microsoft say: "NOLOCK Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic."
So they are equivalent, one is a synonym for the other, they do the same thing, there is no difference. As I pointed to you in my previous reply.
It doesn't matter if "BEGIN TRANSACTION not set" because it might be someone else's transaction you are looking at midway through. A page split might be going on so you might get the same row back twice in different rows on the result set.
If you are querying data that is not being updated, and you don't mind too much if you get invalid data back, for example looking at old archive rows at the end of a table then you are probably are in a situation where NOLOCK or READUNCOMMITED will be helpful. I've also found it useful if there is a large insert going on in a transaction, you can read the data that's not committed so get an idea about how far through the process is.
April 15, 2020 at 7:26 pm
Hello
I know this thread is almost 2 years old, however sticks to my current situation to a measured extend. We were using Oracle database & related technologies and currently moving to MS SQL & other Microsoft technologies.
I've been working with Oracle database & as there are no table hints like WITH(NOLOCK) or READPAST, I need some guidance to get start with MS SQL
We will ONLY read committed rows, said, no dirty reading. I've been referring multiple documents about MS SQL table hints and couple of places read that the Select statement could place a lock on the table, blocking other transactions. Hence, I am eager to know how exactly we circumvent situations when a time consuming query executing against a table that is expected to being committed few times per minute (Sales Order Headers/Sales Order Lines tables)
As the select statements will be doing analysis of sales and other MIS we cannot afford to have dirty reads. I hope I have made myself as clear as possible and apologize for not knowing many technical terms those are specific to MS SQL.
Thanks and regards,
rajesh
April 15, 2020 at 7:53 pm
Look up READ COMMITTED SNAPSHOT. That will be a humungous help to you. Just know that it adds overhead to tempdb. However, most people won't even notice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2020 at 4:55 am
Hello @Grant
Thank you very much. I've read about snapshots at StackOverFlow and few other places, however, my primary concern is not truly answered.
I would pry you to give me a hint about long running queries locking up referred tables. For example, our Oracle EBS material transaction table has 11M rows & at times when materialized views are insufficient (A day old usually), we are forced to refer the base table, which is updated every other second & never had any lockups or related performance issues as Oracle only selects committed rows. With MS SQL, it looks like the approach is different, hence WITH(NOLOCK) & hints are used.
If this statement is true, I have to design accordingly. We are planning to develop a subsystem for the new business application & throughout the discussions the other party (Software vendor) was asking us to use WITH(NOLOCK) against the queries we are going to execute against their tables.
regards,
rajesh
April 16, 2020 at 5:28 am
Hello @Grant
Thank you very much. I've read about snapshots at StackOverFlow and few other places, however, my primary concern is not truly answered.
I would pry you to give me a hint about long running queries locking up referred tables. For example, our Oracle EBS material transaction table has 11M rows & at times when materialized views are insufficient (A day old usually), we are forced to refer the base table, which is updated every other second & never had any lockups or related performance issues as Oracle only selects committed rows. With MS SQL, it looks like the approach is different, hence WITH(NOLOCK) & hints are used.
If this statement is true, I have to design accordingly. We are planning to develop a subsystem for the new business application & throughout the discussions the other party (Software vendor) was asking us to use WITH(NOLOCK) against the queries we are going to execute against their tables.
regards,
rajesh
You said the table has 11 millions rows (which isn't really that big) but you not said how many rows you're actually reading.
A lot of people use WITH (NOLOCK). The reason why they have to use it isn't because other things are blocking a query... it's because the query is taking to long and can't help but be blocked.
A far better thing to do is to fix the query that is being blocked and to fix the query(ies) that are doing the blocking.
Unfortunately, very few actually take the time to even try that permanent fix for blocking.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2020 at 5:37 am
Hello @jeff
I was referring Oracle in the context to explain how we "were" doing things until date & how the demand from new Solution provider to use WAIT(NOLOCK) with all queries executed against their tables, is getting us in to confusion as WAIT(NOLOCK) clearly documented as dirty-reading.
If possible we would prefer "Select a,b,c from xyz" in the place of "Select a,b,c from xyz WAIT(NOLOCK)" to make sure that only committed rows are selected. However, as I mentioned with my previous posts, I am reading statements from different sources about the Select statements putting exclusive locks on rows, blocking other transactions from accessing the currently queried row(s)
Thank you very much for the reply.
regards,
April 16, 2020 at 6:26 am
As Grant mentioned in order to achieve a similar functionality like Oracle you need to have your database set with Read Committed Snapshot (RCSI).
Basically with RCSI your readers will not block writers and your writers will not block your readers, and it also means you do not need to use the hint "with (nolock)" (by the way the name is not wait(nolock) and in any case you should almost NEVER use this hint)
this will give you the same behavior as Oracle.
see some comments on it here
and have a look at these also
https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/
Your application needs to be designed to deal with the issues that using RCSI has which are pretty much the same that Oracle has. e.g. when updating a record it needs to verify that the record has not been changed since it was last read by the transaction that is updating it.
for your info on my shop majority of databases have RCSI enabled (and we have a lot of them!!)
main ones that have been designed to use it
Microsoft CRM - Uses sp_getapplock to enable correct handling possible multiple updates of same set of records in multiple tables
In House Pollicy Admin system - uses built in controls - each table has a transactionid column that gets updated with every single update statement with a new value, and the code when doing the update verifies that the value of this column is the same as it was when the application read the record.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply