Transaction with multiple updates and unique index

Question

SQL Server has table, where one record is FK to the same table (parent record or 'null' if it's first record):

CREATE TABLE [dbo].[RegisteredDevice]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PreviousDeviceId] [int] NULL,
    [Position] [int] NULL,
    [DeviceName] [nvarchar](max) NOT NULL,
    [ModelNumber] [nvarchar](max) NOT NULL,

    CONSTRAINT [PK_RegisteredDevice] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[RegisteredDevice] WITH CHECK 
    ADD CONSTRAINT [FK_RegisteredDevice_RegisteredDevice_PreviousDeviceId] 
        FOREIGN KEY([PreviousDeviceId])
            REFERENCES [dbo].[RegisteredDevice] ([Id])
GO

ALTER TABLE [dbo].[RegisteredDevice] 
    CHECK CONSTRAINT [FK_RegisteredDevice_RegisteredDevice_PreviousDeviceId]
GO

I have the following SQL code:

BEGIN TRANSACTION
    UPDATE [RegisteredDevice] 
    SET [PreviousDeviceId] = 2
    WHERE [Id] = 5;

    UPDATE [RegisteredDevice] 
    SET [PreviousDeviceId] = 4
    WHERE [Id] = 2;

    UPDATE [RegisteredDevice] 
    SET [PreviousDeviceId] = 1
    WHERE [Id] = 3;

COMMIT TRANSACTION

For this data:

but this code is not executed inside transaction:

Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (2).
The statement has been terminated.

Msg 2601, Level 14, State 1, Line 6
Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (4).
The statement has been terminated.

Msg 2601, Level 14, State 1, Line 8
Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (1).
The statement has been terminated.

If all these changes will be committed inside one transaction, there is no any duplicate key. Why transaction is not processed?

The FK check is carried out per update statement, not per transaction. If you combine them into a single update I think it will work. — Dale K, Jul 29 '23 at 00:55
@DaleK unfortunately, I don't control SQL, it generates by Entity Framework Core and can't modify it :( — Oleg Sh, Jul 29 '23 at 00:57
You'll need to rejig your changes such that duplicates never exist then e.g. set the existing instance to null before setting the new instance. — Dale K, Jul 29 '23 at 00:59
And FYI you need to mention stuff like the SQL is generated by EF as that is an important part of your question. — Dale K, Jul 29 '23 at 01:00
@DaleK but then it will be 2 transactions in any cases and it's bad solution... — Oleg Sh, Jul 29 '23 at 01:00
Well personally I think EF is a bad solution :) so I no longer use it. Another option is to put this update in a stored procedure and bypass EF. — Dale K, Jul 29 '23 at 01:02
@DaleK impossible for my case, because it's a part of other possible changes (adding, deleting, modifying etc). But thank you in any case for trying to help ^) — Oleg Sh, Jul 29 '23 at 01:09
Personally I would put all the changes you need to do through stored procedures - there are a lot of benefits to doing it that way. — Dale K, Jul 29 '23 at 01:13
The foreign key isn't the problem. You've left out of your question the following definition: `CREATE UNIQUE INDEX IX_RegisteredDevice_PreviousDeviceId ON [dbo].[RegisteredDevice] (PreviousDeviceId);` — AlwaysLearning, Jul 29 '23 at 01:25
As @DaleK suggested, combining the updates into a single statement will succeed despite the unique index constraint, ref: [dbfiddle.uk](https://dbfiddle.uk/xfduVgxe) — AlwaysLearning, Jul 29 '23 at 01:36
I would say that previousid should not be unique indexed, it's just a history field. I don't know if that works for your FK setup though. — siggemannen, Jul 29 '23 at 08:35
Are the names of your devices and the model numberd *really* over 4,000 characters in length? — Thom A, Jul 29 '23 at 10:08

score 1 · Accepted Answer · answered Jul 30 '23 at 02:09

In SQL Server, constraint checks are not deferrable, and are always carried out per update statement, not per transaction. If you combine them into a single statement then it works as noted in the other answer.

You claim you cannot modify the query because it's generated by Entity Framework. But assuming you are using EF Core 7.0+, you can do the following bulk update query:

await Db.RegisteredDevice
    .Where(rd => rd.Id == 2 || rd.Id == 3 || rd.Id == 5)
    .ExecuteUpdateAsync(setter => setter
        .SetProperty(
            rd => rd.PreviousDeviceId,
            rd => rd.Id == 2 ? 4 :
                  rd.Id == 3 ? 1 :
                  2
         )
    );

Which will generate a batch update statement that does the same thing as the other answer.

score 0 · Answer 2 · edited Jul 29 '23 at 07:50

0

As it says, no need for a transaction. A single UPDATE is atomic and can be written like :

UDPATE dbo.RegisteredDevice 
SET    PreviousDeviceId = CASE Id 
                             WHEN 5 THEN 2
                             WHEN 2 THEN 4
                             WHEN 3 THEN 1
WHERE  Id IN (3, 3, 5);

edited Jul 29 '23 at 07:50

Stu

30,392
6
14
33

answered Jul 29 '23 at 07:13

SQLpro

3,994
1
6
14

unfortunately, I can't edit SQL query... – Oleg Sh Jul 29 '23 at 18:57

Transaction with multiple updates and unique index

2 Answers2