A
A
artshelom2021-09-19 01:30:15
SQL Server
artshelom, 2021-09-19 01:30:15

How not to block neighboring records for updating by adding a trigger?

Create trigger Update_applicationUpdate_MainTab on MainTab
after update
NOT FOR REPLICATION
as 
begin
  Update [SubTab] with(ROWLOCK) set [LatUpdateTime] = SYSDATETIME() 
  where [dbo].[SubTab].[ClientTableId] in (select Id from inserted)
end;

When adding this trigger, when I do: update the first row (id=1) from the first transaction (without completing the transaction). And from the second transaction, updating the second line (id = 2), then until the first line is updated (the transaction does not end). The second transaction will not work. Without this trigger, everything works as it should.
Namely: From the first transaction I update the first line. From the second to the second line. And the second one is successful.
What could be the reason? (In SubTab, each MainSub line has its own SubTab line. That is, they do not match there). As I understand it, because inserted - can accumulate, several data for the last transaction?

CREATE TABLE [dbo].[SubTable](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Table] [nvarchar](64) NULL,
  [LatUpdateTime] [datetime2](7) NULL,
  [ClientTableId] [nvarchar](64) NULL,
 CONSTRAINT [PK_SubTable] 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]

CREATE TABLE [dbo].[MainTable](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](max) NOT NULL
 CONSTRAINT [PK_MainTable] 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]

insert into [dbo].[MainTable] VALUES('check1')
insert into [dbo].[MainTable] VALUES('check2')
insert into [dbo].[SubTable] VALUES('[dbo].[MainTable]', SYSDATETIME(), SYSDATETIME(), 1)
insert into [dbo].[SubTable] VALUES('[dbo].[MainTable]', SYSDATETIME(), SYSDATETIME(), 2)

Create trigger Update_applicationUpdate_MainTable on MainTable 
after update 
as
Update [SubTable] with(ROWLOCK) set [LatUpdateTime] = SYSDATETIME() 
  where [dbo].[SubTable].[ClientTableId] in (select Id from inserted) 
  and [Table] = '[dbo].[MainTable]';


Transaction -1:
begin transaction

update [dbo].[MainTable]
  set [Name] = 'Тестовый37' where id = 1


Transaction-2:
update [dbo].[MainTable]
  with(ROWLOCK)
  set [Name] = 'Тестовый37' where id = 2

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question