Delete query not deleting data

Question

I am running a web service which connects to a SQL Server 2012 database. When executing multiple delete queries within a short space of time (around 5 in a second), it appears random which ones are actually executed. When ran one at a time or putting a 0.5 second delay between each execution, they run perfectly.

Looking at the SQL profiler, all the query are showing as RPC:Completed even if they haven't actually deleted the row in the table. I checked the table and the data was still there, then copied and pasted the query from the Profiler into SSMS and that affected one row and delete it.

So I assume the web service is working fine and the problem is on the database side of it. Is there a way in the profiler to view if the query was a success? And what could be causing this to actually not affect the row?

No triggers. Running the same query just with different parameters. Only the data is changing usually sequentially. No other queries are running on the database just now.

I've added client site logging, it does actually return 1 if it deletes the row and 0 if it doesn't delete the row. However even when it comes up as 0, the Profiler shows it has ran the query but doesn't seem to have affected it. And when I run the query through SSMS, it does affect the row.

Not receiving any errors from the web service and the query runs fine through SSMS. Only seems to not delete when it is ran multiple times in quick succession. I do agree it is most likely targeting different rows but can't see how that happens when it runs okay in SSMS.

table structure and query

CREATE TABLE dbo.ContractDates2HumanAssets
(
    iContractDate2HumanAssetID int IDENTITY(1,1) NOT NULL,
    iContractDateID int NOT NULL,
    iHumanAssetID int NOT NULL,
    cCategory nvarchar(256) NOT NULL,
    cHR_x0020_ID nvarchar(256) NOT NULL,
    cCD_x0020_ID nvarchar(256) NOT NULL,
     CONSTRAINT PK_ContractDates2HumanAssets PRIMARY KEY CLUSTERED 
    (
        iContractDate2HumanAssetID ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

CREATE TABLE dbo.ContractDates
(
    iContractDateID int IDENTITY(1,1) NOT NULL,
    iContractID int NOT NULL,
    dDate datetime NOT NULL,
    cResource nvarchar(256) NOT NULL,
    cCD_x0020_ID nvarchar(256) NOT NULL,
    CONSTRAINT PK_ContractDates PRIMARY KEY CLUSTERED 
    (
        iContractDateID ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

exec sp_executesql N'DELETE FROM ContractDates2HumanAssets 
WHERE iContractDateID IN ((SELECT ContractDates.iContractDateID 
FROM ContractDates2HumanAssets 
INNER JOIN ContractDates ON ContractDates.iContractDateID = ContractDates2HumanAssets.iContractDateID
WHERE iHumanAssetID = @humanid AND iContractID = (SELECT iContractID FROM Contracts WHERE cContractNo = @id) AND dDate = @newDate))',N'@id nvarchar(6),@newDate nvarchar(10),@humanid int',@id=N'999111',@newDate=N'2018-03-16',@humanid=82

That is the query which is appearing in the profiler. After checking the table that row is still there. But copying and pasting that into SSMS and executing it, it comes back with 1 row affected and is deleted.

Table data

SELECT * 
FROM ContractDates2HumanAssets 
WHERE iContractDateID IN
(
    (
        SELECT ContractDates.iContractDateID 
        FROM ContractDates2HumanAssets 
        INNER JOIN ContractDates 
            ON ContractDates.iContractDateID = ContractDates2HumanAssets.iContractDateID 
        WHERE 
            iHumanAssetID = 82 
            AND iContractID = 
            (
                SELECT iContractID 
                FROM Contracts 
                WHERE cContractNo = N'999111'
            )
    )
);

The results:

╔════════════════════════════╦═════════════════╦═══════════════╦═══════════╦══════════════╦══════════════╗
║ iContractDate2HumanAssetID ║ iContractDateID ║ iHumanAssetID ║ cCategory ║ cHR_x0020_ID ║ cCD_x0020_ID ║
╠════════════════════════════╬═════════════════╬═══════════════╬═══════════╬══════════════╬══════════════╣
║                     102538 ║          113369 ║            82 ║           ║              ║              ║
║                     102539 ║          113370 ║            82 ║           ║              ║              ║
║                     102540 ║          113371 ║            82 ║           ║              ║              ║
║                     102541 ║          113372 ║            82 ║           ║              ║              ║
║                     102542 ║          113373 ║            82 ║           ║              ║              ║
║                     102543 ║          113374 ║            82 ║           ║              ║              ║
║                     102544 ║          113375 ║            82 ║           ║              ║              ║
║                     102545 ║          113376 ║            82 ║           ║              ║              ║
║                     102546 ║          113377 ║            82 ║           ║              ║              ║
║                     102547 ║          113378 ║            82 ║           ║              ║              ║
║                     102548 ║          113379 ║            82 ║           ║              ║              ║
║                     102549 ║          113380 ║            82 ║           ║              ║              ║
║                     102550 ║          113381 ║            82 ║           ║              ║              ║
║                     102551 ║          113382 ║            82 ║           ║              ║              ║
║                     102552 ║          113383 ║            82 ║           ║              ║              ║
║                     102553 ║          113384 ║            82 ║           ║              ║              ║
╚════════════════════════════╩═════════════════╩═══════════════╩═══════════╩══════════════╩══════════════╝

I ran that query to get all the relevant data, I can get the individual data per table if that is more helpful.

Answer 1

SQL Server never randomly chooses to delete or not delete a row. When you send a valid DELETE statement to SQL Server, it executes it. Guaranteed. Period. If there is an error in the statement, SQL Server will return an error. Are you seeing errors?

The far more likely problem is the query itself is not targeting the rows you think it is, or those rows don't exist. Show your table structure, along with the actual delete statements.

Robert Rodriguez' answer below, which I upvoted back when the answer was posted, talks about the impact of implicit transactions, which may well be the cause of your issue. SQL Server most certainly does delete rows, even when SET IMPLICIT_TRANSACTIONS is ON. To prove this, run the following statements that create and populate a table in a query window in SSMS:

USE tempdb;

DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t
(
    i int NOT NULL
        CONSTRAINT t_pk
        PRIMARY KEY
        CLUSTERED
);
GO

INSERT INTO dbo.t (i)
VALUES (1);

SELECT *
FROM dbo.t;
GO

Running the above code, you'll see this output:

╔═══╗
║ i ║
╠═══╣
║ 1 ║
╚═══╝

Now, in a second SSMS query window, run the following code:

USE tempdb;
/*********************************
* TURN ON IMPLICIT TRANSACTIONS  *
*********************************/
SET IMPLICIT_TRANSACTIONS ON;

SELECT [State] = 'PRE DELETE'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)

DELETE 
FROM dbo.t
WHERE dbo.t.i = 1;

SELECT [State] = 'POST DELETE'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)
GO

--DISCONNECT the session after you run the above code

Running the above code, you'll see two sets of results:

╔════════════╦═════════════╦═══════════════════════╦════════════════════════╗
║   State    ║ @@TRANCOUNT ║ IMPLICIT_TRANSACTIONS ║ Count of Rows in dbo.t ║
╠════════════╬═════════════╬═══════════════════════╬════════════════════════╣
║ PRE DELETE ║           1 ║ ON                    ║                      1 ║
╚════════════╩═════════════╩═══════════════════════╩════════════════════════╝

╔═════════════╦═════════════╦═══════════════════════╦════════════════════════╗
║    State    ║ @@TRANCOUNT ║ IMPLICIT_TRANSACTIONS ║ Count of Rows in dbo.t ║
╠═════════════╬═════════════╬═══════════════════════╬════════════════════════╣
║ POST DELETE ║           1 ║ ON                    ║                      0 ║
╚═════════════╩═════════════╩═══════════════════════╩════════════════════════╝

In the above output, you can see the first resultset indicates the row does exist in dbo.t. Prior to the second resultset, the DELETE FROM dbo.t statement executes. In the second resultset you can see SQL Server has in fact deleted the row. Ensure you now disconnect the session above, which will initiate an automatic ROLLBACK TRANSACTION due to the lack of an explicit COMMIT TRANSACTION statement, in combination with SET IMPLICIT_TRANSACTIONS ON;.

Now open a third query window in SSMS, and run the following code:

SELECT [State] = 'POST ROLLBACK'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t);

You'll see the following output indicating the row still exists in dbo.t.

╔═══════════════╦═════════════╦═══════════════════════╦════════════════════════╗
║     State     ║ @@TRANCOUNT ║ IMPLICIT_TRANSACTIONS ║ Count of Rows in dbo.t ║
╠═══════════════╬═════════════╬═══════════════════════╬════════════════════════╣
║ POST ROLLBACK ║           0 ║ OFF                   ║                      1 ║
╚═══════════════╩═════════════╩═══════════════════════╩════════════════════════╝

Of course, the row exists in the table, due to the implicit transaction being rolled back, exactly as if you'd issued a ROLLBACK TRANSACTION directly after the DELETE FROM.

Assuming you still don't believe the row is actually deleted by the DELETE FROM dbo.t statement, lets add the output from DBCC PAGE for the table in question to the second set of code, and re-run it. The code below is taken from my blog post on using DBCC PAGE to view row details

USE tempdb;
SET IMPLICIT_TRANSACTIONS ON;

SELECT [State] = 'PRE DELETE'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)


DBCC TRACEON(3604) WITH NO_INFOMSGS;
DECLARE @dbid int = DB_ID();
DECLARE @fileid int;
DECLARE @pageid int;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
FROM sys.schemas s  
    INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa
WHERE o.name = N't'
    AND s.name = N'dbo'
    AND dpa.page_type_desc = N'DATA_PAGE';
OPEN cur;
FETCH NEXT FROM cur INTO @fileid, @pageid;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (@dbid, @fileid, @pageid, 3);
    FETCH NEXT FROM cur INTO @fileid, @pageid;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF(3604);

DELETE 
FROM dbo.t
WHERE dbo.t.i = 1;

DBCC TRACEON(3604) WITH NO_INFOMSGS;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
FROM sys.schemas s  
    INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa
WHERE o.name = N't'
    AND s.name = N'dbo'
    AND dpa.page_type_desc = N'DATA_PAGE';
OPEN cur;
FETCH NEXT FROM cur INTO @fileid, @pageid;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (@dbid, @fileid, @pageid, 3);
    FETCH NEXT FROM cur INTO @fileid, @pageid;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF(3604);

SELECT [State] = 'POST DELETE'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)
GO

The resultsets look exactly the same as in the second set above, however if we look at the "Messages" tab, we'll see the output from running DBCC PAGE before and after the delete. The output from the first DBCC PAGE prior to the delete, and with the header section removed, looks like:

.
.
.
Slot 0 Offset 0x6b Length 11

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 11

Memory Dump @0x000000B76B27A06B

0000000000000000:   10000800 01000000 010000                      ...........

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

i = 1                               

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)       

You can see in the output above the Record Type is PRIMARY_RECORD, and Slot 0 contains the value: 1. This indicates the row exists in the table and has the value we expect.

The output from DBCC PAGE, post delete, with the same header section removed, looks like:

Slot 0 Offset 0x6b Length 11

Record Type = GHOST_DATA_RECORD     Record Attributes =  NULL_BITMAP    Record Size = 11

Memory Dump @0x000000B76B27A06B

0000000000000000:   1c000800 01000000 010000                      ...........

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

i = 1                               

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)       

The output clearly shows that the Record Type for slot 0 is GHOST_RECORD, which indicates the slot (row) has been deleted.

We can also check the transaction log via the undocumented system function, sys.fn_dblog, using the m_lsn value from the DBCC PAGE output:

SELECT *
FROM sys.fn_dblog('102:281659:60', '102:281659:60');

The output shows the delete has been recorded in the transaction log:

╔════════════════════════╦═════════════════╦═══════════════════╦════════════════╗
║      Current LSN       ║    Operation    ║      Context      ║ Transaction ID ║
╠════════════════════════╬═════════════════╬═══════════════════╬════════════════╣
║ 00000066:00044c3b:003c ║ LOP_DELETE_ROWS ║ LCX_MARK_AS_GHOST ║ 0000:00160373  ║
╚════════════════════════╩═════════════════╩═══════════════════╩════════════════╝

I've only got the first several columns of the output being shown here for brevity, but you can clearly see the LOP_DELETE_ROWS operation, with a context of LCX_MARK_AS_GHOST, which indicates SQL Server has recorded the DELETE operation in the transaction log.

Answer 2

There is a possibility the web service code is turning on implicit transactions.

As an example of how this works, I've created a simple table in tempdb, inserted a row, turned on implicit transactions, and deleted the row:

USE tempdb;

CREATE TABLE dbo.t
(
    someval int
);

INSERT INTO dbo.t (someval)
VALUES (1);

SET IMPLICIT_TRANSACTIONS ON;
DELETE FROM dbo.t;

If I now disconnect and re-connect the session, then run this code:

USE tempdb;
SELECT *
FROM dbo.t

I see the row is still in the table. This fits the description of what you're seeing pretty well. You'll need to check the webservice code for this, or look at a trace to see if this option is in effect.

If you're interested, you can upvote this Not Connect item to ask Microsoft to provide an easier method of determining if implicit transactions are enabled.

Answer 3

Have you checked to see if you are getting deadlocks?

My background is in Sybase but SQL Server should be pretty similar. The client message handler (this would be in the web service) gets message #1205 for a deadlock. In addition, the deadlock ought to be logged in the SQL Server log.

Two sessions would have to be contending for two locks to cause the deadlock. I could see a web service using a pool of connections, resulting in more than one session. Contending for two locks is pretty easy to accomplish. One lock could be on a data page and another on an index page.