Update is not working properly with Perl DBI Module

[shree]

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 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 $id (@ids2process){
                $count++;
                my $ret = HandleIds($id);
                next if($ret == -1);

                my $updated = '';
            eval{
                    my $update_ids = $dbh->prepare("UPDATE queue set p
+rocessed = \'T\' where id = $ids");
                    $updated = $update_ids->execute();
            };
            if($@) { $log->error("Unable to update record for - id: $i
+d, updated: $updated");}
            else { $log->info("Updated record for - id: $id, updated: 
+$updated");}

           
           if(($count % $commitLevel  == 0) || ($count == scalar(@ids2
+process)))
                 {
                    my $commited = $dbh->commit;
                    $log->info("COMMIT at $count Ids, commited: $commi
+ted");
                 }

         }
       }else {
                sleep(5);
        }
    }

==== I have attched 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), 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), 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

[dreadpiratepeter]

UPDATE: poster silently changed the code to fix the issue I pointed out below.

where is $id defined? you reference it several times in the loop, but I don't see it declared anywhere. Unless you mean $ids, but your code would not have run under strict then.

[tokpela]

Two things I see (not sure if this will solve your issue):

my $update_ids = $dbh->prepare("UPDATE queue set processed = \'T\' whe
+re id = $ids");

You are preparing the update statement on every while loop iteration. I think you should prepare the update statement before the while loop and use a placeholder for your id value.

my $update_ids = $dbh->prepare("UPDATE queue set processed = 'T' where
+ id = ?");

Also, what is the \'T\' for? 'T' should work fine since you are quoting the SQL with double quotes and don't need to escape the single quote.

[mje]

I guess you've edited that code since dreadpiratepeter commented since I see $id defined in the foreach loop. I commented on your post to dbi-users (which coincidentally used foreach my $ids but I'll reproduce some of it here.

I don't think we can help you because we don't know what HandleIds() does. I note it may return -1 and causes all of the remaining ids in your batch to be skipped. Some further comments below:

You could prepare your update statement once outside of the loop and use placeholders:

my $update_ids = $dbh->prepare(q/update queue set processed = ? where 
+id = ?/);
# later:
$update_ids->execute('T', $ids);

          foreach my $id (@ids2process){
                 $count++;
                my $ret = HandleIds($id);
                next if($ret == -1);

We don't know what HandleIds does and it causes all remaining ids to be ignored. This is probably where your problem is.

            eval{
                    my $update_ids = $dbh->prepare("UPDATE queue set p
+rocessed = \'T
\' where id = $ids");
                     $updated = $update_ids->execute();
            }; 
            if($@) { $log->error("Unable to update record for - id: $i
+d,
updated: $updated");}
            else { $log->info("Updated record for - id: $id, updated:
$updated");}

What if your code is wrong and you pass an id which does not exist in the table - you do not know the update changed nothing. Check $updated contains 1.

There is something wrong with the above logic due to HandleIds() again. If your select returns 10 rows but HandleIds returns -1 for the last id, next causes the loop to end and your commit did not get called. Much better to do:

Personally I don't like turning off AutoCommit like this I prefer to do:

$dbh->begin_work;
  #do my work;
$dbh->commit;

i.e., put the commit outside the loop then you don't care how many ids were processed, you just commit all the updates.

Did you intend to sleep 5s or 10s when no ids were found - you are doing the latter.