SQLite/Perl question

I know that SQLite locks the database exclusively when a write is performed on it.  But is there any way of forcing a lock on it?  For example I want to select some rows, then update those rows.  My concern is that in between the time the rows are selected and when I update the rows that something will get written to the database and make my updates inaccurate.

I would love to have a stored procedure to do this to make it all easy but SQLite does not provide that.  So is there any way of forcing a lock on the database BEFORE I do the select and then unlock it AFTER the update?

In perl :)
joeshinAsked:
Who is Participating?
 
clockwatcherCommented:
Ok... found a few minutes and the exclusive transactional works fine under perl.  The following locks the database until the user hits enter.

#!/usr/bin/perl
use strict;

use DBI;

my $dbh =  DBI->connect("dbi:SQLite:dbname=test.db");

$dbh->do("begin exclusive transaction");  
my $wait = <STDIN>;  # arbitrarily lock the database until user hits enter
$dbh->do("commit");

------------------------------------

Here's what you get when you try and access the database while the above has it held exclusively:

C:\Users\mark\Documents\ee\perl>sqlite3 test.db
SQLite version 3.4.1
Enter ".help" for instructions
sqlite> select * from mytable;
SQL error: database is locked
sqlite> .quit
0
 
clockwatcherCommented:
What version of SQLite are you using?  According to the docs (http://www.sqlite.org/lang_transaction.html), 3.0.8 supports SQL-based exclusive transactions so it should be possible to do what you're asking to do.

Should go something like this: Begin your exclusive transaction.  Issue your select.  Issue your update.  Then your commit.  If it's all on the same handle, I think it'll be treated as an atomic transaction and keep the database locked until the commit or the handle is closed.

Haven't tested it but if no one gets back to you with a definite tested perl solution, I'll try to find some time to mess around with it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.