February 22, 2011 at 6:39 pm
Hi,
As we know read commited is on by default and which means It prevents transactions to read data if some other transaction is doing some update operation on the data
now suppose we got a table with million records now we are updating one row in table and select that table in parallel
for example
if we doing
update table A
set row =1 where row=2
select * from table A
question 1:
can we run update and select statements parallely as both are on the both table and read commited enabled
Question 2:
suppose if update transaction is done while select is in between ..will select gets the updated record or the old records
February 23, 2011 at 6:09 am
praveensatya (2/22/2011)
Hi,As we know read commited is on by default and which means It prevents transactions to read data if some other transaction is doing some update operation on the data
now suppose we got a table with million records now we are updating one row in table and select that table in parallel
for example
if we doing
update table A
set row =1 where row=2
select * from table A
question 1:
can we run update and select statements parallely as both are on the both table and read commited enable
If those two statement were done as you wrote them from a single connecion, the UPDATE would complete and then the SELECT would run. But let's assume they're from two different connections. Then what happens depends on a lot of things. Let's assume that it's exactly as written, but that there's an index on the column in the WHERE cluase of the UPDATE. The UPDATE statement is going to do a nice neat index seek to get to the value it needs to update and it's going to update it. But the SELECT statement is going to be doing a table scan because it doesn't have a WHERE clause. That means it will probably have to wait until the UPDATE completes. But let's put a WHERE cluase on the SELECT statement such that 'WHERE row=722.' Now, each process does a discrete SEEK operation and neither interferes with the other. They can both occur simultaneously, no problem.
Question 2:
suppose if update transaction is done while select is in between ..will select gets the updated record or the old records
With READ COMMITTED isolation level, it means exactly that, it will only get the committed records. Further, as I said before, if the transaction is in process, the SELECT will have to wait until the transaction is completed, depending on how the locking occurred, it might get to read first and make the UPDATE statement wait, in which case, it'll get the old data, not the new data.
"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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply