select statement making long transaction

We are using IDS 12.10.FC4W1. We were getting "long transaction aborted" message in online log and found a select statement running during those times. That select statement scan whole index and takes too long to get result also it locks the index. statement is like select column_list from table where col1=123 order by col2 and there is an index on col1 and another index on col2. if we eliminate"order by" clause or use index directive or col1 we get result within a second(otherwise optimizer choose index on col2). Please guide how a select statement can lock the table/index and cause "long transaction". Note: isolation level is read "committed" and database is in unbufferred log mode.

select triggers??? Madison Pruet Retired and Loving it On Friday, February 26, 2016 11:25 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote: We are using IDS 12.10.FC4W1. We were getting "long transaction aborted" message in online log and found a select statement running during those times. That select statement scan whole index and takes too long to get result also it locks the index. statement is like select column_list from table where col1=123 order by col2 and there is an index on col1 and another index on col2. if we eliminate"order by" clause or use index directive or col1 we get result within a second(otherwise optimizer choose index on col2). Please guide how a select statement can lock the table/index and cause "long transaction". Note: isolation level is read "committed" and database is in unbufferred log mode. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

There is no trigger on that table. That's why query return result immediately on proper index hit.

Are you sure there's no BEGIN WORK in front of the select? >We are using IDS 12.10.FC4W1. We were getting "long transaction aborted" message in online log >and found a select statement running during those times. That select statement scan whole index >and takes too long to get result also it locks the index. >statement is like >select column_list from table where col1=123 order by col2 >and there is an index on col1 and another index on col2. if we eliminate"order by" clause or use >index directive or col1 we get result within a second(otherwise optimizer choose index on col2). >Please guide how a select statement can lock the table/index and cause "long transaction". >Note: isolation level is read "committed" and database is in unbufferred log mode.

How do you know that it's locking the index? What sort of lock? Are your temp spaces created as temp spaces - do they show up with a "T" in onstat -d? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KAMRAN HAQ Sent: Friday, February 26, 2016 10:25 AM To: ids@iiug.org Subject: select statement making long transaction [36629] We are using IDS 12.10.FC4W1. We were getting "long transaction aborted" message in online log and found a select statement running during those times. That select statement scan whole index and takes too long to get result also it locks the index. statement is like select column_list from table where col1=123 order by col2 and there is anindex on col1 and another index on col2. if we eliminate "order by" clause or use index directive or col1 we get result within a second(otherwise optimizer choose index on col2). Please guide how a select statement can lock the table/index and cause "long transaction". Note: isolation level is read "committed" and database is in unbufferred log mode. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

My guess is you do not have a non-logging dbspace in your tem= p list. This will cause any sort to use a logging dbspace, and if yo= u run out out of transaction log space before the sort completes the= n the select will be rolled back. John F. Miller III= STSM, Lead Architect [1]miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (ID= S) [2]-----ids-bounces@iiug.org wrote: ----- >To: [3]id= s@iiug.org >From: "KAMRAN HAQ" >Sent by: [4]ids-bounces@iiug.org >= Date: 02/26/2016 09:25AM >Subject: select statement making long trans= action [36629] > >We are using IDS 12.10.FC4W1. We were gettin= g "long transaction >aborted" >message in online log and found= a select statement running during >those times. >That select = statement scan whole index and takes too long to get >result also >it locks the index. > >statement is like >select c= olumn=5Flist from table where col1=3D123 order by col2 >and there is= an index on col1 and another index on col2. if we >eliminate >= ;"order by" clause or use index directive or col1 we get result within &= gt;a >second(otherwise optimizer choose index on col2). >Plea= se guide how a select statement can lock the table/index and >cause "= long >transaction". >Note: isolation level is read "committed= " and database is in >unbufferred log >mode. > ><= br>>****************************************************************** **= * >********** > Forum Note: Use "Reply" to post a response in= the discussion forum. > > > References 1. 3D"mailto:miller3@us.ibm.com= 2. file://localhost/tmp/3D"mai= 3. 3D"mailto:ids@iiug.org" 4. 3D"mailto:ids-bounces@iiug.org"

Well, one other possibility is that you had to create a temporary table, or temporary index to resolve the query, and that caused you to have to generate allocation log records. I'm not sure, but guess that would have put the query into a transnational state. And if there was a lot of other activity going on, and the select query did take a long time, then you could have ended up with a long transaction. Not sure. Madison Pruet Retired and Loving it On Friday, February 26, 2016 1:12 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: There is no trigger on that table. That's why query return result immediately on proper index hit. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.