March 27, 2016 at 10:11 am
Hi all,
I am not sure if this is normal, i tried googling but have never found a similar thread in which a SELECT is block by an INSERT.
Please see my use case below ->
Session 1
select user_name(), db_name(), schema_name();
testdbuser, testdb, testschema
set implicit_transactions on
select @@OPTIONS & 2 -- show 2.
insert into testtab values ('j');
select * from testtab -- see 'j' row there
-- did not commit or rollback yet.
Session 2
-- login using sa account.
use testdb;
select user_name(), db_name(), schema_name();
dbo, testdb, dbo
select * from testdb.testschema.testtab; -- wait indefinitely until Session 1 commit or rollback
Why is the 2nd session SELECT being BLOCK by the uncommitted insert ? Is this the normal behavior for SQL SERVER when implicit_transactions is on ?
p.s. My isolation level is set as READ COMMITTED.
Regards,
Noob
March 27, 2016 at 11:54 am
Here's one way of looking at it (if you have time for a lot of heavy reading it might be useful to learn to understand it in terms of the various locks which are involved insted of in thee terms, but that's a read the books job, not something for a quick reply).
Session 1 can see the results so far of what it has done; it's inserted a row, if it looks it can see that row, even though it isn't yet committed. (That's usefule for several reasons, for example it enables it to check and see if what it's done is what it intended, before committing, if it wants to).
Session 2 can only see committed state (apart from uncommitted changes that it has begun itself). Soa ession 2 can't see that row, because it isn't yet committed and it's something some other session is doing, not itself. But it also can't see that the table doesn't contain that row, because the absence of that row is also not committed.
So it can't get a result which doesn't contain that row, and it can't get one that does contain it - so it can't get a result until it's known whether that row exists or not, which won't be until Session 1's transaction is completed by being committed or by being rolled back.
Tom
March 27, 2016 at 12:23 pm
Further on Tom's fine answer, the uncommitted insert statement will be holding Sch-M lock that prevents concurrent access to the table.
😎
Run this in session 1 to observe the locks held by the session
SELECT
-- resource_associated_entity_id can be either HOBT (BIGINT) or OBJECT_ID (INT)
-- use % 2147483647 to prevent arithmetic overflow error when passing the value
-- to the object_name function.
OBJECT_NAME(resource_associated_entity_id % 2147483647)
,*
FROM sys.dm_tran_locks TL
WHERE TL.REQUEST_SESSION_ID = @@SPID
AND TL.resource_type = 'OBJECT'
March 27, 2016 at 12:39 pm
TomThomson (3/27/2016)
Here's one way of looking at it (if you have time for a lot of heavy reading it might be useful to learn to understand it in terms of the various locks which are involved insted of in thee terms, but that's a read the books job, not something for a quick reply).Session 1 can see the results so far of what it has done; it's inserted a row, if it looks it can see that row, even though it isn't yet committed. (That's usefule for several reasons, for example it enables it to check and see if what it's done is what it intended, before committing, if it wants to).
Session 2 can only see committed state (apart from uncommitted changes that it has begun itself). Soa ession 2 can't see that row, because it isn't yet committed and it's something some other session is doing, not itself. But it also can't see that the table doesn't contain that row, because the absence of that row is also not committed.
So it can't get a result which doesn't contain that row, and it can't get one that does contain it - so it can't get a result until it's known whether that row exists or not, which won't be until Session 1's transaction is completed by being committed or by being rolled back.
Hi tom and eirikur,
Thanks for your replies.
Q1) so with that being said, can i say that this behavior (uncomitted insert blocking select ) is actually behaving as intended in sql server ?
Q2) not comparing with oracle though i must mention that in oracle - select are never blocked by DMLs , wouldnt an uncommitted insert in this case cause a major serialization in the table ? E.g. user update a row, went off without committing until he is back in ofc tml (meanwhile no one can select the table ) 😮
Regards,
Noob
March 27, 2016 at 2:50 pm
szejiekoh (3/27/2016)
Q1) so with that being said, can i say that this behavior (uncomitted insert blocking select ) is actually behaving as intended in sql server ?
Yes, this is a standard behaviour for SQL Server. But it is controlled not just by the READ_COMMITTED transaction isolation level, but also by another setting, the database option "READ_COMMITTED_SNAPSHOT"; the forced wait happens when READ_COMMITTED_SNAPSHOT is set OFF for the current database. If that option is ON, row versioning is used to determine whether it is possible to provide transactionally consistent versions of the database so that all transactions can see a consistent view in such a manner that session 2's transaction doesn't see the new row (so session 2 can't see any changes made by session 1, and session 1 can't see any data in modified by session 2's current transaction in the form it had before that modification).
Q2) not comparing with oracle though i must mention that in oracle - select are never blocked by DMLs , wouldnt an uncommitted insert in this case cause a major serialization in the table ? E.g. user update a row, went off without committing until he is back in ofc tml (meanwhile no one can select the table ).
Yes, that can happen (but of course people can see the table, unless they need to look at the page containing the uncommitted row). I think Oracle doesn't have the option of not READ COMMITTED SNAPSHOT, which is bad news for workloads that work better without it (there are some), it's not always a good thing.
Tom
March 27, 2016 at 9:40 pm
TomThomson (3/27/2016)
szejiekoh (3/27/2016)
Q1) so with that being said, can i say that this behavior (uncomitted insert blocking select ) is actually behaving as intended in sql server ?Yes, this is a standard behaviour for SQL Server. But it is controlled not just by the READ_COMMITTED transaction isolation level, but also by another setting, the database option "READ_COMMITTED_SNAPSHOT"; the forced wait happens when READ_COMMITTED_SNAPSHOT is set OFF for the current database. If that option is ON, row versioning is used to determine whether it is possible to provide transactionally consistent versions of the database so that all transactions can see a consistent view in such a manner that session 2's transaction doesn't see the new row (so session 2 can't see any changes made by session 1, and session 1 can't see any data in modified by session 2's current transaction in the form it had before that modification).
Q2) not comparing with oracle though i must mention that in oracle - select are never blocked by DMLs , wouldnt an uncommitted insert in this case cause a major serialization in the table ? E.g. user update a row, went off without committing until he is back in ofc tml (meanwhile no one can select the table ).
Yes, that can happen (but of course people can see the table, unless they need to look at the page containing the uncommitted row). I think Oracle doesn't have the option of not READ COMMITTED SNAPSHOT, which is bad news for workloads that work better without it (there are some), it's not always a good thing.
Hi Tom,
Thanks for sharing this. Just some last doubts to clarify with you -> on reading snapshot isolation, I came across this phrase.
Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change
which version (the original or the changed version) is marked with the XSN number ?
e.g. transaction 123 update rowA to some value
is the currentVersion of rowA marked with XSN123
or
is the originalVersion of rowA marked with XSN123 ?
"Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. The Database Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number."
How does SQL server determine when a row version is still needed and when its not ?
What does it means by "useful transaction"?
Regards,
Noob
March 28, 2016 at 1:07 pm
Warning: I'm basing my response on how a different compny planned to do it 15 or 16 years before Microsoft did it, because I haven't seen much detail on the Microsoft implementation. They had the benefit of more than a decade of additional research in various companies and universities, and although I tried to keep up with the current state of research during those years it's possible that they have spotted something I didn't and used it.
szejiekoh (3/27/2016)
Thanks for sharing this. Just some last doubts to clarify with you -> on reading snapshot isolation, I came across this phrase.Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change
which version (the original or the changed version) is marked with the XSN number ?
e.g. transaction 123 update rowA to some value
is the currentVersion of rowA marked with XSN123
or
is the originalVersion of rowA marked with XSN123 ?
The original version keeps its existing XSN number (or is assigned the lowest existing XSN number is it doesn't alrady have one) and is copied long with that to tempdb. The new row is assigned the transactions XSN number. "modified" in the passge you quoted means "modified version is committed committed", so the last XSN number assigned to a row is always assigned to the current committed version.
"Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. The Database Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number."
How does SQL server determine when a row version is still needed and when its not ?
What does it means by "useful transaction"?
The transaction sequence number is still useful if there is at least one row version for which it is the sequence number and at least one current transaction for which it is the less than the transaction's sequence number and has no version with a higher sequence number which is less than the that transaction's sequence number. In other words if for some row it's the newest version earlier than the sequence number for some cuurent transaction. "needed" and "useful" hve the sme mening in that passage - a row version is needed if it's useful (or of course if it's the current committed version, but "needed" isn't generally used to refer to current versions, only to older versions).
Tom
March 30, 2016 at 2:24 am
Hi Tom,
Thanks for your explanation and sorry for the late reply. Wanted some dedication time to this thread.
The original version keeps its existing XSN number (or is assigned the lowest existing XSN number is it doesn't alrady have one) and is copied long with that to tempdb. The new row is assigned the transactions XSN number. "modified" in the passge you quoted means "modified version is committed committed", so the last XSN number assigned to a row is always assigned to the current committed version.
So do you mean, the latest XSN number is always assigned to the current row-in-change ?
The transaction sequence number is still useful if there is at least one row version for which it is the sequence number and at least one current transaction for which it is the less than the transaction's sequence number and has no version with a higher sequence number which is less than the that transaction's sequence number. In other words if for some row it's the newest version earlier than the sequence number for some cuurent transaction. "needed" and "useful" hve the sme mening in that passage - a row version is needed if it's useful (or of course if it's the current committed version, but "needed" isn't generally used to refer to current versions, only to older versions)
Sorry, can you elaborate with an illustration ? I cant figure past this phrase
and at least one current transaction for which it is the less than the transaction's sequence number and has no version with a higher sequence number which is less than the that transaction's sequence number.
:crying:
Regards,
Noob
March 30, 2016 at 10:56 am
szejiekoh (3/30/2016)
Hi Tom,Thanks for your explanation and sorry for the late reply. Wanted some dedication time to this thread.
The original version keeps its existing XSN number (or is assigned the lowest existing XSN number is it doesn't alrady have one) and is copied long with that to tempdb. The new row is assigned the transactions XSN number. "modified" in the passge you quoted means "modified version is committed committed", so the last XSN number assigned to a row is always assigned to the current committed version.
So do you mean, the latest XSN number is always assigned to the current row-in-change ?
I don't know what "row-in-change" means. Version nunbers are ssigned only to committed values, I believe. The latest \xSn number for a version of a row is the version number for the current (ie most recent) committed value. The XSN number assigned to a row version on commitment of a change is the XSN which was assigned to the committing transaction
when it first accessed the database (or possibly an XSN aassigned when a BEGIN TRANSACTION statement was issued for for a top-level transaction (nested transactions don't commit, despite ssuing commit commands, and don't have XSNs, although they do sometimes initiate roll back).
The transaction sequence number is still useful if there is at least one row version for which it is the sequence number and at least one current transaction for which it is the less than the transaction's sequence number and has no version with a higher sequence number which is less than the that transaction's sequence number. In other words if for some row it's the newest version earlier than the sequence number for some cuurent transaction. "needed" and "useful" hve the sme meaning in that passage - a row version is needed if it's useful (or of course if it's the current committed version, but "needed" isn't generally used to refer to current versions, only to older versions)
Sorry, can you elaborate with an illustration ? I cant figure past this phrase
and at least one current transaction for which it is the less than the transaction's sequence number and has no version with a higher sequence number which is less than the that transaction's sequence number.
OK, suppose a row has versions with XSNs 8, 11, 15 and 19 (19 being the XSN for the current committed verson or the row). If there is a current (uncommitted) transaction which has XSN 9 or XSN 10 the version of that row with XSN 8 is needed/useful because 8 is the highest XSN for that row which is lower than the XSN for that transaction. If there is also a current transaction with XSN 16, that row's version with XSN 15 is also needed because that was the state of the row when that transaction started. The version with XSN 19 is useful/needed because it is the current committed version. If there are no current transactions with XSN 12, 13, or 14 then the version with XSN 11 is not useful or needed because there is no transction for which 11 is the highest XSN for that row which is less than the transaction's XSN. We know there are no current transactions with XSN 8,11,15, or 19 because transactions with those XSNs have committed (that's how row-versions with those XSNs came about) so they have finished and are no longer current.
Tom
March 30, 2016 at 11:36 am
the version numbers are strictly internal management mechanisms for SQL Server. They're not something you need to worry about under normal conditions. Why are you so focused on this level of the internals? Most systems are never going to care about this unless they have a very high degree of conflict all focused on UPDATES and all focused on multiple sessions attempting to update the exact same row of data.
For some detail on the topic, I'd suggest getting a copy of Kalen Delaney's SQL Server Internals.
The rows are only assigned a version when changes are occurring. Otherwise, the data is just stored on disk. The row being changed (update or delete) is stamped with the transaction number (XSN) exactly as Tom already said.
"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
March 30, 2016 at 1:54 pm
I think this explains the behavior fairly well:
March 30, 2016 at 9:36 pm
Hi Tom,
Ahh.. I think I got you..
So XSN numbers are assigned to transactions (e.g. BEGIN transactions), but they are not assign as part of the row version until commit.
Right ?
Regards,
Noob
March 30, 2016 at 9:39 pm
Grant Fritchey (3/30/2016)
the version numbers are strictly internal management mechanisms for SQL Server. They're not something you need to worry about under normal conditions. Why are you so focused on this level of the internals? Most systems are never going to care about this unless they have a very high degree of conflict all focused on UPDATES and all focused on multiple sessions attempting to update the exact same row of data.For some detail on the topic, I'd suggest getting a copy of Kalen Delaney's SQL Server Internals.
The rows are only assigned a version when changes are occurring. Otherwise, the data is just stored on disk. The row being changed (update or delete) is stamped with the transaction number (XSN) exactly as Tom already said.
Hi Grant,
Not sure why also, I just got skeptical when committed data (though its previous versions) now are store in temp. Didn't want temp to grow bigger (just worried if there might be some scenarios that will keep previous versions of committed rows stored in temp without releasing them)
But I am not at that level yet to think about this though 😀
Regards,
Noob
March 31, 2016 at 8:22 am
szejiekoh (3/30/2016)
I just got skeptical when committed data (though its previous versions) now are store in temp. Didn't want temp to grow bigger (just worried if there might be some scenarios that will keep previous versions of committed rows stored in temp without releasing them)
Those worries are justified. Using any of the snapshot isolation levels will increase the load on tempdb. And if you have very long-running transaction (or orphaned transaction that are idle but someone has forgotten to close), then this can become an issue, especially if you have a lot of changes in your database.
Before enabling snapshot isolation in your production database, I recommend testing it on a dev server with a realistic load to see how much additional load on tempdb this causes, so that you can ensure that prod's tempdb is large enough (and uses enough files) to handle the load.
March 31, 2016 at 8:55 am
szejiekoh (3/30/2016)
Hi Tom,Ahh.. I think I got you..
So XSN numbers are assigned to transactions (e.g. BEGIN transactions), but they are not assign as part of the row version until commit.
Right ?
Regards,
Noob
Yes, that's right.
Tom
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply