Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 1.
Every day one winner from United States will get Joes 2 Pros Volume 1.
Dirty Records Recap
Most SQL people know what a “Dirty Record” is. You might also call that an “Intermediate record”. In case this is new to you here is a very quick explanation. The simplest way to describe the steps of a transaction is to use an example of updating an existing record into a table. When the insert runs, SQL Server gets the data from storage, such as a hard drive, and loads it into memory and your CPU. The data in memory is changed and then saved to the storage device. Finally, a message is sent confirming the rows that were affected.
For a very short period of time the update takes the data and puts it into memory (an intermediate state), not a permanent state. For every data change to a table there is a brief moment where the change is made in the intermediate state, but is not committed. During this time, any other DML statement needing that data waits until the lock is released. This is a safety feature so that SQL Server evaluates only official data.
Basic Table Hints
Some transactions take time and then rollback. In other words, the changes never become official data and in essence never took place. The example below shows a transaction that takes 15 seconds and then rolls back:
BEGIN TRAN
UPDATE dbo.Employee
SET HireDate = '1/1/1992'
WHERE EmployeeID = 1
WAITFOR DELAY '00:00:15'
ROLLBACK TRAN
If employee #1 was really hired in 1989 and you run the above code, you have incorrect data for fifteen seconds in the intermediate state. During those fifteen seconds, if a 401K vesting program ran an evaluation on all employees hired before 1990, the process would wrongfully overlook employee #1. The safety catch is the 401K process would wait until this transaction is done in order to get the official data.
A great deal of data can be changed in the intermediate state, but never get committed. Locking prevents other processes from making decisions on dirty data. The result is that only committed data is used to isolate transactions. The drawback is that some processes that could run instantly now have to wait. Locking lowers the level of concurrency, which is the ability to allow software to run many processes at the same time.
A Select query can now pull out any record being changed in the intermediate state. You could say that a query will not show any dirty records because all transactions are isolated from each other. By changing one record in your CurrentProducts table, a query looking for records from that table will have to wait. In the figure below the update statement was executed first and puts data into an intermediate state. The query on the right waits for the transaction to be committed.
If you look more closely you can see that the update statement has been running for one minute, 38 seconds and the query on the right was started six seconds later. The committed RetailPrice is $75 and the RetailPrice in the intermediate state of that value is $999. This will be turn two and a half minutes. Upon completion, the price never changed and the $999 value was thrown away (never persisted). The query on the right waits until the data becomes official. This is a safety feature that has a downside in that the select query takes much longer to run.
If you don’t want the query on the right to wait, you have some choices. If you are fine with the query on the right accessing dirty data, you can issue a locking hint for the query. The following code tells the query to run without waiting:
SELECT *
FROM CurrentProducts (READUNCOMMITTED)
In the code example would run without waiting. The result is the query would show the RetailPrice value of $999.00.
In the figure below we see ProductID 1 has a value in the RetailPrice field of $999.00 in the result set. The query did not wait and ran with the newest value it found in memory. In the end the $999.00 was never a committed value in the database. Any evaluation of data running this query could give you a false report. In other words you run the same query three minutes later and get a different result. This is despite the fact that the table may not have really changed at all in that time.
The READUNCOMMITTED table hint allows the query to show uncommitted (dirty) data. The advantage is the query runs much faster. This is a common solution for evaluation queries that don’t need to be exact. A common example is just looking to see how many records are in a table as in the query below:
SELECT COUNT(*)
FROM CurrentProducts (READUNCOMMITTED)
You know this result changes over time and is used for general trends. In this case, SQL Server allows the query to run without waiting. The following statements are equivalent and show a shorter way of accomplishing the same result:
SELECT COUNT(*) FROM CurrentProducts (READUNCOMMITTED)
SELECT COUNT(*) FROM CurrentProducts (NOLOCK)
Readuncommited and nolock are just two of many Table hints used by SQL server to affect how a queries isolcation level can be affected.
Note: If you want to setup the sample JProCo database on your system you can watch this video.
Question 7
Q 7) What happens when you try to query records in the intermediate state?
- Your query waits until the data is no longer in the intermediate state.
- Your query runs normally.
- Your query splits the results into permanent and intermediate results.
Please post your answer in comment section to win Joes 2 Pros books.
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Your query waits until the data is no longer in the intermediate state.
Since we did not specify NOLOCK or READUNCOMMITTED the query waits on the initial transaction
Fort Worth Texas
Q 7) What happens when you try to query records in the intermediate state?
correct answer -
1. Your query waits until the data is no longer in the intermediate state.
Shekhar Gurav.
country : India
Q 7) What happens when you try to query records in the intermediate state?
A. 1.Your query waits until the data is no longer in the intermediate state.
Gordon Kane
Allen TX
USA
option one is right answer.
1.Your query waits until the data is no longer in the intermediate state.
Neelesh Jain
India
Correct Ans is option 1.
Your query waits until the data is no longer in the intermediate state.
Pratik Raval
India
ans is 1
Q 7) What happens when you try to query records in the intermediate state?
1) Your query waits until the data is no longer in the intermediate state.
Q 7) What happens when you try to query records in the intermediate state?
1) Your query waits until the data is no longer in the intermediate state.
Leo Pius
USA
Answer:1
Your query waits until the data is no longer in the intermediate state.
kulwant Kumar
Delhi
India
The answer is very much clear in your own blog post. if we are mentioning READUNCOMMITTED or NOLOCK table hint, it will show are dirty records too otherwise, it will wait till lock release records so the answer should be 1st in your choice list, which is:
“Your query waits until the data is no longer in the intermediate state.”
The answer is very much clear in your own blog post. if we are mentioning READUNCOMMITTED or NOLOCK table hint, it will show are dirty records too otherwise, it will wait till lock release records so the answer should be 1st in your choice list, which is:
“Your query waits until the data is no longer in the intermediate state.”
Ritesh (India)
If there are no hints on this query, then the correct answer will be 1 – waits till data are no longer in the intermediate state.
I am from USA>
the correct answers is option : 1 ie.
“Your query waits until the data is no longer in the intermediate state.”
because we did not specify “nolock or readuncommitted”
so the query will wait until data is commited
India
Correct Answer as
1. Your query waits until the data is no longer in the intermediate state.
Varinder Sandhu (India)
Answer is # 1.
“Your query waits until the data is no longer in the intermediate state.”
If the CHECKPOINT option has been given, then an Intermediate Data is committed and it is available for the Query.
Gopalakrishnan Arthanarisamy
Bangalore, India
[...] [...]
Your query waits until the data is no longer in the intermediate state
Rajesh PR (India)
Hi,
Q 7) What happens when you try to query records in the intermediate state?
1) Your query waits until the data is no longer in the intermediate state.
when one query is running on that time table getting lock so all data of that table are in intermediate state. so up-till locked wont get release other query need to waits until data are in intermediate state.
I am from India..
Q 7) What happens when you try to query records in the intermediate state?
Correct Ans:
1) Your query waits until the data is no longer in the intermediate state.
Jatin
INDIA
Answer is 1
Your query waits until the data is no longer in the intermediate state.
Abhishek Bhat
India
Correct answer is option : 1
1.Your query waits until the data is no longer in the intermediate state.
Thanks.
Country – India
What happens when you try to query records in the intermediate state?
1) Your query waits until the data is no longer in the intermediate state.
Above answer is the reason to query intermediate state.
Chennai, Tamilnadu, India
Correct answer: #1 – Your query waits until the data is no longer in the intermediate state.
Country of residence: India
Correct ans is : #1 option –Your query waits until the data is no longer in the intermediate state.
country-India
Q 7) What happens when you try to query records in the intermediate state?
Your query waits until the data is no longer in the intermediate state.
(Sale, Nigeria)
Correct Answer is
1.Your query waits until the data is no longer in the intermediate state
INDIA
1. Your query waits until the data is no longer in the intermediate state.
Sudeepta
India
Correct answer is option 1
1.Your query waits until the data is no longer in the intermediate state.
New Delhi
1.Your query waits until the data is no longer in the intermediate state.
Bhavesh Darji
India
1) Your query waits until the data is no longer in the intermediate state.
Option : 1 is the Answer
Q 7) What happens when you try to query records in the intermediate state?
Option 1 Correct:- Your query waits until the data is no longer in the intermediate state.(By Default SQL Server locks the object in Transaction so any other transaction on same object have to wait until the 1st transaction completes)
Thank you,
Yeou Sunn from Bhilai, Chhattisgarh, India.
if we are mentioning READUNCOMMITTED or NOLOCK table hint, it will show are dirty records too otherwise, it will wait till lock release records so the answer should be 1st in your choice list, therefore the correct is
1.Your query waits until the data is no longer in the intermediate state
I’m from INDIA
1.Your query waits until the data is no longer in the intermediate state.
Partha
India
Query waits until the data is not longer in the intermediate state.
Kelly
USA
Correct Answer:
Your query waits until the data is no longer in the intermediate state
kkmjssate
India
Actually the answer depends on the default isolation level. If the default isolation level is READ COMMITTED the answer is 1.
If the default isolation level is READ COMMITTED SNAPSHOT (as in SQL Azure) the answer is 2
Roji P Thomas
1. Your query waits until the data is no longer in the intermediate state.
Country – India
Answer is Option 2
Sandeep Kumar
INDIA
1.Your query waits until the data is no longer in the intermediate state.
Texas, USA
The right answer is No. 2, the query runs normally.
This is because according to the question as is written, NOLOCK or READUNCOMMITTED have not been used, so the query doesn’t have to wait until the data is no longer in the intermediate state.
Now, if NOLOCK or READUNCOMMITTED are implicitly used due to the subject, then right answer is No. 1.
Rene Castro
El Salvador
Correct answer:
1.Your query waits until the data is no longer in the intermediate state.
Country: United States
Answer is No. 1
The Query waits until the data is out of the intermediate state.
Adrian B
US
when you try to query records in the intermediate state?
In this condition correct answer is:
“Your query waits until the data is no longer in the intermediate state.”
Option No 1.
Thanks,
Rajneesh Verma
INDIA
1.Your query waits until the data is no longer in the intermediate state.
This is correct option.
India
-Dnyanesh
As per my knowledge ans
1) Your query waits until the data is no longer in the intermediate state.
As per my knowledge ans
1) Your query waits until the data is no longer in the intermediate state.
USA
Option 1
1.Your query waits until the data is no longer in the intermediate state.
Explaination :
Read committed (Database Engine default Isolation level) which prevents the dirty read.
Thanks
Shree
Bangalore India
Correct answer: option 1 –
1) Your query waits until the data is no longer in the intermediate state.
Country : India
Vivek Srivastava
The Correct answer for the above question is Option 1
“Your query waits until the data is no longer in the intermediate state.”
Since we did not specify NOLOCK or READUNCOMMITTED the query waits on the initial transaction so it will wait to get the data when it is in normal state comes out the intermediate state.
DILIP KUMAR JENA
Country : INDIA
The answer is #1. Your query waits until the data is no longer in the intermediate state.
The query is not using the READUNCOMMITTED table hint, so it will not run while the data is in the intermediate state.
I’m from Michigan, in the USA
1)Your query waits until the data is no longer in the intermediate state.
india
kaushik thanki
Your query waits until the data is no longer in the intermediate state.
Chetan – USA
Your query waits until the data is no longer in the intermediate state
Manoj Sahoo
Scalable-Systems
Q 7) What happens when you try to query records in the intermediate state?
Answer is:
1. Your query waits until the data is no longer in the intermediate state.
Country: India
Q 7) What happens when you try to query records in the intermediate state?
Option 1. Your query waits until the data is no longer in the intermediate state
is correct answer.
From,
Malay Shah
City: Ahmedabad
Country:India
If we have not specify (NoLock) or (ReadUncommitted) then the answer is
answers is :
1 )Your query waits until the data is no longer in the intermediate state.
The query is not using the READUNCOMMITTED table hint, so it will not run while the data is in the intermediate and waits until the data becomes official
Location: India
Correct answer is # 1.Your query waits until the data is no longer in the intermediate state.
Country: India
The Correct Answer is Option 1:
Your query waits until the data is no longer in the intermediate state.
India
Correct Answer is Option 1.
Krunal K. Doshi
Ahmedabad
Correct answer is option 1:
Your query waits until the data is no longer in the intermediate state.
Jankhana,
India
Answer is: 1. Your query waits until the data is no longer in the intermediate state.
Country: India
Answer: Option 1
Your query waits until the data is no longer in the intermediate state.
Country: India(sikha)
Option 1 is correct
Your query waits until the data is no longer in the intermediate state.
Country : India
Thanks
GurjitSingh
1.Your query waits until the data is no longer in the intermediate state.
Somnath Desai
India
The Correct Answer is Option -1
Thanks,
Narendra(India)
Ans 1 is right
INDIA
Answer 1 :
Your query waits until the data is no longer in the intermediate state.
Country:India
1. “Your query waits until the data is no longer in the intermediate state.”
As SQL as default isolation of Read committed to prevent Intermediate state
-Annie
Bangalore
India
Answer 1 :
Your query waits until the data is no longer in the intermediate state.
Country:India
Option 1 is correct
1. Your query waits until the data is no longer in the intermediate state.
India
The correct answer is option 1
Your query waits until the data is no longer in the intermediate state.
Unless a query hint such as (NOLOCK) or (READUNCOMMITTED) the query will wait for any lock to be released on the data being retrieved.
Country of Residence: USA
Option 1 is the correct answer
!.Your query waits until the data is no longer in the intermediate state.
Rahul Singh rathore
Country: india
Hi Pinal,
Challenge:
Q 7) What happens when you try to query records in the intermediate state?
Correct Answer:
The first choice is the correct answer:
1.Your query waits until the data is no longer in the intermediate state.
Explanation:
If your query didn’t have table hints (nolock, or readuncommitted) the query would wait until the data is no longer in an intermediate, dirty state.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
Hi Sir,
The answer is option 1 : “The query waits until the data is no longer in the intermediate state.”
If the query transactions is with “ISOLATION” property then it will not see the data in an intermediate state.
Anish Shenoy
INDIA,Bangalore,Karnataka
[...] [...]
Answer: 1
Your query waits until the data is no longer in the intermediate state.
Ashish, India
Q 7) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35
Since the query may be waiting for data to leave the intermediate state and the results will not contain intermediate results both (2) and (3) are incorrect. Because SQL needs to ensure data integrity your query waits until the data is no longer in the intermediate state so (1) is correct.
Winner from USA: Bill Pepping
Winner from India: Nakul Vachhrajani
I thank you all for participating here. The permanent record of this update is posted on facebook page.
If NOLOCK or READUNCOMITED is NOT specified then Query 1 is true, as Query has to wait until that transaction is get commited.
If NOLOCK OR READUNCOMITED is specified, then query runs normally, as we specifically defined that, it is ok if SQL SERVER Returns DIRTY DATA.
Country: INDIA
Correct option is 1.
Shilpa
India.
Answer : 1
City: Mumbai
Country : India
Hi Pinal Sir,
The correct answer for this question is option no. 1:
” Your query waits until the data is no longer in the intermediate state. “
The correct answer is the number 1.
Francisco,
Miami
Correct answer is #1. Your query waits until the data is no longer in the intermediate state.
Uday
USA
Answer is #1:
Your query waits until the data is no longer in the intermediate state.
But if you specify the READUNCOMMITTED clearly in the query, then the dirty (intermediate state) data will be reached.
Thanks,
Wayne
Answer Option 1
from USA
Hi Pinaldave,
I actually had a question it… when tried to do the same my select query was not waiting for 15sec, its immediatley returning the uncommitted data without the READUNCOMITED keyword, pls let me know where i was wrong…
Thanks
Padma
Q 7) What happens when you try to query records in the intermediate state?
A. 1.Your query waits until the data is no longer in the intermediate state.
Sreejith Vijayan
Kerala
India
1.Your query waits until the data is no longer in the intermediate state.
Your query waits until the data is no longer in the intermediate state.
Since we did not specify NOLOCK or READUNCOMMITTED the query waits on the initial transaction
India
[...] SQL Joes 2 Pros Development Series – Dirty Records and Table Hints [...]