Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

SQLite UPDATE table lock

by packetstormer (Monk)
on Jul 23, 2012 at 13:05 UTC ( #983167=perlquestion: print w/ replies, xml ) Need Help??
packetstormer has asked for the wisdom of the Perl Monks concerning the following question:

Hello

I am struggling with this SQLite problem. I have a small table that I read from and depending on the results I may shoot off into another function to INSERT/UPDATE the table.
The problem is the SQLite table seems to be locked by the SELECT statement and won't let the update occur. Strangly, the INSERT works fine? The two bits of code is below. I have read the DBD docs and tried to set "sqlite_use_immediate_transaction => 1" but this has no effect.
The error I get is: "DBD::SQLite::st execute failed: database is locked at......."

sub new_dbh { my $dbfile = "shows.db"; my $dbh= DBI->connect("DBI:SQLite:dbname=$dbfile","","",{sqlite_us +e_immediate_transaction => 1,}) or die $DBI::errstr; return $dbh; } sub check_episode_exists { my @s_and_e = @_; my $return; my $dbh = new_dbh(); my ($ins,$skip) = (0,0); foreach my $sh(@s_and_e) { my ($id,$format_name,$title,$overview,$s +how) = split(/\|/,$sh); $show =~ s/-/ /g; print ucfirst($show) . " $format_name - + $title \n"; my $sth = $dbh->prepare("SELECT * from s +easons WHERE id = ? and format_name +=?"); $sth->execute($id,$format_name); my $row = $sth->rows(); if($row == "0") {insert_seasons($id,$for +mat_name,$title,$overview);$ins++;} if($row > 0) {$skip++;update_overview($i +d,$format_name,$overview)} } print "We added $ins\nWe skipped $skip\n"; }
sub update_overview { my $dbh = new_dbh(); my ($id,$format_name,$overview) = @_; my $season = substr($format_name,0,3); my $episode = substr($format_name,3); my $sth = $dbh->prepare("UPDATE seasons SET overview = ? WHERE id = ? AND season = ? AND episode = ?"); $sth->execute($overview,$id,$season,$episode); }

Can anyone spot anything?

Comment on SQLite UPDATE table lock
Select or Download Code
Re: SQLite UPDATE table lock
by Corion (Pope) on Jul 23, 2012 at 13:12 UTC

    In my experience, SQLite only likes one active statement or cursor. So, nesting loops that read/write from tables at the same time generally don't work.

    In your case, it seems it could be enough to ->finish your $sth before calling update_seasons. Alternatively, you could make your SQL more explicit by fetching the count(*) instead of asking the statement how many rows it would return. Then you would also need to use ->fetchall or selectall_arrayref to fetch all data in one go.

      Thanks for the suggestion. Placing the $sth->finish before the update call worked. I should have had COUNT(*) too (that was a typo) so a fetchrow()worked in that instance.
      Thanks

        If you change to select count(*) and call fetchall* you should not need to call finish. Generally speaking, if you are calling finish it should be an indication that you /might/ be doing something wrong.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://983167]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (13)
As of 2014-10-22 13:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (118 votes), past polls