What can cause what appears to be missing data in a single table [duplicate]

Question

I have a situation where it appears as if I've lost data in a single table.

I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the database, for debug and support purposes. Other than the request and response, the logging table also contains an IDENTITY(1,1) column (which is the primary key) and other various columns.

Over a duration of about 15 minutes, it appears as if 2798 rows have "disappeared" from the table. The primary key simply jumps from 3561297 to 3564095.

During the same time period, I've got debug logs from clients of this API which indicates that something went wrong during the request. My IIS log notes that any request which causes database access during this time period had taken between 30000 and 40000 ms to complete.

The odd thing is, that other tables does not appear to have similar gaps. In fact other tables have had data inserted and updated in them, without issue it appears. So it doesn't appear to be a full system crash.

Through my Google-Fu I've come across the fact that gaps in a IDENTITY(1,1) key can be caused by a restart of the server -- and in that case the gap is roughly 1000. However, in this case I'm 100% certain that the database has not been restarted, yet I have this gap.

I've tried reproducing this error by inserting rows and adding a THROW statement in the end, in my test environment to no avail. I've tried to simulate timeouts by both adding a WAITFOR DELAY and making a TABLOCKX, HOLDLOCK on the table, while trying to do a lot of inserts through the application. Every database operation is done through stored procedures btw.

I'm quite frankly stumped. Does anyone have any ideas / hints as to why I've got this apparent data loss in one table? Or what can cause a massive gap in the primary key like this?

As far as I can tell I have no transactions in the logging middlewares SQL. And IMPLICIT_TRANSACTIONS is OFF on the server, and I'm not using XACT_ABORT in the query. How can a transaction rollback occur, if I do not execute a BEGIN TRANSACTION? I thought SQL Server only made "auto transactions" if IMPLICIT_TRANSACTIONS was set to ON?

Answer 1

Over a duration of about 15 minutes, it appears as if 2798 rows have "disappeared" from the table. The primary key simply jumps from 3561297 to 3564095. During the same time period, I've got debug logs from clients of this API which indicates that something went wrong during the request. My IIS log notes that any request which causes database access during this time period had taken between 30000 and 40000 ms to complete.

I don't think the rows have disappeared.

Your debug logs show that something went wrong during requests to the database when the gaps happened. If queries are aborted & rolled back the identity values will not roll back with it.

Another example is that the application has a timeout window of 30 seconds. Queries that take longer than that will be cancelled and the insert will be rolled back.

As mentioned earlier, in the case of rollback, the identity value allocated will not be rolled back. Instead, the next identity value in line will be used when retrying the insert.

There could have been other reasons that the insert was failing, such as network loss, inserting values that would be truncated, wrong datatype conversions, ....

Below is a reproducible example that shows two gaps:

CREATE TABLE dbo.identitytable(id int identity(1,1), val varchar(255))
INSERT INTO dbo.identitytable(val)
VALUES('BLA1');

GO
BEGIN TRAN
INSERT INTO dbo.identitytable(val)
VALUES('BLA2');

ROLLBACK TRAN

GO

INSERT INTO dbo.identitytable(val)
VALUES('BLA3');

GO

SELECT * FROM dbo.identitytable;

GO

INSERT INTO dbo.identitytable(val)
VALUES(REPLICATE('B',256));
GO

INSERT INTO dbo.identitytable(val)
VALUES('BLA5');


SELECT * FROM dbo.identitytable;

With the last select producing a result with two gaps:

id  val
1   BLA1
3   BLA3
5   BLA5

If an insert with multiple rows is rolled back then the gap will be bigger too:

BEGIN TRAN
INSERT INTO dbo.identitytable(val)
SELECT 'BLA6'
FROM master..spt_values;
ROLLBACK TRAN


INSERT INTO dbo.identitytable(val)
VALUES('BLA7');



SELECT * FROM dbo.identitytable;

Result

id  val
1   BLA1
3   BLA3
5   BLA5
2546    BLA7

DB<>Fiddle

But how can a transaction rollback occur, if I do not execute a BEGIN TRANSACTION? I thought TSQL only made "auto transactions" if IMPLICIT_TRANSACTIONS was set to ON?

Queries without BEGIN TRANSACTION or without the "bad idea" IMPLICIT_TRANSACTION setting enabled are auto-committed transactions in itself.

When canceling/aborting the modification of data due to the reasons specified above, undoing these changes have to be done at the database level. This is done via a rollback. If this did not exist, your data would be in an incorrect state.

Answer 2

But how can a transaction rollback occur, if I do not execute a BEGIN TRANSACTION? I thought TSQL only made "auto transactions" if IMPLICIT_TRANSACTIONS was set to ON

There is no need of explicit transaction to have a rollback, for example, any constraint violation would rollback the entire insert, here is a small repro:

if object_id('dbo.t') is not null drop table dbto.t;
go

create table dbo.t(id int identity, col1 int not null);
insert into dbo.t (col1) values(1);
go

insert into dbo.t(col1)
select top 2796 row_number() over(order by getdate())
from sys.columns c cross join sys.columns c1
union all select null;
go

insert into dbo.t (col1) values(2);
go

select *
from dbo.t;

I have not nullable column and the 2796th record of my insertcause the rollback, this way you find 2798 rows "disappeared"