Perl and Oracle row locking

Dev Shed Forums Sponsor:

October 25th, 2012, 11:46 AM

jaimitoc30

Contributing User

Join Date: Aug 2012

Posts: 37

Time spent in forums: 6 h 44 m 41 sec
Reputation Power: 1

Perl and Oracle row locking

Hi, guys, I am not sure if this issue is something I should check specifically with Oracle, or with Perl... but hope someone can point me out the correct direction. I have two servers, running a select to one database table. The reason is to have a load balancing. In real time, there will always be inserts in that table and the two processes are responsible to read those rows to process them. Now, if one process have taken some rows I want to lock them, so the other process doesnt take them. I tried using Oracles SELECT... FOR UPDATE .. SKIP LOCKED, however, when running a test, where I insert 10000 rows, I run the two processes and write to a text file the rows that they have read and then delete them, instead of getting 10000 lines in that text file, I get 14000 something sometimes 16000. When I see the text file, I see:

1000,1
1000,2

It means that the row number 1000 was read by both processes and the skip locked didnt work. So I was wondering, is this an issue with Oracle, or with my perl approach. Here is how I made my code:

Code:

$lck = $dbh->prepare("lock table promotions.test IN ROW SHARE MODE"); 
$lck-> execute();  
while(1){ 
$nf = $dbh->selectrow_array("select count(*) from promotions.test"); 
$sth = $dbh->prepare("commit"); $sth->execute(); 
if ($dbh->err =~ /3113|3114/ ){ exit; }; 
if ($nf eq 0){ 	sleep(1);  } else{  
$query = $dbh->prepare("select rowid,testfield from promotions.test where rownum <= ? for update skip locked"); $query->execute(10);   
while ( @row = $query->fetchrow_array() ) { 	
open( F ,">>/opt/vasapp/logs/test_$ARGV[0].log") || die("cannot open file: " . $!); 
print F "$row[1],$ARGV[0]\n"; 	
close F;  	
$upd = $dbh->prepare("delete from promotions.test where rowid = ?"); 	
$upd->execute("$row[0]"); } 
$sth = $dbh->prepare("commit"); $sth->execute();  }  } $dbh->disconnect;

Don't know if there is an issue with the commit, or what else. If someone can point me to the right direction, will be great. Or if there is a best way to do this.

October 25th, 2012, 12:52 PM

Laurent_R

Contributing User

Join Date: Jun 2012

Posts: 502

Time spent in forums: 4 Days 18 h 47 m 12 sec
Reputation Power: 385

I am not an expert on Oracle, but I have the feeling this is a wrong approach. Your locking mechanism seems a bit strange to me, and I doubt that this would work.

I think an alternative approach would be that one process should look at records that meet certain criteria, and the other process the records that don't match the same criteria. The criteria could be the value of one or several fields in the records, a sort of hashing function on, say, the customer number (say, for example, cust number even or odd), or any thing else you can think of (maybe a function on rowid). I have done that many times on some other types of database, running for example ten parallel processes, each of them reading customers whose numbers ended respectively with 0, 1, 2, ..9, and obtained good load balancing and vastly improved performance.

Yet another approach might be to let it run as it currently works and to remove duplicates from the files in a later step.

October 25th, 2012, 01:02 PM

jaimitoc30

Contributing User

Join Date: Aug 2012

Posts: 37

Time spent in forums: 6 h 44 m 41 sec
Reputation Power: 1

Quote:

Originally Posted by Laurent_R

Thank you for your reply. I have another way to do this, but I would like to make the Oracle Locking to work. This is for real time customer request via cell phone. Customer send an SMS, we have an SMSC... the SMSC will send via SMPP to my server where I have an SMPP receiver, the SMPP receiver will insert the record to this table. The two processes will read the request to process them. Now, the issue with the approach you mention will be fine if I add some type of ramdomize in my SMPP receiver from 1 to 2, and it will insert with an ID, 1 or 2, and I can specify the process to select 1 or 2, however, it one server goes down, it will only process the ones with ID 1. The approach es to have a load balance, however if one server goes down, the other one can continue processing all requests. I thought the best approach was to let oracle lock the rows that are being processed by one process so the other one gets the new requests that are not being used by the other process. I would like oracle to handle this 'cause I know it does that, however it is not working. If Oracle handles this, it will be better for my performance. I know there are other approaches, but I would like one approach where Oracle will handle this by locking the rows and leave that task to Oracle and not to the script itself. I know I should be able to do that by using SELECT FOR UPDATE, but I know I am doing something wrong and I would appreciate someone with experience about locks to help me out, or if it was a mistake with my Perl code. Thank you. Regards.

October 25th, 2012, 01:30 PM

OmegaZero

Contributing User

Join Date: May 2007

Posts: 737

Time spent in forums: 3 Weeks 4 Days 22 h 37 m 52 sec
Reputation Power: 928

No experience with Oracle, but I don't see you checking to make sure that the "lock table" statement succeeded (unless you have RaiseError set in the part of the code you haven't shown).

__________________
sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);

October 25th, 2012, 02:05 PM

jaimitoc30

Contributing User

Join Date: Aug 2012

Posts: 37

Time spent in forums: 6 h 44 m 41 sec
Reputation Power: 1

Quote:

Originally Posted by OmegaZero

No experience with Oracle, but I don't see you checking to make sure that the "lock table" statement succeeded (unless you have RaiseError set in the part of the code you haven't shown).

Well, this is what I did. i added RaiseError and also added AutoCommit to 0:

Code:

DBI->connect("dbi:Oracle:xxxxxxxx:1521/xxxxxx", "xxxxxx", 'xxxxxxx',{RaiseError => 1,AutoCommit=>0});

Now it works perfectly. Don't know if it was the autocommit or the raiseerror... but I am glad it works now.

server-host# wc -l *
35096 test_1.log
34904 test_2.log
70000 total

Thank you very much for replying and for your assistance.

October 25th, 2012, 02:17 PM

jaimitoc30

Contributing User

Join Date: Aug 2012

Posts: 37

Time spent in forums: 6 h 44 m 41 sec
Reputation Power: 1

Quote:

Originally Posted by jaimitoc30

Well, this is what I did. i added RaiseError and also added AutoCommit to 0:

Code:

DBI->connect("dbi:Oracle:xxxxxxxx:1521/xxxxxx", "xxxxxx", 'xxxxxxx',{RaiseError => 1,AutoCommit=>0});

After analyzing, I am pretty sure it was due to the autocommit. When Oracle locks rows, the commit automatically releases the locks. So I turned it off, and added the autocommit at the end, so I think it was the issue, just if somebody else is wondering about this.

Perl and Oracle row locking

Developer Shed Advertisers and Affiliates