June 23, 2016 at 12:46 am
hi can any one explain when one update statement access a record and select statement want to access the same record , lets assume update takes 10 min(till not committed). what will happen to select statement. select statement can access previous image of data or waiting for new update.
previous image of the record
111 , 3344,4,gghghg,5
update image of the record
111,5555,ghhh,6
thanks
June 23, 2016 at 1:07 am
What is the isolation level?
Do you use any hints (e.g. NOLOCK)?
Igor Micev,My blog: www.igormicev.com
June 23, 2016 at 1:09 am
no just a general question . what happen to select query?
June 23, 2016 at 1:13 am
read committed
June 23, 2016 at 1:23 am
ashwan (6/23/2016)
no just a general question . what happen to select query?
It won't return the currently updating data if it's selected by the SELECT query.
If you use hint (NOLOCK) for e.g., then you will have that dirty data in the select result, but as the transaction is not yet committed, it's a dirty data.
Igor Micev,My blog: www.igormicev.com
June 23, 2016 at 1:25 am
If isolation level is read committed and you are not using any query hint then SELECT statement would be blocked by the UPDATE statement. You can see the blocking using a variety of ways (sp_who2, DMVs, custom queries)
June 23, 2016 at 1:31 am
my question is what is that select statement return a value old image? or waiting for response
June 23, 2016 at 1:45 am
In default isolation level with none of the snapshot isolation options turned on, it will wait for the update to finish and then return the new value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2016 at 1:51 am
let say there is a DB with large update frequently . In this situation all select statements are waiting for new update. ? why Select statements do not return with old values until all updates are committed?
June 23, 2016 at 2:03 am
ashwan (6/23/2016)
let say there is a DB with large update frequently . In this situation all select statements are waiting for new update. ?
No, the lock manager's smart, the selects and updates will be granted locks in order of requesting them. The selects will wait for updates and later updates will wait for the selects to complete.
why Select statements do not return with old values until all updates are committed?
Because the default isolation level does not allow that. They must wait.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2016 at 2:15 am
if isolation level set to (read uncommitted) can we see previous image of the (before update)
June 23, 2016 at 2:27 am
ashwan (6/23/2016)
let say there is a DB with large update frequently . In this situation all select statements are waiting for new update. ? why Select statements do not return with old values until all updates are committed?
You can have the old values using the NOLOCK hint, but consider reading dirty data.
Igor Micev,My blog: www.igormicev.com
June 23, 2016 at 2:30 am
ashwan (6/23/2016)
if isolation level set to (read uncommitted) can we see previous image of the (before update)
It's the same as using hint (NOLOCK). You can place it for desired number of sql statements in your session, while the read committed level will be valid for the all statements in the same.
Igor Micev,My blog: www.igormicev.com
June 23, 2016 at 2:33 am
if change default isolation = read uncommitted . what will happen
June 23, 2016 at 3:10 am
Then select won't take locks and will read whatever value is there at the point it runs, might be old value, might be new, no way to tell, just depends on whether read starts before update changes value or not.
If the select is reading multiple rows, it can read the before value from some rows and the after from other rows.
And you can't change the default isolation to read uncommitted. The default isolation is read committed. You can choose to change your session's isolation level, that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply