Can an INSERT statement block SELECT statements?
-
Hi All,
Can an INSERT statement block SELECT statements? not sure why ? Can anyone help me understand in what scenarios it may happen?
Thanks,
Sam
-
if the page inserts happens at end of the file, why it has block the SELECT statements?
-
Yes - an insert statement can block select statements. The insert statement is going to take exclusive locks - the level of those locks depends on what that insert statement is actually doing. It can be locks at the row, page, extent or table level.
Your select statement has to wait for the insert to release the lock - so it doesn't read incorrect data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
When an INSERT occurs, a lot of locks are taken out. It's not just a question of adding a row at the end of the last page (assuming a clustered index based on ordered data, change that assumption, and everything else changes too). Any non-clustered indexes will also be updated, and, depending on the values there, the inserts can occur in pages in the center of the index, not just at the end. It completely depends on the key column or columns and the distribution of the data.
So these simple locks will impact SELECT statements while they are held if those statements need access to the same pages currently being written to. Expand the size of the INSERT, you expand the impact to reads.
Basically, assume everything affects everything else. Sure, two reads against the same page don't impact each other. But two reads against different pages may run into contention trying to get the memory needed to read those pages, even though there is no inherent locks blocking those reads. It's all a great big, interactive, complicated, mess. Frankly, it's amazing just how well it works considering the difficulty of managing all the different interactions. However, it works great (most of the time).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
To go along with with Grant so very correctly stated, there's the subject of this "little" thing known as a "Bad Page Split". Rows are forced to go onto the correct pages of an index according to the logical order of keys. When a page is full, the page splits into 2 pages and the new page must be registered in the next level up on the B-Tree, which can block hundreds of other pages. That can happen on every level in the B-Tree including, the Root Level. If the root level page needs to split, then the entire index is locked by the system-level transaction that all page splits cause (including the supposed "good" page splits.
That's why you shouldn't just do supposed "Best Practices" index maintenance. All those lovely indexes that have fragmented due to mid-index page splits have made room for such inserts by doing page splits. Then along comes the index maintenance that either rebuilds those 0 (exactly the same as 100) Fill Factor indexes and now they have no where to put the out of order inserted pages and you're stuck with massive numbers of page splits with block EVERYTHING. And, yep... the same holds true with Reorganize but it's worse. If you've assigned a fill factor, it will consolidate pages below the fill factor and do absolutely NOTHING to make space above the fill factor because it cannot create new pages.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks everyone for the explanation.But one thing bothering me,
Considering the below example, I am in read committed isolation level and assuming that my table has 100 rows inside it.
now, comes 2 spids. spid = 55 , started a trasanction and does 200 new INSERT's and the txn is still open. In the meanwhile, another spid = 56, tries to do a SELECT on the table. In this case, I would expect sql to return 100 rows which are committed and since other txn is still active. it doesnt show up the newly added 200 rows. in this case, does the SELECT gets blocked or passes through?
-
No, an INSERT statement does not block SELECT statements. SELECT statements can run simultaneously with INSERT statements in most databases. Upsers
- vsamantha35 wrote:
Thanks everyone for the explanation.But one thing bothering me,
Considering the below example, I am in read committed isolation level and assuming that my table has 100 rows inside it.
now, comes 2 spids. spid = 55 , started a trasanction and does 200 new INSERT's and the txn is still open. In the meanwhile, another spid = 56, tries to do a SELECT on the table. In this case, I would expect sql to return 100 rows which are committed and since other txn is still active. it doesnt show up the newly added 200 rows. in this case, does the SELECT gets blocked or passes through?
It depends. Let's say that the clustered index is on, oh, telephone number, including country & area codes. For a given phone, that would constitute a unique value. The 100 rows we're inserting includes a bunch of phone numbers scattered across the globe and regions. This means, lots of pages are being locked while the inserts occur. While the exclusive locks on the pages are held, reads do not occur, and yeah, massive interference between our two processes.
On the other hand, let's say the clustered index is on an identity column. All inserts are going to occur in the last page, and pages created after that. The reads are against everything EXCEPT the last page. Then, the reads go through fine. HOWEVER, let's say that the last page is also needed for the reads. Then we get blocked again.
There just isn't a 100%, perfectly neat, walkthrough on this stuff. It's down to the fundamental behaviors. What is being inserted, where, how much, that determines the number and type of locks being taken. Then, what is being read, where, how much, that determines the pages that may or may not be exclusively locked, therefore blocking the read process.
So, your bog standard average OLTP system that likely has identity columns as clustered keys will mostly have one, or a few, rows inserted into, or after, the last page on the index. Most of the reads are likely to be prior to that last page, and therefore won't suffer much (we're ignoring the effects of nonclustered indexes that I discussed earlier) so the reads go through fine.
Blocking and waits are a standard part of how SQL Server works. Trying to rage against them is futile. Instead, understand how they work, and write your code using that understanding.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Leticiagreen wrote:
No, an INSERT statement does not block SELECT statements. SELECT statements can run simultaneously with INSERT statements in most databases.
Apologies for being blunt, but that is simply not true.
Can we agree an INSERT statement requires an exclusive lock? Can we also agree that at least a page will be locked? Then, if that page is locked, exclusively, and the database is not in snapshot isolation mode, any reads against that exclusive lock, will be blocked? Right? If I'm wrong, please point it out.
Now, introduce snapshot isolation, so that a copy of the locked page(s) is available for reads, and the whole explanation shifts, dramatically. Then, the reads are (mostly) not blocked by the writes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Just to prove Grant's statement a little more, here's a quick demo.
So you can see that the first
SELECT, which wants rows on or after the current date, is not blocked, due to there being anINDEXonSomeDate(whichINCLUDEs all the columns the query needs), so the data engine is able to get all the rows without accessing the blocked data.For the second
INSERT, however, we're now inserting a row after the current date, and when I run theSELECTagain, it now can't complete, due to theINSERTblocking theSELECT. As soon as thatINSERTisCOMMITed, however, theSELECTis able to be completed.An
INSERTcan block aSELECT, but if they do depends on both what data is beingINSERTed, what data is beingSELECTed, and whatINDEXes the data engine has access to. There's not as simple as a "yes it (always) will" or "No it won't (ever) be".SQL from the animated gif:
--First Connection's batches
CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1) CONSTRAINT PK_SomeTable PRIMARY KEY,
SomeString varchar(10),
SomeDate date);
GO
INSERT INTO dbo.SomeTable (SomeString, SomeDate)
VALUES('abcd',GETDATE()),
('efgh',DATEADD(DAY,-2,GETDATE()));
GO
--Use a transaction to help simulate "simultaneous" statements
BEGIN TRANSACTION;
INSERT INTO dbo.SomeTable (SomeString, SomeDate)
VALUES('ijkl',DATEADD(DAY,-1,GETDATE()));
COMMIT;
GO
--Use a transaction to help simulate "simultaneous" statements
BEGIN TRANSACTION;
INSERT INTO dbo.SomeTable (SomeString, SomeDate)
VALUES('mnop',DATEADD(DAY,+1,GETDATE()));
COMMIT;
GO
DROP TABLE dbo.SomeTable;
GO
---------
--Second connection's batch
SELECT *
FROM dbo.SomeTable
WHERE SomeDate >= CONVERT(date,GETDATE());Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Just to prove Grant's statement a little more, here's a quick demo.
So you can see that the first
SELECT, which wants rows on or after the current date, is not blocked, due to there being anINDEXonSomeDate(whichINCLUDEs all the columns the query needs), so the data engine is able to get all the rows without accessing the blocked data.For the second
INSERT, however, we're now inserting a row after the current date, and when I run theSELECTagain, it now can't complete, due to theINSERTblocking theSELECT. As soon as thatINSERTisCOMMITed, however, theSELECTis able to be completed.An
INSERTcan block aSELECT, but if they do depends on both what data is beingINSERTed, what data is beingSELECTed, and whatINDEXes the data engine has access to. There's not as simple as a "yes it (always) will" or "No it won't (ever) be".SQL from the animated gif:
--First Connection's batches
CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1) CONSTRAINT PK_SomeTable PRIMARY KEY,
SomeString varchar(10),
SomeDate date);
GO
INSERT INTO dbo.SomeTable (SomeString, SomeDate)
VALUES('abcd',GETDATE()),
('efgh',DATEADD(DAY,-2,GETDATE()));
GO
--Use a transaction to help simulate "simultaneous" statements
BEGIN TRANSACTION;
INSERT INTO dbo.SomeTable (SomeString, SomeDate)
VALUES('ijkl',DATEADD(DAY,-1,GETDATE()));
COMMIT;
GO
--Use a transaction to help simulate "simultaneous" statements
BEGIN TRANSACTION;
INSERT INTO dbo.SomeTable (SomeString, SomeDate)
VALUES('mnop',DATEADD(DAY,+1,GETDATE()));
COMMIT;
GO
DROP TABLE dbo.SomeTable;
GO
---------
--Second connection's batch
SELECT *
FROM dbo.SomeTable
WHERE SomeDate >= CONVERT(date,GETDATE());Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Leticiagreen wrote:
No, an INSERT statement does not block SELECT statements. SELECT statements can run simultaneously with INSERT statements in most databases.
That's seriously true in Oracle (and some other databases) or if you have RCSI in place on SQL Server. It's not true for the "normal" stuff in SQL Server, where Inserts can and will block Selects that are trying to read from the same pages or need to scan the same pages.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Thom A wrote:
Just to prove Grant's statement a little more, here's a quick demo.
As a bit of a sidebar, what did you use as a screen recorder?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Thom A wrote:
Just to prove Grant's statement a little more, here's a quick demo.
As a bit of a sidebar, what did you use as a screen recorder?
On Windows, ShareX. I think I downloaded it from the Windows Store.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:Jeff Moden wrote:Thom A wrote:
Just to prove Grant's statement a little more, here's a quick demo.
As a bit of a sidebar, what did you use as a screen recorder?
On Windows, ShareX. I think I downloaded it from the Windows Store.
Thanks, Thom.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply