Hi,
I have Perl program that will process one by one records from a queue
table and set the flag 'T' to the processed records,
Hence the same processed records should not be process again.
And here the commit will happen in batch, the program will take batch
of 10 records once and process it after
processing each record it will set the flag as 'T' for that record.
And finally after processing 10 records it will do a batch commit.
Here I am facing a serious issue that the Update query which i
prepeare and execute from DBI is not updating the flag as 'T' for all
record,
out of 10 records 2-3 records will not get updated, and the same are
get fetched from the program for next batch.
I have tired with the option inactiveDestory =>1 , still i don't get
any positive result.
I need some help on this the snip of code are as below:
use strict;
use warnings;
my $dsn = 'dbi:Oracle:dbname';
my $user = 'xxxxx';
my $pass = 'xxxxxxxxxxx';
my $attr = {
FetchHashKeyName => 'NAME_lc',
RaiseError => 1,
PrintError => 0,
AutoCommit => 0,
ChopBlanks => 1,
};
my $rowcount = 11;
my $commitLevel = 10;
my $dbh = DBI->connect($dsn, $user, $pass, $attr);
my $queued_recs = $dbh->prepare("SELECT id from (SELECT id FROM queue
WHERE processed=\'F\' ORDER BY id ASC) where ROWNUM < $rowcount");
my $going = 1;
while($going) {
$queued_recs->execute();
my @ids2process;
while ( my @ids = $queued_recs->fetchrow_array ) {
push(@ids2process, $ids[0]);
}
if(!scalar(@ids2process)) {
sleep(5);
}
if(scalar(@ids2process) > 0 ) {
my $count=0;
foreach my $ids (@ids2process){
$count++;
my $ret = HandleIds($id);
next if($ret == -1);
my $updated = '';
eval{
my $update_ids = $dbh->prepare("UPDATE
queue set processed = \'T
\' where id = $ids");
$updated = $update_ids->execute();
};
if($@) { $log->error("Unable to update record for - id:
$id,
updated: $updated");}
else { $log->info("Updated record for - id: $id,
updated:
$updated");}
if(($count % $commitLevel == 0) || ($count ==
scalar(@ids2process)))
{
my $commited = $dbh->commit;
$log->info("COMMIT at $count Ids, commited:
$commited");
}
}
}else {
sleep(5);
}
}
==== I have attached the dbtrace logs as below to get clear picture
====
######### Got the record from queue 1st time
f5e5300 (field=0): '20110...'
<- fetchrow_array= ( '2011051805000001' ) [1 items] row2 at /../../
bin/dbi_ids.pl line 106
-> fetchrow_array for DBD::Oracle::st
(DBI::st=HASH(0xeed86e0)~0xeed8260) thr#ca8f010
dbd_st_fetch 1 fields...
dbd_st_fetched 1 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0xf56ef70)~INNER)
thr#ca8f010
dbd_st_destroy
OCIHandleFree(ee96968,OCI_HTYPE_STMT)=SUCCESS
<- DESTROY= undef at /../../bin/dbi_ids.pl line 234 via
at /../../bin/dbi_ids.pl line 234
########### update flag first time
-> prepare for DBD::Oracle::db (DBI::db=HASH(0xee44ef0)~0xee44e40
'UPDATE queue set flag = 'T' where id = 2011051805000001')
thr#ca8f010
New 'DBI::st' (for DBD::Oracle::st,
parent=DBI::db=HASH(0xee44e40), id=undef)
dbih_setup_handle(DBI::st=HASH(0xf58bfc0)=>DBI::st=HASH(0xf573a30),
DBD::Oracle::st, f576c20, Null!)
dbih_make_com(DBI::db=HASH(0xee44e40), ee4b1d0, DBD::Oracle::st,
464, 0) thr#ca8f010
dbih_setup_attrib(DBI::st=HASH(0xf573a30), Err,
DBI::db=HASH(0xee44e40)) SCALAR(0xd903fe0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), State,
DBI::db=HASH(0xee44e40)) SCALAR(0xd911f90) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), Errstr,
DBI::db=HASH(0xee44e40)) SCALAR(0xd904020) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), TraceLevel,
DBI::db=HASH(0xee44e40)) 12 (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), FetchHashKeyName,
DBI::db=HASH(0xee44e40)) 'NAME_lc' (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), HandleSetErr,
DBI::db=HASH(0xee44e40)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), HandleError,
DBI::db=HASH(0xee44e40)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), ReadOnly,
DBI::db=HASH(0xee44e40)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0xf573a30), Profile,
DBI::db=HASH(0xee44e40)) undef (not defined)
OCIHandleAlloc(ee6eb50,f856898,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(ee96968,ee90bf8,'UPDATE queue set flag = 'T'
where id = 2011051805000001',71,1,0)=SUCCESS
OCIAttrGet(ee96968,OCI_HTYPE_STMT,f8568ac,
0,OCI_ATTR_STMT_TYPE,ee90bf8)=SUCCESS
dbd_st_prepare'd sql UPDATE (pl1, auto_lob1, check_sql1)
dbd_describe skipped for UPDATE
<- prepare= DBI::st=HASH(0xf58bfc0) at /../../bin/dbi_ids.pl line
127 via at /../../bin/dbi_ids.pl line 126
-> execute for DBD::Oracle::st (DBI::st=HASH(0xf58bfc0)~0xf573a30)
thr#ca8f010
dbd_st_execute UPDATE (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
OCIStmtExecute(ee90b28,ee96968,ee90bf8,1,0,0,0,mode=DEFAULT,
0)=SUCCESS
OCIAttrGet(ee96968,OCI_HTYPE_STMT,
7fff9d2c53b0,0,OCI_ATTR_ROW_COUNT,ee90bf8)=SUCCESS
OCIAttrGet(ee96968,OCI_HTYPE_STMT,
7fff9d2c53b6,0,OCI_ATTR_SQLFNCODE,ee90bf8)=SUCCESS
dbd_st_execute UPDATE returned (SUCCESS, rpc1, fn5, out0)
<- execute= 1 at /../../bin/dbi_ids.pl line 128 via at /../../
bin/dbi_ids.pl line 126
-> finish for DBD::Oracle::st (DBI::st=HASH(0xf58bfc0)~0xf573a30)
thr#ca8f010
<- finish= 1 at /../../bin/dbi_ids.pl line 129 via at /../../
bin/dbi_ids.pl line 126
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0xf573a30)~INNER)
thr#ca8f010
dbd_st_destroy
######### again for second batch even i updated the flag as 'T' while
processing 1st time in 1st batch
<- fetchrow_array= ( '2011051805000001' ) [1 items] row1 at
/../../
bin/dbi_ids.pl line 106
-> fetchrow_array for DBD::Oracle::st
(DBI::st=HASH(0xeed8260)~0xeed86e0) thr#ca8f010
dbd_st_fetch 1 fields...
dbd_st_fetched 1 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)
######### again updated for 2nd time and this time the update was
sucussful and the same id never came agagin to process for further
batchers
-> prepare for DBD::Oracle::db (DBI::db=HASH(0xee44ef0)~0xee44e40
'UPDATE queue set flag = 'T' where id = 2011051805000001')
thr#ca8f010
New 'DBI::st' (for DBD::Oracle::st,
parent=DBI::db=HASH(0xee44e40), id=undef)
dbih_setup_handle(DBI::st=HASH(0xf4bdcb0)=>DBI::st=HASH(0xf5913d0),
DBD::Oracle::st, f5916c0, Null!)
dbih_make_com(DBI::db=HASH(0xee44e40), ee4b1d0, DBD::Oracle::st,
464, 0) thr#ca8f010
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), Err,
DBI::db=HASH(0xee44e40)) SCALAR(0xd903fe0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), State,
DBI::db=HASH(0xee44e40)) SCALAR(0xd911f90) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), Errstr,
DBI::db=HASH(0xee44e40)) SCALAR(0xd904020) (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), TraceLevel,
DBI::db=HASH(0xee44e40)) 12 (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), FetchHashKeyName,
DBI::db=HASH(0xee44e40)) 'NAME_lc' (already defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), HandleSetErr,
DBI::db=HASH(0xee44e40)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), HandleError,
DBI::db=HASH(0xee44e40)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), ReadOnly,
DBI::db=HASH(0xee44e40)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0xf5913d0), Profile,
DBI::db=HASH(0xee44e40)) undef (not defined)
OCIHandleAlloc(ee6eb50,f856898,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(ee96968,ee90bf8,'UPDATE queue set flag = 'T'
where id = 2011051805000001',71,1,0)=SUCCESS
OCIAttrGet(ee96968,OCI_HTYPE_STMT,f8568ac,
0,OCI_ATTR_STMT_TYPE,ee90bf8)=SUCCESS
dbd_st_prepare'd sql UPDATE (pl1, auto_lob1, check_sql1)
dbd_describe skipped for UPDATE
<- prepare= DBI::st=HASH(0xf4bdcb0) at /../../bin/dbi_ids.pl line
127 via at /../../bin/dbi_ids.pl line 126
-> execute for DBD::Oracle::st (DBI::st=HASH(0xf4bdcb0)~0xf5913d0)
thr#ca8f010
dbd_st_execute UPDATE (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
OCIStmtExecute(ee90b28,ee96968,ee90bf8,1,0,0,0,mode=DEFAULT,
0)=SUCCESS
OCIAttrGet(ee96968,OCI_HTYPE_STMT,
7fff9d2c53b0,0,OCI_ATTR_ROW_COUNT,ee90bf8)=SUCCESS
OCIAttrGet(ee96968,OCI_HTYPE_STMT,
7fff9d2c53b6,0,OCI_ATTR_SQLFNCODE,ee90bf8)=SUCCESS
dbd_st_execute UPDATE returned (SUCCESS, rpc1, fn5, out0)
<- execute= 1 at /../../bin/dbi_ids.pl line 128 via at /../../
bin/dbi_ids.pl line 126
-> finish for DBD::Oracle::st (DBI::st=HASH(0xf4bdcb0)~0xf5913d0)
thr#ca8f010
<- finish= 1 at /../../bin/dbi_ids.pl line 129 via at /../../
bin/dbi_ids.pl line 126
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0xf5913d0)~INNER)
thr#ca8f010
dbd_st_destroy
OCIHandleFree(ee96968,OCI_HTYPE_STMT)=SUCCESS
