We can sometimes see that the Name and Userid is (NULL) in sp_iqtransaction like below.

[SQL]

  select * from sp_iqtransaction() where Name is null and userid is null ;

Name Userid TxnID CmtID VersionID State Connhandle IQConnID TxnCreateTime
(NULL) (NULL) 52458621 52458626 52458626 COMMITTED 58 2714410 2014-04-28 14:59:26.041

[Steps to reproduce]

There are two session A and B.

1. create a test table.

    – create table gjang(c1 int);

2. [Session A]

    – Data has been changed using insert command.

      >> insert gjang values(1);

3. [Session B]

    – Before commit is executed in “Session A”, Reference old version of
gjang table in “Session B”.

      >> select * from gjang;

4. [Session A]

    – Execute commit and then disconnect from “Session A”.

      >> /*insert gjang values(1);*/
      >> commit

5. We can see that Name and Userid of “Session A” is null after disconnection.

[Cause]

If “Session A” has been disconnected, Name and Userid becomes NULL. Because
“Session B” is still referencing an old version of table in “Session A.”

[Session B]

Name Userid TxnID CmtID VersionID State Connhandle IQConnID TxnCreateTime
SQL_DBC_10a18710 DBA 52458705 0 52458705 ACTIVE 57 2714356 2014-04-28 15:00:36.545

If Executing a commit after completing a referencing job in “Session B”,
State of “Session A” will be changed from COMMITTED to APPLIED.
And then “Session A” will be disappeared in sp_iqtransaction when next checkpoint.

[Session B]

>> /*select * from gjang;*/
>> commit;

Thanks

Gi-Sung Jang

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Oleg Luchinskiy

    Describe what is meant by field:

    MainTableKBCr, MainTableKBDr, TempTableKBCr, TempTableKBDr, TempWorkSpaceKB

    in sp_iqtransaction() proc ?

    How to understand and use them?

    (0) 
      1. Hemanth H

        Hi Gi-Sung,

        If that user is disconnected but it is causing versioning.

        if we check in sp_iqtransaction, still that user will be there with username NULL.

        I am able to drop that connection. How to kill that connection?

        Thanks,

        Santosh Negalur

        (0) 
        1. Gi-sung Jang Post author

          Hi, santosh negalur

          As I mentioned this article, you have to find other session which hold versioning using sp_iqtransaction or sp_iqlocks or sp_iqcontext.

          You can’t drop this session because it’s already disconnected.

          Other session has to issue commit and checkpoint, It makes release versioning of txn.

          “If Executing a commit after completing a referencing job in “Session B”,

          State of “Session A” will be changed from COMMITTED to APPLIED.

          And then “Session A” will be disappeared in sp_iqtransaction when next checkpoint.”


          Thanks

          Gi-Sung Jang

          (0) 

Leave a Reply