Yes, you can use SQLite from multiple clients simultaneously. But, in my experience, you should take some caution.

When using autocommit (the default), I found that I could not write to the database too often. Granted, the hard disk was connected to the PC via a slowish USB cable, but after a few minutes of adding new records to the database, I found the hardware couldn't keep up with writing to the database. After a few minutes, I got a Windows dialog box telling me the hard disk hardware wasn't responding. And that was while adding one record every few seconds. Apparently the amount of disk (re)writes is quite huge, compared to the amount of data written.

When I switched to using transactions, manually committing every hundred of new records, I found that you cannot write to the database using another client while another transaction is in progress. Other clients have to wait until it's over, and since my transaction took a few minutes (naive approach), I got timeouts.

You can upper the timeout time (you can change how long a client can wait, the default is a few seconds), but fast, it definitely isn't.

So, the best approach for me for this application was to store the new records in an array of hashes, and flush the array in a short burst transaction to the database every few minutes. Of course, in such a case, the risk of clashes (duplicate records) when writing from multiple clients gets big, because you can't really rely on the database to weed them out when you're generating the records.

So no, not all is well when writing to SQLite from multiple clients. Granted, the fact that my database was connected over a slow USB connection must have made things a lot worse than they could have been with an internal harddisk, but that just is lowering the scale. I'm sure that SQLite really isn't up to the task of heavy updating from many clients, and do it fast.

In reply to Re: Multiple Clients for db_file, sqlite, similar... by bart
in thread Multiple Clients for db_file, sqlite, similar... by blockcipher

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.