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.
Best Answer
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
isON
. To prove this, run the following statements that create and populate a table in a query window in SSMS:Running the above code, you'll see this output:
Now, in a second SSMS query window, run the following code:
Running the above code, you'll see two sets of results:
In the above output, you can see the first resultset indicates the row does exist in
dbo.t
. Prior to the second resultset, theDELETE 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 automaticROLLBACK TRANSACTION
due to the lack of an explicitCOMMIT TRANSACTION
statement, in combination withSET IMPLICIT_TRANSACTIONS ON;
.Now open a third query window in SSMS, and run the following code:
You'll see the following output indicating the row still exists in
dbo.t
.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 theDELETE FROM
.Assuming you still don't believe the row is actually deleted by the
DELETE FROM dbo.t
statement, lets add the output fromDBCC 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 detailsThe 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:
You can see in the output above the
Record Type
isPRIMARY_RECORD
, and Slot0
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:
The output clearly shows that the
Record Type
for slot0
isGHOST_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 them_lsn
value from the DBCC PAGE output:The output shows the delete has been recorded in the transaction log:
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 ofLCX_MARK_AS_GHOST
, which indicates SQL Server has recorded theDELETE
operation in the transaction log.