Will delete from inside transaction remove records from after transaction

Question

I have a quick question for you... If I ran the following command:

BEGIN TRANSACTION
 <commands...>
 DELETE FROM <table>
COMMIT TRANSACTION

And while the above transaction is running an insert is carried out on the table. Will the delete:

1. remove the data added after the transaction started
2. only remove data that existed at the start of the transaction or that was added as part of the transaction

Hope someone can help.

Answer 1

I assume that your are running your code in one SPID and the insert will run on other SPID and the isolation level is the default one in SQL SERVER - READ COMMITTED.

Shortly, the answer is NO, as INSERT will wait for the DELETE to end. Tested like this:

1) Setup:

-- drop table dbo.Test
CREATE TABLE dbo.Test
(
    Id INT NOT NULL,
    Value NVARCHAR(4000)
)
GO

INSERT INTO Test (Id, Value) 
SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)), text
from sys.messages
GO

2) In query window 1

BEGIN TRANSACTION
DELETE FROM dbo.Test where ID > 100000

3) In query window 2

INSERT INTO Test (Id, Value) 
SELECT 100000000 + ROW_NUMBER() OVER (ORDER BY (SELECT 1)), text
from sys.messages

sp_who2 active shows that second query (SPID) is blocked by first query, so query is waiting to get lock

3) In query window 1

COMMIT -- query 1 will finish

4) Second query will finish

So, INSERT has to wait until DELETE finishes.