if we execute a update statement and a delete statement parallel in sql server 2008 having isolation level at Read committed. What will be the output -

if transaction having multiple select statements feteching data table , parallel query runs deleting set of records fetched in previoius transaction. result of select statement within transaction executes after delete query.

isolation level set read committed.

begin trans  select * mytable id >100 , id <1000  //while loop //some time taking operation. here parallel delete query executes   select * mytable id >100 , id <1000 //**what output of query ?**  commit trans  --------  //some other delete query delete mytable id > 500 , id <1000 

since rci holds locks duration of statement it's going depend on timing. assuming order have , delete happens in rci , in own transaction first select bring of data, delete starts , other select starts. deadlock (depending) 1 of statements going locks granted first , other wait until can acquire locks. it's timing problem @ point.

if run these in tight enough loop you'll hit interesting results. if wanted more stable return value set use snapshot isolation.