From: "Dr.Ruud" <[EMAIL PROTECTED]>
"Octavian Rasnita" schreef:
Dr.Ruud:
Octavian Rasnita:
but the problem is that the primary key field which has
autoincrement option skips the ID of each record which is not
inserted because it is already in the database, and I don't want
that.
This skip is normal in a multi-connection environment. You allocate
the next value, and there is just no safe and easy way to unallocate
it. Maybe SQLite has a single-connection mode that behaves
differently, but I don't think so.
So just do a SELECT yourself, before INSERTing. Preferrably in a
batch.
I finally made the program to create a hash with the unique field as
a key, and if the record with that field is in the hash, the program
doesn't need to query the database.
If the record with the field that should be unique is not found in
that hash, the program searches it in the DB and only if it was not
found it is inserted.
This way it works fine.
Unless another process can insert concurrently, in between your SELECT
and INSERT.
Although the program is multi threaded, only a single thread can insert and
2 threads can fetch rows from the DB, so it shouldn't be a problem.
But anyway, I am using something like this:
#Start transaction
$dbh->do("begin");
#Try to insert the records:
foreach my $unique_key(@unique_keys) {
next if $key_seen->{$unique_key};
#Search if this unique key is already in DB:
$rss_article_q->execute($unique_key);
next if $rss_article_q->fetchrow_array;
#Insert the record in the DB:
$rss_article_i->execute(...);
#Put the unique key in the hash:
$key_seen->{$unique_key} = 1;
}
$dbh->do("commit");
So I am using transactions. What do you think, if 2 separate threads would
use this code, will it work? Or it might happen what you said... the first
thread will see that there is no unique key in DB, then the second thread
will insert that unique key, then the first thread will try to insert it and
it will fail.
If this code won't work, can you suggest a better way? I might need to use 2
threads that make the update and I don't want the program to die.
Thank you.
Octavian
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
