lock problem

Hi, A user execute beow SQL in dbaccess. the number of rows of talbe aapf10 is 550804, It run slowly, and others run some update sql on the same table and being locked, Do anybody know it is blow sql lead to the lock problem ? thanks for your time. select aa10dpnoa,round(sum(aa10bal)/10000,0) from aapf10 where aa10cur="01" and aa10subj[1,4] in ("1301","1302","1303","1304","1305", "1307","1308") group by 1 order by 1 this sql use the wrong execute plan. Estimated Cost: 344571 Estimated # of Rows Returned: 10 1) cbs.aapf10: INDEX PATH Filters: (cbs.aapf10.aa10subj[1,4] IN ('1301' , '1302' , '1303' , '1304' , '1305' , '1307' , '1308' )AND cbs.aapf10.aa10cur = '01' ) (1) Index Keys: aa10dpnoa aa10dpnok aa10accod (Serial, fragments: ALL) Below are the index definition. create unique index "cbs".aalf11 on "cbs".aapf10 (aa10acid) using btree in idxdbs ; create index "cbs".aalf12 on "cbs".aapf10 (aa10ctid) using btree in idxdbs ; create index "cbs".aalf13 on "cbs".aapf10 (aa10cur,aa10subj) using btree in idxdbs ; create index "cbs".aalf14 on "cbs".aapf10 (aa10dpnoa,aa10dpnok, aa10accod) using btree in idxdbs ; create index "cbs".aalf15 on "cbs".aapf10 (aa10actyp,aa10bkno, aa10subj) using btree in idxdbs ; create index "cbs".aalfhc1518 on "cbs".aapf10 (aa10name) using btree in idxdbs ;

Yes. Each row, or the page the row lives on if the lock level of the table is page level, is locked briefly while the row is being examined so that the row is not modified or deleted while it is being read. These locks are transitory and last only a fraction of a second. Your update application needs to SET LOCK MODE TO WAIT <nseconds>; after connecting to the database so that his update process will wait for that many seconds for locks held by other sessions to be released before returning an error. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 6, 2012 at 12:26 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > A user execute beow SQL in dbaccess. the number of rows of talbe aapf10 is > 550804, It run slowly, and others run some update sql on the same table and > being locked, Do anybody know it is blow sql lead to the lock problem ? > thanks > for your time. > > select aa10dpnoa,round(sum(aa10bal)/10000,0) > from aapf10 > where aa10cur="01" > and aa10subj[1,4] in ("1301","1302","1303","1304","1305", > "1307","1308") > group by 1 > order by 1> > this sql use the wrong execute plan. > > Estimated Cost: 344571 > Estimated # of Rows Returned: 10 > > 1) cbs.aapf10: INDEX PATH > > Filters: (cbs.aapf10.aa10subj[1,4] IN ('1301' , '1302' , '1303' , '1304' , > '1305' , '1307' , '1308' )AND cbs.aapf10.aa10cur = '01' ) > > (1) Index Keys: aa10dpnoa aa10dpnok aa10accod (Serial, fragments: ALL) > > Below are the index definition. > > create unique index "cbs".aalf11 on "cbs".aapf10 (aa10acid) using > > btree in idxdbs ; > create index "cbs".aalf12 on "cbs".aapf10 (aa10ctid) using btree > > in idxdbs ; > create index "cbs".aalf13 on "cbs".aapf10 (aa10cur,aa10subj) using > > btree in idxdbs ; > create index "cbs".aalf14 on "cbs".aapf10 (aa10dpnoa,aa10dpnok, > > aa10accod) using btree in idxdbs ; > create index "cbs".aalf15 on "cbs".aapf10 (aa10actyp,aa10bkno, > > aa10subj) using btree in idxdbs ; > create index "cbs".aalfhc1518 on "cbs".aapf10 (aa10name) using > > btree in idxdbs ; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9d8edd9804b84525f3

hi, we more or less have the same problem. i've been asking the same questions except in our case, inserts are being blocked. http://www.iiug.org/forums/ids/index.cgi/read/26071 thanks

check your isolation level as well. are you in repeatable read? thanks