SQL deadlock occurred, using three triggers, cross-server, did not find the reason

Question

1. I used an Insert-trigger on the Table-A of the Database-A on Server-A. This trigger will put the Inserted-data into Table-B of the Database-B on Server-B.
2. I used an Insert-trigger on the Table-B of the Database-B on Server-B. This trigger uses the Inserted-data to calculate, and then insert the result in the Table-C of the Database-B on Server-B.
3. I used an Update-trigger on the Table-C of the Database-B on Server-B. This trigger used the Inserted-data to calculate, and then send mail automatically.

SQL Server now looks as if often deadlock. Is there any good solution?

Description: First trigger is used for get data, second trigger is the actual logic, the third trigger sends mail according to the result of the second trigger.

Trigger on Table-A of Database-A on Server-A as below:

USE [Database-A]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TRIGGER_INSERT_A]
  ON [dbo].[Table-A]
  FOR INSERT
AS
  BEGIN
    INSERT INTO [Server-B].[Database-B].[dbo].[Table-B] (
        [Server-B].[dbo].[Table-B].[field1],
        [Server-B].[dbo].[Table-B].[field2],
        [Server-B].[dbo].[Table-B].[field3],
        [Server-B].[dbo].[Table-B].[field4],
        [Server-B].[dbo].[Table-B].[field5],
        [Server-B].[dbo].[Table-B].[field6],
        [Server-B].[dbo].[Table-B].[field7]
    ) SELECT
        [field1],
        [field2],
        [field3],
        [field4],
        [field5],
        [field6],
        [field7]
        FROM inserted
  END
GO

Trigger on Table-B of Database-B on Server-B as below:

USE [Database-B]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TRIGGER_INSERT_B]
ON [dbo].[Table-B]
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

  -- Logic portion is omitted
  -- UPDATE [dbo].[Table-B] OR INSERT [dbo].[Table-B]
END

GO

Trigger on Table-C of Database-B on Server-B as below:

USE [Database-B]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TRIGGER_UPDATE_C]
ON [dbo].[Table-C]
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @recipients VARCHAR(MAX)
  DECLARE @subject NVARCHAR(255)
  DECLARE @body NVARCHAR(MAX)
  DECLARE @body_format VARCHAR(20)
  DECLARE @importance VARCHAR(6)
  DECLARE @sensitivity VARCHAR(12)

  DECLARE @query NVARCHAR(MAX)
  DECLARE @execute_query_database NVARCHAR(128)
  DECLARE @attach_query_result_as_file BIT
  DECLARE @query_attachment_filename NVARCHAR(260)
  DECLARE @query_result_header BIT
  DECLARE @query_result_width INT
  DECLARE @query_result_separator CHAR(1)
  DECLARE @exclude_query_output BIT
  DECLARE @append_query_error BIT
  DECLARE @query_no_truncate BIT

  SELECT
    @recipients = [recipients],
    @subject = [subject],
    @body = [body],
    @body_format = [body_format],
    @importance = [importance],
    @sensitivity = [sensitivity]
  FROM [dbo].[Table-D]

  SET @query = 'query portion is omitted'
  SET @execute_query_database = 'Database-B'
  SET @attach_query_result_as_file = 1
  SET @query_attachment_filename = 'Automatic alarm.txt'
  SET @query_result_header = 1
  SET @query_result_width = 256
  SET @query_result_separator = ' '
  SET @exclude_query_output = 0
  SET @append_query_error = 0
  SET @query_no_truncate = 0

  -- UPDATE [dbo].[Table-C] here

  EXEC [msdb].[dbo].[sp_send_dbmail]
      @profile_name = 'profile_name',
      @recipients = @recipients,
      @subject = @subject,
      @body = @body,
      @body_format = @body_format,
      @importance = @importance,
      @sensitivity = @sensitivity,
      @query = @query,
      @execute_query_database = @execute_query_database,
      @attach_query_result_as_file = @attach_query_result_as_file,
      @query_attachment_filename = @query_attachment_filename,
      @query_result_header = @query_result_header,
      @query_result_width = @query_result_width,
      @query_result_separator = @query_result_separator,
      @exclude_query_output = @exclude_query_output,
      @append_query_error = @append_query_error,
      @query_no_truncate = @query_no_truncate
END

Answer 1

I totally agree with the opinion of Wes H, but I don't like the idea of having a high frequency ETL. If inserts happen once a day, you will waste lots of resources.

According to you script, you are replicating an entire table on the other server, so why don't you actually use replication?

Otherwise, this seems a scenario for the SERVER BROKER. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker You could create a message directly inside the trigger and consume it asynchronously of the other server.

Answer 2

Using triggers across a linked server is a terrible idea. There are so many things that can go wrong with the process, in addition to the deadlocks.

Most likely, the transaction for the insert into Table A is being held longer due to latency for the insert into ServerB.TableB. If that linked server connection is slow for any reason, you'll run into problems.

If TableA and TableB need to be so closely linked, they should be on the same server, even better if they were in the same database. Crossing a physical server boundary will add significant overhead to what should be an extremely quick process, easily changing from a few microseconds into 5 milliseconds or more, even across a fast network. That may not seem like much, but a 1000 % increase in duration will have an impact. Also, even the best network can have a momentary drop in performance. A sudden latency increase to 100 milliseconds will bring your performance to a sudden stop as you potentially experience transaction timeouts.

Is there any reason your insert into Table B must be part of the same transaction as the insert into Table A? I think moving data between servers is best handled by an independent process, meaning not part of the transaction with Table A. I would recommend an ETL process that runs frequently, maybe every minute. If that is too slow, you really should consolidate them onto the same infrastructure.

Based on the code you've posted, I don't see how TableC is related to the deadlock. The triggers for TableA and TableB don't seem to interact with it.