a question of order by , strange behavior
Greeting !!
I've a IDS 11.50.UC7IE running in CentOS Linux , there is a strange behavior
in the following procedure ,I will make it simply !
Table orderconfirm : seqno int,ordno char(10) ;
Table dealfill : seqno int,ordno char(10) ;
create sequence seq_dealsession.nextval increment by 1 start with 1 ;
in esql/c deal.exe , receiving from socket server's data in single thread ,
deal.exe will insert orderconfirm or dealfill in the following insert sql :
EXEC SQL insert into dealfill(seqno,ordno) values
(seq_dealsession.nextval,$ordno)
EXEC SQL insert into orderconfirm(seqno,ordno) values
(seq_dealsession.nextval,$ordno)
since deal.exe receive data in single thread , so I suppose the order I
received will be the same with seq_dealsession sequence number , so far so good
and then another esql/c order.exe , fetch orderconfirm and dealfill every
usleep(10000) :
sprintf(strsql,"%s%s%s%s",
"select seqno, " ,
" ordno " ,
" from orderconfirm where seqno > ?" ,
" order by seqno " ) ;
EXEC SQL prepare idx from $strsql ;
EXEC SQL declare cursorx cursor for idx ;
sprintf(strsql,"%s%s%s%s",
"select seqno, " ,
" ordno " ,
" from dealfill where seqno > ?" ,
" order by seqno " ) ;
EXEC SQL prepare idy from $strsql ;
EXEC SQL declare cursory cursor for idy ;
while(1)
{
usleep(10000) ;
EXEC SQL open cursorx using $last_dealsession_num ;
fprintf(logfp,"open cursorx using
last_dealsession_num=(%d)\\
",last_dealsession_num) ;
while(1)
{
EXEC SQL fetch cursorx into $seqno,$ordno ;
if(sqlca.sqlcode !=0)
break ;
if( (seqno-last_dealsession_num)!=1)
{
fprintf(logfp,"orderconfirm seqno num
skip...seqno=(%d),last_dealsession_num=(%d)\\
",seqno,last_dealsession_num) ;
break ; //break while....orderconfirm fetch
}
else
last_dealsession_num = seqno ;
printf("orderconfirm get (%d)\\
",seqno) ;
fprintf(logfp,"orderconfirm get (%d)\\
",seqno) ;
fprintf(logfp,"(%d)(%s)\\
",seqno,ordno) ;
}//while cursorx
EXEC SQL close cursorx ;
EXEC SQL open cursory using $last_dealsession_num ;
fprintf(logfp,"open cursory using
last_dealsession_num=(%d)\\
",last_dealsession_num) ;
while(1)
{
EXEC SQL fetch cursory into $seqno,$ordno
if(sqlca.sqlcode !=0)
break ;
if( (seqno-last_dealsession_num)!=1)
{
fprintf(logfp,"dealfill seqno num
skip...seqno=(%d),last_dealsession_num=(%d)\\
",seqno,last_dealsession_num) ;
break ; //break while....dealfill fetch
}
else
last_dealsession_num = seqno ;
printf("dealfill get seqno(%d)\\
",seqno) ;
fprintf(logfp,"dealfill get seqno(%d)\\
",seqno) ;
fprintf(logfp,"(%d)(%s)\\
",seqno,ordno) ;
}//while cursory
EXEC SQL close cursory ;
}//endless while
take a look at log , It surprise me the following message:
open cursorx using last_dealsession_num=(12291)
open cursory using last_dealsession_num=(12291)
open cursorx using last_dealsession_num=(12291)
open cursory using last_dealsession_num=(12291)
open cursorx using last_dealsession_num=(12291)
orderconfirm seqno num skip...seqno=(12293),last_dealsession_num=(12291)
open cursory using last_dealsession_num=(12291)
open cursorx using last_dealsession_num=(12291)
orderconfirm get (12292)
(12292)(0010282933)
orderconfirm get (12293)
(12293)(0010282936)
orderconfirm get (12294)
(12294)(0010282939)
as you can see , 12292 and 12293 both exist in orderconfirm , not dealfill ,
but while open cursorx using 12291 , it return 12293 ....
this is the strange thing , because 12292 is skipped !!!
cursorx should order by seqno , so seqno > 12291 should get 12292 first ,
and then 12293 .... in this simple case , dealfill is not involved ,
you can image almost data 12292 and 12293 insert into DB at the same time ,
but cursorx should always order by seqno ...in this case ,
12292 and then 12293 !!
Any suggestion are welcome !! Thanks !!
sorry...
create sequence seq_dealsession , not create sequence seq_dealsession.nextval
, I type it wrong ...
Sorry , forget to mentined that orderconfirm and dealfill both are raw table , seqno in both table are unique index !! Thanks !!