none
Stored procedure mysteriously run with Isolation Level Serializable

    Întrebare

  • We are using Azure SQL Database (capability level SQL Server 2017). ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are both ON.

    A deadlock occurred which had me scratching my head until I saw that the simple INSERT SELECT stored procedure (the deadlock victim) was running in isolation level Serializable.

    This is strange since we don't use that isolation level at all on that database (code or stored procedures). After must chasing of information, I can say that:

    * I looked at the sp_reset_connection not resetting isolation, but I ran the code in article:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/916b3d8a-c464-4ad5-8901-6f845a2a3447/sql-server-2014-reseting-isolation-level?forum=sqldatabaseengine

    and I can see the isolation level being set back to 2 (Read Committed). However I don't know if that is the canonical test for this issue. Even so, we don't use Serializable on that database;

    * I looked at the issue with TransactionScope. We don't use TransactionScope, although we do use SqlConnection.BeginTransaction() which is passed to a SqlBulkCopy. I inspected the isolation level of this transaction and it is Read Committed. It should be noted that the other deadlock member (and the winner) was one of these transactions and the deadlock file said they had an isolation level of Read Committed;

    * I checked connection pooling and determined that our use of Database name in the connection string should isolate the connection pool by database;

    * I checked the (TDS) protocol levels using this SQL:

    SELECT SUBSTRING(CAST(protocol_version as binary(4)), 1, 1)
    FROM sys.dm_exec_connections;

    Most were 0x74, while two were 0x71.

    I tracked the IP address of the two 0x71 connections to a VM that is running 3rd-party software that MAY use Serializable, but given the connection strings are different (due to the Database connection string key) I would not expect these connection pools to mix. I also ran the diagnositic in the above article against that 3rd-party Azure SQL Database instance and also saw the connection reset back to Read Committed.

    At this point I am at a dead end. I know why we had a deadlock, I just cannot determine how a simple (auto commit) INSERT SELECT transaction had it's isolation level set to Serializable. What else can I check/examine?

    Cheers,
    Mac

    • Mutat de Shanky_621MVP joi, 17 mai 2018 07:17 moving it to Azure forum
    joi, 17 mai 2018 00:16

Răspunsuri

  • G'day Guys,

    Mystery is solved - further code review shows that we ARE using TransactionScope() to wrap code further up the callstack, and this will set the isolation level to Serializable.

    Thanks for your time and sorry to raise an issue that was solvable on our side.

    Cheers,
    Graham

    vineri, 18 mai 2018 14:56

Toate mesajele

  • Hello,

    You mentioned you are not using Serializable at the database level and you are using the default Read Committed Snapshot Isolation (RCSI) , but are you sure you are not using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE at a connection level on the connections that are performing those Insert statements?


    Are you sure a change on the locking and row versioning is not been requested at the connection level instead of a database level? With Azure SQL Database you can have RCSI at the database level (which is the default) but you can change at the connection level to Serializable, Snapshot, Repeatable Read, Read Committed, or Read Uncommitted.


    Could you please look for some code like below?


    connection.BeginTransaction(System.Data.IsolationLevel.Serializable


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    joi, 17 mai 2018 12:08
  • G'day Alberto,

    Thanks for the reply.

    The code/SQL inspection shows nothing in the code.

    Here is the store procedure that was marked serializable:

    CREATE PROCEDURE [dbo].[CreateProfileAssessment](@uid int, @orgGuidelineId int, @guidelineInstanceKey varchar(50))
    AS
        INSERT INTO [PROFILE_ASSESSMENT] (UID, OID, ORG_GUIDELINE_KEY, GUIDELINE_INSTANCE_KEY, ASSESSMENT_STATUS, ASSESSMENT_START_DATE)
        SELECT @uid, op.[OID], @orgGuidelineId, @guidelineInstanceKey, 'New', GETUTCDATE()
        FROM [dbo].[ORG_PROFILE] op
        WHERE op.[UID] = @uid
    GO

    Here is the code snippet running the stored procedure:

    using (SqlConnection conn = new SqlConnection(connectionString)) {
        try {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.CommandText = "CreateProfileAssessment";
            cmd.Parameters.Add(new SqlParameter("@uid", uid));
            cmd.Parameters.Add(new SqlParameter("@orgGuidelineId", orgGuidelineId));
            cmd.Parameters.Add(new SqlParameter("@guidelineInstanceKey", guidelineInstanceKey));
            conn.Open();
            sqlReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            success = true;
        } catch (Exception ex) {
            logger.Error(ex, "System error in CreateUserAssessmentInDB");
            success = false;
        } finally {
            if (sqlReader != null && !sqlReader.IsClosed) {
                sqlReader.Close();
            }
        }
    }

    Here is the code snippet creating the winner transaction (the stored procedure is complex ETL) - the deadlock file indicates this was Read Committed isolation level:

    using (SqlConnection conn = new SqlConnection(connectionString)) {
        conn.Open();
        // create transaction
        SqlTransaction tran = conn.BeginTransaction();
        try {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = tran;
    
            <snipped temporary table work>
    
            try {
                // create a bulk copy operation and stream in CSV
                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran);

    Here for the sake of completeness is the deadlock XDL:

    <deadlock>
      <victim-list>
        <victimProcess id="process823b521c28" />
      </victim-list>
      <process-list>
        <process id="process823b521c28" taskpriority="0" logused="0" waitresource="KEY: 14:72057594044153856 (38e36d6599f8)" waittime="1391" ownerId="77326132" transactionname="user_transaction" lasttranstarted="2018-05-15T16:21:50.870" XDES="0x82381d0458" lockMode="RangeS-S" schedulerid="1" kpid="5500" status="suspended" spid="107" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-15T16:21:50.950" lastbatchcompleted="2018-05-15T16:21:50.870" lastattention="1900-01-01T00:00:00.870" clientapp=".Net SqlClient Data Provider" hostname="*deleted*host1" hostpid="8840" loginname="*deleted*" isolationlevel="serializable (4)" xactid="77326132" currentdb="14" currentdbname="*deleted*" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="*deleted*.dbo.CreateProfileAssessment" queryhash="0xb27abce8fdc7ddc3" queryplanhash="0xb0a98215b1ff936e" line="5" stmtstart="318" stmtend="868" sqlhandle="0x03000e006f470e75f00e3801c0a8000001000000000000000000000000000000000000000000000000000000">
    INSERT INTO [PROFILE_ASSESSMENT] (UID, OID, ORG_GUIDELINE_KEY, GUIDELINE_INSTANCE_KEY, ASSESSMENT_STATUS, ASSESSMENT_START_DATE)
        SELECT @uid, op.[OID], @orgGuidelineId, @guidelineInstanceKey, 'New', GETUTCDATE()
        FROM [dbo].[ORG_PROFILE] op
        WHERE op.[UID] = @ui    </frame>
          </executionStack>
          <inputbuf>
    Proc [Database Id = 14 Object Id = 1963870063]   </inputbuf>
        </process>
        <process id="process820c2bcca8" taskpriority="0" logused="3700" waitresource="KEY: 14:72057594044153856 (8ec23bc7425e)" waittime="1093" ownerId="77325491" transactionname="user_transaction" lasttranstarted="2018-05-15T16:21:49.847" XDES="0x822d944458" lockMode="X" schedulerid="1" kpid="11968" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2018-05-15T16:21:50.300" lastbatchcompleted="2018-05-15T16:21:50.110" lastattention="2018-05-15T16:21:50.153" clientapp=".Net SqlClient Data Provider" hostname="*deleted*host2" hostpid="7844" loginname="*deleted*" isolationlevel="read committed (2)" xactid="77325491" currentdb="14" currentdbname="*deleted*" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="*deleted*.dbo.MergePatientRecords" queryhash="0x2d4fc503ab6193c4" queryplanhash="0xc6ffbe4b4c39252c" line="243" stmtstart="28072" stmtend="28732" sqlhandle="0x03000e00cc0adc36434b0200d8a8000001000000000000000000000000000000000000000000000000000000">
    UPDATE [ORG_PROFILE] 
        SET MRN_NUMBER = CASE WHEN spt.[EID] IS NULL OR spt.[MRN] IS NULL THEN MRN_NUMBER ELSE spt.[MRN] END
        FROM [ORG_PROFILE] op 
        INNER JOIN [#UPDATE] tu ON tu.[OID] = op.[OID] AND tu.[UID] = op.[UID]
        INNER JOIN [#STAGING_PATIENT_TEMP] spt ON spt.[STAGING_PATIENT_KEY] = tu.[STAGING_PATIENT_KEY    </frame>
            <frame procname="*deleted*.dbo.KeyAndMergePatientRecords" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="15" stmtstart="1340" stmtend="1522" sqlhandle="0x03000e001f12d52230123801c0a8000001000000000000000000000000000000000000000000000000000000">
    EXEC [MergePatientRecords] @inserted OUTPUT, @updated OUTPUT, @ignored OUTPUT, @duped OUTPU    </frame>
          </executionStack>
          <inputbuf>
    Proc [Database Id = 14 Object Id = 584389151]   </inputbuf>
        </process>
      </process-list>
      <resource-list>
        <keylock hobtid="72057594044153856" dbid="14" objectname="*deleted*.ORG_PROFILE" indexname="ORG_PROFILE_PK" id="lock821d5d2200" mode="X" associatedObjectId="72057594044153856">
          <owner-list>
            <owner id="process820c2bcca8" mode="X" />
          </owner-list>
          <waiter-list>
            <waiter id="process823b521c28" mode="RangeS-S" requestType="wait" />
          </waiter-list>
        </keylock>
        <keylock hobtid="72057594044153856" dbid="14" objectname="*deleted*.dbo.ORG_PROFILE" indexname="ORG_PROFILE_PK" id="lock820433f980" mode="RangeS-U" associatedObjectId="72057594044153856">
          <owner-list>
            <owner id="process823b521c28" mode="RangeS-S" />
          </owner-list>
          <waiter-list>
            <waiter id="process820c2bcca8" mode="X" requestType="convert" />
          </waiter-list>
        </keylock>
      </resource-list>
    </deadlock>

    I am baffled as to how the stored procedure was marked serializable... ideas welcome.

    Cheers,
    Graham



    • Editat de Mac From Mack joi, 17 mai 2018 16:30 Removed login/host info
    joi, 17 mai 2018 16:25
  • Hello,

    Let me share this issue further and try to get an explanation for you or try to get somebody that can really help you.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    joi, 17 mai 2018 20:59
  • The default connection mode in various C# packages is serializable.  Have run into this many times.  Explicitly set the mode here to read committed.  I forget if there's a way to change the default, I'm not much of a C# guy.

    Josh

    vineri, 18 mai 2018 12:29
  • G'day Guys,

    Mystery is solved - further code review shows that we ARE using TransactionScope() to wrap code further up the callstack, and this will set the isolation level to Serializable.

    Thanks for your time and sorry to raise an issue that was solvable on our side.

    Cheers,
    Graham

    vineri, 18 mai 2018 14:56
  • Hello,

    I found some help and was ready to update the forum thread when you wrote you found the answer.


    Thank you for coming to Azure forums to find answers to your questions.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    vineri, 18 mai 2018 15:32