1

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
denimknight
  • 301
  • 2
  • 9
  • 22

3 Answers3

3

You need to dive more to the Locks and Transaction Isolation Levels topic. Look at this example, which may be more common than in the previous answer. INSERT is not blocked here because DELETE just locks set of Keys for a DELETE operation.

And anyway, before DELETE operation start, if other queries in this transaction are not holding locks on this table, there is no reason for SQL Server to prevent INSERT operations from other transaction.

CREATE TABLE t (Id int PRIMARY KEY)
GO

INSERT INTO t VALUES(1)
GO

BEGIN TRAN

DELETE FROM t

-- separate window
INSERT INTO t VALUES(2)
Denis Reznik
  • 964
  • 5
  • 10
2

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.

Community
  • 1
  • 1
Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
  • That's great. Thanks for the info/ – denimknight Dec 24 '15 at 12:45
  • 2
    This answer is incorrect. This INSERT uses SELECT as a source, and as it uses SELECT it will use Shared locks for reads, so this is the reason why INSERT waits. And more, this example assume that table doesn't have primary key, so all table will be locked for the delete operation, and this is not true for a more common case - table with Primary Key. – Denis Reznik Dec 24 '15 at 12:59
  • As I undestood, denimknight wants to know if the INSERT might introduce data while DELETE is happening. As it is stated in MS docs for DELETE, it will lock the table: By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. (https://msdn.microsoft.com/en-us/library/ms189835.aspx - Locking Behavior) – Alexei - check Codidact Dec 24 '15 at 14:30
0
  1. Yes. Why not? If other queries in your transaction will not hold locks on a your_table, SQL Server will start locking your_table with Update locks, just after DELETE operation start. So, before this action, all other processes can successfully add new rows to the table.
  2. DELETE in your case will delete all committed data, existed in your table before DELETE operation. Uncommitted data of this transaction will be deleted also.
Denis Reznik
  • 964
  • 5
  • 10