Using a lockfile would seem simplest. See eg. Re: reliable lockfiles? (lock)

One might like to do flock manually or use a CPAN module. Meta::cpan is equipped with many flock modules. The Mutex module was made to work realiably with parallelization, supports threads and processes, including nested sessions.

The path option to Mutex constructs a Mutex::Flock object. One may specify the script path or a temporary path. Typically, the system flock requires read-write access to work. Thus, the Mutex module does a check and emits an error if the path isn't writeable by the calling process-userid.

use Mutex;

my $mutex = Mutex->new( path => $0 );  # choose $0 or tmp path
my $mutex = Mutex->new( path => "/tmp/db.lock" );
 
# The script terminates after some time if a previous
# instance is still running with a held lock.

exit unless $mutex->timedwait( 5 );    # seconds

# Has exclusive lock at this point. Due to using flock,
# the lock is released no matter how the process dies,
# even if omitting $mutex->unlock.

# Do DB insert here.

$mutex->unlock;

# Do other things, optionally.

...
[download]

The example lacks logic for storing the item to insert into a state file, in the event unable to obtain an exclusive lock. Running again with exclusive lock inserts pending items.

my $state_mutex = Mutex->new( path => "/tmp/state.lock" );

$state_mutex->enter( sub {
    # Unable to obtain exclusive lock, then append pending inserts
    # to a state file. Otherwise, read pending items, insert into DB,
    # then unlink or empty the state file.
} );
[download]

Regards, Mario

You could solve this on the database side using transaction boundaries and an exclusive lock via UPDATE statements.

Setting up a lock table:

create table "LockTable" ( LockID integer primary key, LockTable varch
+ar(255) NOT NULL, LockTime datetime NULL);
insert into "LockTable" values (0, '', NULL);
insert into "LockTable" values (1, 'MyTable', NULL);
[download]

Then using it to force INSERT statements to queue up (all INSERT statements have to play by the rules for this to work; not as effective in an ad hoc environment but great if you control the process):

begin transaction;
update "LockTable" set LockTime=CURRENT_TIMESTAMP where LockTable = 'M
+yTable';
insert into "MyTable" values (a, b, c, etc.);
update "LockTable" set LockTime=NULL where LockTable = 'MyTable';
commit transaction;
[download]

This trick works in most DBMS systems, although you do have to pay attention to the specific effects if the transaction isolation level is set other than one might expect. It can be a performance soak, though, so use it only where you need it.

Hi All,

Thanks for your suggestions. It's works perfectly even without locking mysql table. Just create a file in begin of script and lock the script by 'flock($fh, LOCK_EX)' and close this file handler in required place. Thanks a lot hippo

Thanks,
Manimuthu

Hello k_manimuthu,

It is strange how you call your insert script that fast that MySQL is not able to handle the insert statements. The easy way for me would be to connect your scripts either by creating loops and when you reach the end of the loop call next insert.

Alternatively you can use Thread::Queue. But I would first try to see if I can create an OO approach for the insert statements instead of calling the script by each self.

But this is my idea on how to approach the problem, maybe another monk has another idea.

Update: You can also in case you use the MySQL DB you can call the function INSERT ... ON DUPLICATE KEY UPDATE Syntax.

Update2: Or as fellow monk hippo proposed Ensuring only one copy of a perl script is running at a time.

Hope this helps, BR.

The OP never stated that he or she is using MySQL.

Using a queue for this situation is quite sensible, in theory. Can you explain how to implement one with Thread::Queue?



The way forward always starts with a minimal test.

Hello 1nickt,

You are right, the OP never stated that he is using MySQL so I will update the answer.

Regarding Using a queue for this situation is quite sensible, in theory. Can you explain how to implement one with Thread::Queue? I would approach it like this. Example not real time code:

I use forks instead of threads because I get the error:

$ perl test.pl 1 2
This Perl not built to support threads
Compilation failed in require at test.pl line 3.
BEGIN failed--compilation aborted at test.pl line 3.
[download]

Having said that, sample of executable code:

#! /usr/bin/perl
use forks;
use strict;
use warnings;
use Thread::Queue;
use feature 'say';

sub insert_to_db {
    say $_[0];
}

my $q = Thread::Queue->new();    # A new empty queue
# Send work to the threads
$q->enqueue($_) for @ARGV;

# Worker threads
my $thread_limit = 8;

my @thr = map {
    threads->create(sub {
        while (defined (my $item = $q->dequeue_nb())) {
           insert_to_db($item);
        }
            });
} 1..$thread_limit;

# terminate.
$_->join() for @thr;

__END__

$ perl test.pl 1 2
Argument "2.56_01" isn't numeric in numeric ge (>=) at /home/user/perl
+5/lib/perl5/x86_64-linux/forks.pm line 1570.
1
2
[download]

Of course the sample of code, in reality instead of ARG I would queue the number of insert statements and execute them in sequence.

Any way, my approach would be to queue the insert statements in order to complete each insert before the next one.

Hope this clears things more, BR.

Seeking for Perl wisdom...on the process of learning...not there...yet!