read program see only some records out of all inserted by another program (isolation level CS)
in [DB2]
|
Prev: nowexam DB2 version 8 certification: Everything you need to know
Next: Result Sets in Stored Procedure
From: ovms on 1 Jun 2010 06:32 Hi, > We are using IBM DB2 in linux platform version 9.7. > We use default CS isolation level and have two programs running > concurrently, one insert records and commit every 200 rows while > another select those inserted records based on a unique sequence > number, from the last obtained sequence number. (select from T1 where > sequence number = last sequence number, fetch 50 rows each time. ) > We found that some of the rows inserted was missed by the select > program. > Any idea why this happen and how to prevent it , if RR isolation level > is not prefered. > PC Further clarify, the reason we found some of the rows inserted was missed was because the select start from the last sequence number for 50 rows, we suspect that some row of higher sequence number was seen by the select statement before some with lower sequence number when they are both committed within same unit of work by the inserting program. The question is: is it normal to have inserted rows partially revealed to another selected program running currently and anyway to avoid this?
From: Anwei Shen on 1 Jun 2010 14:26 On Jun 1, 6:32 am, ovms <patrickclc...(a)gmail.com> wrote: > Hi, > > > We are using IBM DB2 in linux platform version 9.7. > > We use default CS isolation level and have two programs running > > concurrently, one insert records and commit every 200 rows while > > another select those inserted records based on a unique sequence > > number, from the last obtained sequence number. (select from T1 where > > sequence number = last sequence number, fetch 50 rows each time. ) > > We found that some of the rows inserted was missed by the select > > program. > > Any idea why this happen and how to prevent it , if RR isolation level > > is not prefered. > > PC > > Further clarify, the reason we found some of the rows inserted was > missed was because the select start from the last sequence number for > 50 rows, we suspect that some row of higher sequence number was seen > by the select statement before some with lower sequence number when > they are both committed within same unit of work by the inserting > program. > The question is: is it normal to have inserted rows partially > revealed > to another selected program running currently and anyway to avoid > this? So this sequence number is not unique? Every 200 rows share the same sequence number?
From: Mark A on 1 Jun 2010 17:38 > > We are using IBM DB2 in linux platform version 9.7. > > We use default CS isolation level and have two programs running > > concurrently, one insert records and commit every 200 rows while > > another select those inserted records based on a unique sequence > > number, from the last obtained sequence number. (select from T1 where > > sequence number = last sequence number, fetch 50 rows each time. ) > > We found that some of the rows inserted was missed by the select > > program. > > Any idea why this happen and how to prevent it , if RR isolation level > > is not prefered. > > PC > > Further clarify, the reason we found some of the rows inserted was > missed was because the select start from the last sequence number for > 50 rows, we suspect that some row of higher sequence number was seen > by the select statement before some with lower sequence number when > they are both committed within same unit of work by the inserting > program. > The question is: is it normal to have inserted rows partially > revealed > to another selected program running currently and anyway to avoid > this? Do you have an order by in your select Statement? It would help if you provided the exact SQL syntax used and DDL for the table.
From: db2 on 4 Jun 2010 04:14
On Jun 2, 5:38 am, "Mark A" <no...(a)nowhere.com> wrote: > > > We are using IBM DB2 in linux platform version 9.7. > > > We use default CS isolation level and have two programs running > > > concurrently, one insert records and commit every 200 rows while > > > another select those inserted records based on a unique sequence > > > number, from the last obtained sequence number. (select from T1 where > > > sequence number = last sequence number, fetch 50 rows each time. ) > > > We found that some of the rows inserted was missed by the select > > > program. > > > Any idea why this happen and how to prevent it , if RR isolation level > > > is not prefered. > > > PC > > > Further clarify, the reason we found some of the rows inserted was > > missed was because the select start from the last sequence number for > > 50 rows, we suspect that some row of higher sequence number was seen > > by the select statement before some with lower sequence number when > > they are both committed within same unit of work by the inserting > > program. > > The question is: is it normal to have inserted rows partially > > revealed > > to another selected program running currently and anyway to avoid > > this? > > Do you have an order by in your select Statement? It would help if you > provided the exact SQL syntax used and DDL for the table.- Hide quoted text - > > - Show quoted text - Hi, I update more information as below : table creation DDL: ------------------------------------------------ -- DDL Statements for table "ABC "."TBL_ORDER_ACT" ------------------------------------------------ CREATE TABLE "ABC "."TBL_ORDER_ACT" ( "STREAM_ID" SMALLINT NOT NULL , "TLOG_SEQ_NUM" BIGINT NOT NULL , "ROW_TIME" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP , .......... "TRADE_DATE" DATE ) IN "STOORDAC" INDEX IN "ITOORDAC" ; -- DDL Statements for primary key on Table "ABC "."TBL_ORDER_ACT" ALTER TABLE "ABC "."TBL_ORDER_ACT" ADD PRIMARY KEY ("STREAM_ID", "TLOG_SEQ_NUM"); -- DDL Statements for indexes on Table "ABC "."TBL_ORDER_ACT" CREATE INDEX "ABC "."IDX_ORDER_ACT_02" ON "ABC "."TBL_ORDER_ACT" ("TRANS_TIME" ASC) COMPRESS NO ALLOW REVERSE SCANS; -- DDL Statements for indexes on Table "ABC "."TBL_ORDER_ACT" CREATE INDEX "ABC "."IDX_TBLORDER_ACT_BROKER_NUM" ON "ABC "."TBL_ORDER_ACT" ("BROKER_NUM" ASC, "ORDER_SEQ_NUM" ASC) COMPRESS NO ALLOW REVERSE SCANS; -- DDL Statements for indexes on Table "ABC "."TBL_ORDER_ACT" CREATE INDEX "ABC "."IDX_TBLORDER_ACT_STOCK_CODE" ON "ABC "."TBL_ORDER_ACT" ("STOCK_CODE" ASC) COMPRESS NO ALLOW REVERSE SCANS; Two programs running in parallel with the following SQLs: Insert SQL: INSERT INTO ABC.TBL_ORDER_ACT VALUES (1,?,CURRENT TIMESTAMP, 6611,'00939',809,?,'2010-04-07 09:30:00.328077',212137392600328077, 'O',1,'OI','A',6500,'N',0,0,0,0,0,'B','A','Y', 43000,0,43000,0,0,'OG','OG01129001','','','N','N','','1970-01-01', 0,75904,10000010,702,'',0,'2010-04-07','2010-04-07') The TLOG_SEQ_NUM field are being filled with an increasing number for each record by the program. the read SQL: select tlog_seq_num from abc.tbl_order_act where stream_id=1 and tlog_seq_num>${LASTSEQ} order by tlog_seq_num asc fetch first 1000 rows only;" the read SQL each time read from the Lastseq obtained in previous read; we found that it missed a lot of records at the end. We don't find similar problem with similar calls in mainframe z/OS DB2; any light on this are appreciated. PC. |