SQLite/Perl question

Posted on 2007-08-01
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
    LVL 25

    Assisted Solution

    What version of SQLite are you using?  According to the docs (, 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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
    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.…
    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…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now