none
Blocked process report: How can I have a tancount of 1 in read committed isolation level? RRS feed

  • Question

  • Aren't transaction executed in serialisable isolation level?

    What does it mean when I see a blocking process reporting a trancount of 1 in read committed isolation mode.

    Implicit connection vs user transaction?

    Monday, February 13, 2017 8:26 PM

Answers


  • The transaction is in read committed isolation level.

    The trancount indicate the statement is part of transaction.

    Sleeping, the statement completed.

    The cause for the blocking is a transaction left open.

    opening transaction has nothing to do with changing the isolation level, which is was my initial misunderstanding.

    Thanks for your time Teige.


    • Edited by Antoine F Thursday, February 16, 2017 3:17 PM
    • Marked as answer by Antoine F Thursday, February 16, 2017 3:17 PM
    Thursday, February 16, 2017 3:15 PM

All replies

  • Hi Antoine F,

    Firstly, in SERIALIZABLE ISOLATION LEVEL, all the rows are locked for the duration of the transaction, no insert,update or delete is allowed.

    Secondly, according to your description, my understanding is that you found that the TRANSACTION ISOLATION LEVEL has been set to read committed, could you please tell us from which report did you get the information, please share the screen shot.

    READ COMMITTED ISOLATION LEVEL means that statements cannot read data that has been modified but not yet committed by other transactions, it simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read.

    If you have any other questions, please let me know.

    Best Regards,
    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 14, 2017 7:07 AM
    Moderator
  • Hello Teige,

    it is the blocked process report collected via extended event session on sql server 2012 and viewed as a xel file.

    I have a hard time understanding how a sleeping process in read committed isolation level can block another process.

    • status-"sleeping"
    • trancount=1
    • isolationlevel="read committed (2)"

    <blocking-process> <process status="sleeping" spid="180" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-02-09T17:29:27.320" lastbatchcompleted="2017-02-09T17:29:27.340" lastattention="2017-02-09T17:28:56.303" clientapp="Internet Information Services" hostname="NameOfTheHost" hostpid="7332" loginname="LoginName" isolationlevel="read committed (2)" xactid="250347465712" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack /> <inputbuf> INSERT INTO mytable ...

    </inputbuf> </process> </blocking-process>


    Tuesday, February 14, 2017 3:26 PM

  • The transaction is in read committed isolation level.

    The trancount indicate the statement is part of transaction.

    Sleeping, the statement completed.

    The cause for the blocking is a transaction left open.

    opening transaction has nothing to do with changing the isolation level, which is was my initial misunderstanding.

    Thanks for your time Teige.


    • Edited by Antoine F Thursday, February 16, 2017 3:17 PM
    • Marked as answer by Antoine F Thursday, February 16, 2017 3:17 PM
    Thursday, February 16, 2017 3:15 PM