How to check why insert to the SQL Server isn't executed atomically?

Question

My case is very weird and specific. I thought this behavior is impossible.
I use SQL Server 2019.
When I do an insert from one specific machine and it fails it inserts the line partially. For all the other machines even if the same error occurs I don't have any problems.
It's not a multiple-row insert, just one line.

INSERT INTO KPIS (id, kpiValue, kpiName, kpiDescription, kpiComment) VALUES (
1,
123,
'some name', 
'some description',
'some comment')

For the last couple of days, I got problems with the Internet it was disconnecting quite often. So in the middle of the insert, I got a connection error multiple times:

Runtime Error: 0x8005E002 -2147098622

Each time when this was happening I saw in the database lines like this:

+-------------------------------------------------------+  
|                        Results                        |  
+---+-----+-----------+------------------+--------------+  
| 1 | 123 | some name |                  | some comment |  
+---+-----+-----------+------------------+--------------+  
| 2 | 456 | some name |                  |              |  
+---+-----+-----------+------------------+--------------+
| 3 | 789 |           | some description |              |
+---+-----+-----------+------------------+--------------+

I'm absolutely sure that in the initial query all data was present.

My question is:

How is it possible for the single line insert query to insert data partially? I thought it is an atomic operation.

Related questions I found, but they look more complicated than my case: Q1, Q2

The inserts should be atomic. The problem here is likely that your _reads_ are happening in such a way that you are not viewing all of the underlying data as you expect. We need to the code/logic behind the reads which generated the table above. — Tim Biegeleisen, Jan 21 '21 at 02:56
I read it manually in the SSMS. Just SELECT * FROM KPIS WHERE id = 123 — hiichaki, Jan 21 '21 at 02:58
No, just one line simplest insert without any additional parameters. — hiichaki, Jan 21 '21 at 03:28
I strongly suggest you ignore the possibility that SQL Server has a *fundamental flaw with basic transaction processing* until you can reliably *reproduce* this issue. — Damien_The_Unbeliever, Jan 21 '21 at 08:22
Your database can be slightly corrupted too - I would check with dbcc checkdb (always my first step if weird errors pop up). — Arvo, Feb 03 '21 at 12:44

Geoffrey Fuller · Answer 1 · 2021-02-03T11:22:09.403

I think it's always safer to put insert statements into transactions, then check the insert for errors. If an error is found, rollback the transaction, otherwise commit the transaction. Before or after the insert within the transaction, you can check the fields for any NULL or other unwanted values. You could also use the @@Rowcount or @@Error to check a row insert prior to committing a transaction. Within the transaction, you could also record the error with @@Error and insert the error record into another tracking table, then rollback the main insert transaction.

BEGIN TRANSACTION AddKPIName

    INSERT INTO KPIS (id, kpiValue, kpiName, kpiDescription, kpiComment) VALUES (
    1,
    123,
    'some name', 
    'some description',
    'some comment')
IF  CASE
        WHEN 1 is NULL then 'NullInsert'
        WHEN 123 is NULL then 'NullInsert'
        WHEN 'some name' is NULL then 'NullInsert'
        WHEN 'some description' is NULL THEN 'NullInsert'
        WHEN 'some comment' is NULL THEN 'NullInsert'
    ELSE 'Not Null'
    END = 'NullInsert'
    BEGIN
        ROLLBACK TRANSACTION AddKPIName
    END
ELSE
    BEGIN
        COMMIT TRANSACTION AddKPIName
    END

How to check why insert to the SQL Server isn't executed atomically?

1 Answers1