September 18, 2015 at 11:40 am
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
Shamshad Ali
September 18, 2015 at 11:46 am
With the default READ_COMMITTED isolation level the behavior you are seeing is what I would expect, a reader being blocked by writer until the writer commits. You could use nolock as long as you know the risks or you could enable the READ_COMMITED_SNAPSHOT isolation level and use that. I like to send people to this post[/url] by Kimberly Tripp to help understand the different isolation levels.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 18, 2015 at 11:54 am
It depends.
If you're inserting on a table, you might not want to read from it until you finish your insert, unless you don't want to use the inserted rows.
If you don't care about inserted rows, you can use the snapshot isolation. If you want to get all the rows and don't care if the transaction is commited or not use NOLOCK or Read Uncommited isolation.
Just let me tell you a story. I used to work on an ETL workshop and the Employees table was updated everyday by truncating and inserting the whole table. One colleague didn't want to wait until it finished because it was preventing some processes to run. I asked him if he didn't care that half of the employees weren't included in the report. He decided to wait for the load to finish.
September 19, 2015 at 4:20 am
shamshad.ali (9/18/2015)
My question is do I need to use no lock hint in this situation.
I think there are two issues with NOLOCK which I refer to as well-known and poorly-known.
The well-known one is that your resultset might include dirty rows - rows that are subsequently rolled back. Typically people tell me that including them is fine - they might be rolled back by a powercut, in which case someone will type them back in when the lights come back on! or there might be an insert of a record with a $1billion accidental amount which the Business Rule in a trigger catches and rolls back - including that record would dramatically change the report - and the decision that the person reading it then took based on the data - but most people tell me that their client-side validation would have caught that and that they don't resort to Triggers to catch, and then have to rollback, such validation errors. So this type of dirty-data is usually a non-problem, or an OK-problem.
The second, and I think poorly-known, issue is that of an index page split at the exact time that the NOLOCK query is running. Some other task inserts a row into the table, the index page is full, SQL splits the index page into two, us DEVs don't have to lift a finger to make all that happen autoMagically 🙂
However, under certain circumstances, AND with incredibly unfortunate timing, the query can do one of two things:
1. Read the index page just after it has been split, but before the new second-half has been written.
2. Read the original (whole) index page and delay long enough that the second-half page is then available in the index, and read, so the query includes that too.
3. (of 2 :hehe:) SQL may cause the query to abort, with an error, at this point.
3. is probably the best outcome (from users PoV). But it raises a totally unrepeatable error (from DEVs PoV)
1 & 2 will provide duff data in the report but no error/indication. Half-an-index-page-missing is probably far worse than Half-an-index-page-duplicated as (I hope) the duplicates may well be spotted, or cause something else (expecting uniqueness) to fail with an error. Either way the potential for some-rows-wrong is absolutely unacceptable to me, and we NEVER use NOLOCK in production code. We do use it in DBA code - e.g. where we want to query on production tables without interfering with / blocking / etc. the user processes. Our query is probably doing something "unnatural" to the APP and might well cause a side-effect due to our Big Feet! But we know that the data is unreliable, and we are certainly not doing it to be able able to tell a Business Line Manager what the $$$ is on some set of data 🙂
The greater the frequency of Inserts and Selects the higher than chance that the NOLOCK will hit a page-split, the outcome is spooky, unrepeatable, and having to fix it (i.e. by removing all NOLOCKs scattered through the code and coming up with a better solution) is terrifying. I see it used like confetti in some 3rd party APPs, even in situations where it provides no benefit and SQL will just ignore it ... clearly the DEVs are using it because of Policy and not through Knowledge.
We use Read Committed Snapshot (Isolation) on all our OLTP databases. We have that on in DEV and QA so we are confident that we are not doing anything which could fail because of RCSI. There are a few possible problems with RCSI but they are very few and I would guess that most people could just turn on RCSI and solve their blocking issues (ie.. where RCSI is the appropriate solution) without ever hitting a problem - but of course it makes sense to be aware of what the particular issues are with RCSI. The fact that it is a database-setting is what makes it such a useful tool. Removing NOLOCK from every query in an APP is a bigger job for sure 🙂
You might gather that I feel strongly about it 😀 😀 😀
September 19, 2015 at 10:52 am
shamshad.ali (9/18/2015)
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.Shamshad Ali
Quick question, can you use the output clause in the first insert to insert into the second table? That would eliminate the select.
😎
September 28, 2015 at 8:36 pm
Hi shamshad.ali
I think there's noting wrong with the isolation level. (READ_COMMITTED I believe).
If I understand, you described two situations:
Situation 1
1. You started an explicit transaction in SESSION1 with the multiple DMLs. The first was INSERT INTO Table1 ..not committed/rollback
2. Opened another session, SESSION2 in SSMS and executed SELECT on Table1.
As Jack Corbett pointed out, readers(SESSION2) will be blocked by writers (SESSION1) until the the explicit transaction commit/rollback (assuming that the SESSION2 does not use read uncommitted isolation level)
This is normal behavior for read committed isolation level.
The scope of a transaction is session.
Situation 2
1. You started an explicit transaction in SESSION1 with the multiple DMLs. The first was INSERT INTO Table1
2. The writers will not block readers within the same session. This means that if you select data from Table1 after insertion within the same transaction, the select will include the inserted rows even if the transaction is still open.
begin transaction
insert into table1 -- add new rows
select * from table1 --shows the new rows
insert into table2 ...
...
commit transaction
OUTPUT
You can output the inserted rows into a table/table variable.
If you output the inserted rows into a table var, in case of rollback the table variable will keep the inserted rows.:-)
D.Mincic
😀
MCTS Sql Server 2008, Database Development
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply