Meaning of “Name/Userid is NULL” in sp_iqtransaction.
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
Describe what is meant by field:
MainTableKBCr, MainTableKBDr, TempTableKBCr, TempTableKBDr, TempWorkSpaceKB
in sp_iqtransaction() proc ?
How to understand and use them?
Hi, Oleg Luchinskiy
Please refer to the below URL:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01840.1603/doc/html/san1278453335524.htmlSyBooks Onlinehttp://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01840.1603/doc/html/san1278453335524.html
Thanks
Gi-Sung Jang.
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
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