SQL Server Transaction with multiple updates and unique index

vfh0ocws 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(163)

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?

sql-server

来源：https://stackoverflow.com/questions/76791683/transaction-with-multiple-updates-and-unique-index

2条答案

按热度按时间

fkaflof61#

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.

赞(0）回复(0）举报 2023-08-02

ffdz8vbo2#

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);

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Transaction with multiple updates and unique index

2条答案

相关问题

热门标签

最新问答