0

The system I am administering is deadlocking very frequently on a specific stored procedure.

The application is made up of an Azure SQL Database (ASD) that is connected to a web application. The ASD is integrated with the main OLTP database on an Azure VM. The ASD stores a small amount of data, but most of the data is accessed through external tables and CRUD operations are executed on the VM using "SP_EXECUTE_REMOTE" from the ASD.

The problem query looks like:

BEGIN TRY
   EXEC SP_EXECUTE_REMOTE @data_source_name = N'VM_db',
                       @stmt = N'BEGIN TRY
       EXEC [dbo].[StoredProc] @Var1,@Var2,@Var3,@Var4
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ERROR_MSG;
END CATCH',
@params = N'@Var1 NVARCHAR(80), @Var2 BIT, @Var3 UNIQUEIDENTIFIER, @Var4 DATETIME',
Var1=@pVar1, @Var2=@pVar2, @Var3=@pVar3, @Var4=@now

The data source (VM_db) is set to use Read Committed Snapshot Isolation (RCSI). Does the SP_EXECUTE_REMOTE honour the target database's Isolation level, or does it default to READ COMMITTED?

1
  • 2
    The deadlock report will show the actual isolation level as well as the locks involved.
    – Dan Guzman
    Feb 14, 2023 at 16:39

1 Answer 1

1

The isolation level can be different according to your setting and actions. You can directly check the information by using SP_EXECUTE_REMOTE and run a query to check it.

  • To check the Connection Isolation Level run DBCC USEROPTIONS

  • To check the Database Isolation Level run the query: SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

  • To check the Statement Isolation Level you can use on the remote server Extended Events

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.