IQ 16 user 'another user' has the row '<tablename>' locked.

[former_member214071]

Good day everyone.

I'm working on this database and I get this error when adding a new column to a table.

User 'another user' has the row '<tablename>' locked.

To this database is also used by SAP BO server as datasource for reporting.

Can anyone help me how to find and remove the lock ?

Thanks in advance.

[former_member259191 · Accepted solution]

In IQ, even SELECT statements send an internal BEGIN TRAN to the server to be able to protect the data set version accessed in that session. Therefore you need to make sure that there is no READER in the system if you will do schema changes. For the same reason, If you want to avoid the space requirements of extra versions for example, it's better you disconnect readers before large ETL operations.

There is also one setting we use for BI users to make sure that they always get the latest version of data and release the previous version:  Add the following parameters to the "advance options" of ODBC profile:  AUTOPRECOMMIT=YES.

Regards,

[former_member214071 · Accepted solution]

Hello Sengul , thanks for your reply and the clear explanation about how IQ works locking even when querying data.

I was unable to find the AUTOPRECOMMIT in advanced options in the system DSN ODBC connection.

[former_member259191 · Accepted solution]

Hi Emerson,

There is no such option in ODBC. When you click on "Advanced" in IQ ODBC profile there is an editable window, you need to put AUTOPRECOMMIT=YES into that area.

Regards,

[former_member214071 · Accepted solution]

thanks Sengul, I was looking to the predefined parameter only but you are rigth there is a textbox above to input custom parameters.

best regards,

[saroj_bagai]

YOu can look at sp_iqlocks and see who is holding lock on the table, you can either let that process finish and then try to alter table again, Alter table does need exclusive lock on the table

[former_member214071]

Thanks for fast reply Saroj,  after running sp_iqlocks it shows that the table has many rows all for user_id=dba, lock_duration=Transaction and lock_type=Shared.

It calls my attention cuz there is nobody else working with this table just me, I wonder why are all those locks on this table.

Now I got the conn_id for all those rows how can I remove the locks?

Thanks.

[former_member214071]

it seems I can use drop connection <conn_id>  .  I still wonder why so many locks since nobody is working on this table.

But with the help provided I was able to drop the locks.

Thanks.

[former_member188490]

Next time before dropping such connection, you can run

  Select Userid , IQconnID , NodeAddr , ConnCreateTime from sp_iqconnection() where ConnHandle = NNN

  NNN is connetion handle number which you are dropping.

  This will give you some idea about who ( Userid ) opened which IQ connection ID ( IQConnID ) from which client box ( NodeAddr ) since what time ( ConnCreateTime )

Since the connection is already dropped, this information can also be found using IQ diagnostic files ( .iqmsg , .srvlog ) where you can look it up what exectly got disconnected.

Based on what was disconnected you will be able to figure out whether there is another user OR some other application opened the connection which you dropped.

Regards

[former_member214071]

Thanks for reply Milind,

The locks are from the BO server IP address and they are since 2 days ago, userid is dba.  I wonder why BO server is locking rows if there is no insert or updates from this server.  The SAP BO only does querys to use on the reports.

When killed one connection these got saved on the .srvlog

I. 08/08 08:30:30. User "dba" dropped connection 10313 ("dba")

I. 08/08 08:30:30. Connection terminated abnormally

I. 08/08 08:30:30. Disconnected TCPIP client's AppInfo: IP=192.168.0.180;HOST=sapbo;OSUSER=biservice;OS='Windows 2008R2 Build 7601 Service Pack 1';EXE='E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\WIReportServer.exe';PID=0x1154;THREAD=0x2ed4;VERSION=16.0.0.428;API=ODBC;TIMEZONEADJUSTMENT=-360

Thanks.