SQLite/Perl question

Posted on 2007-08-01
Medium Priority
Last Modified: 2012-08-14
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 :)
Question by:joeshin
  • 2
LVL 25

Assisted Solution

clockwatcher earned 200 total points
ID: 19614935
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.
LVL 25

Accepted Solution

clockwatcher earned 200 total points
ID: 19615068
Ok... found a few minutes and the exclusive transactional works fine under perl.  The following locks the database until the user hits enter.

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


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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question