|
We use sequences a lot for creating sequential
order numbers. A couple of months back I noticed that some sequences would
just randomly increment by say 10 or 20 for no reason, and they increment
without even calling them from what I can tell.
Then today I was writing some new code and I
had a query that was hanging the server. I just happened to notice that at
around the same time the sequence I was using was incrementing on it's own, and
by a lot more than just one. So I hung the server again on purpose,
stopped it, restarted it,and sure enough the sequence had incremented
by16. I did this a couple more times with the same result. Now
at other times this bug has happened in cases where the server was not hung
and restarted, but this is the only thing I have found where
I can repeat the behavior.
Following is the query we use to get the next
sequence number, the query that causes the server to hang, and the table
definitions involved and the sequence definition. I don't know what
other things could cause the sequence problem, this is just one I was able
to isolate. This is sapdb version 7.3 running on redhat 7.2.
select
orderid_9300.nextval from dual
select
trans_type,sku,descript,quant,orderitems.amount,history.amount,trans_date,auth_code
from orderitems,history where history.trans_id = '$TRANS_ID' and trans_type =
'SALE' or trans_type = 'TEST' and orderitems.trans_id =
history.trans_id
CREATE TABLE "ADMIN"."HISTORY"
(
"TRANS_ID" Char (128) ASCII,
"ORDER_ID" Integer,
"MER_ID" Char (24) ASCII NOT NULL,
"SITE_ID" Char (8) ASCII NOT NULL DEFAULT ' ',
"SALE_TYPE" Char (8) ASCII NOT NULL DEFAULT ' ',
"OUTCOME" Char (2) ASCII NOT NULL DEFAULT ' ',
"AMOUNT" Float (10),
"TRANS_DATE" Date NOT
NULL,
"TRANS_TIME" Char (10) ASCII NOT NULL DEFAULT ' ',
"DAY_OF_WEEK" Char (3) ASCII NOT NULL DEFAULT ' ',
"PYMT_TYPE" Char (1) ASCII NOT NULL DEFAULT ' ',
"PYMT_SUBTYPE" Char (5) ASCII NOT NULL DEFAULT ' ',
"TRANS_TYPE" Char (12) ASCII DEFAULT ' ',
"AUTH_CODE" Char (48) ASCII DEFAULT ' ',
"REMOTE_ADDR" Char (24) ASCII
)
CREATE TABLE "ADMIN"."ORDERITEMS"
(
"TRANS_ID" Char (48) ASCII DEFAULT '0',
"ORDER_ID" Integer NOT NULL DEFAULT 0,
"MER_ID" Char (24) ASCII,
"SITE_ID" Char (8) ASCII,
"SKU" Char (48) ASCII
NOT NULL DEFAULT ' ',
"ITEM_TYPE" Char (12) ASCII NOT NULL DEFAULT ' ',
"DESCRIPT" Char (128) ASCII,
"AMOUNT" Float (10),
"QUANT" Float (10)
)
CREATE SEQUENCE "ORDERID_9300" INCREMENT BY 1 START WITH 10000000 MINVALUE 1 MAXVALUE 99999999999999999999999999999999999999 NOCYCLE NOCACHE
|
