Re: [sqlite] Clarification on file locking in web-served apps
Okay, I think I figured out one piece that I was forgetting Check me on this: SQLite locks the DB during the Open only so long as it takes to read through the DB file. I seem to recall running across that fact somewhere in the FAQs or on this user list I know the general SQL theory on transactions, sorry if I didn't get that across correctly earlier. So in other SQL engines, accessing a table can be done concurrently by several processes, and the granularity for updates and inserts is generally record-only, unless one uses a transaction. I'm sure that slightly over-simplified, but it should be about accurate I know from an earlier email by Richard Hipp... >>> 1. Everytime you call sqlite_open(), SQLite must read and parse the entire database schema. This is surprisingly quick, but you still might want to keep your schema small if you are calling sqlite_open() a lot. 2. SQLite uses course-grained locking. While most client-server database engines lock a table or row of data at a time, SQLite locks the entire database. So if concurrency is an issue for you, it is better to break the data up into separate files so that you can have more (and finer grained) locks. <<< ...that sqlite_open() does have a bite to take from the processing time. I think I see now where I got confused I must have taken statements 1 and 2 and rolled them together in my mind > sqlite_open does not lock the database. You can keep the database open as > long as you like, and whenever you're not actually executing SQL the file > will be unlocked. But I would like to ask a follow up question regarding your comment about keeping SQLite open On a web server, can this be done and shared by all users? Put another way, can I have my web server spawn off a sort of pseudo server for my couldn't-help-but-make-it-huge database so that it only gets "opened" once during the day or week and shares the instance with every web user that hits any of several pages? Thanks so much for taking the time to answer! -- Vania Smrkovski http://www.pandorasdream.com On Wed, 2004-01-14 at 21:36, [EMAIL PROTECTED] wrote: > Vania, > > > > > > Vania Smrkovski <[EMAIL PROTECTED]> > 15/01/2004 12:14 PM > Please respond to vania > > > To: "SQLite-Users (E-mail)" <[EMAIL PROTECTED]> > cc: > Subject:[sqlite] Clarification on file locking in web-served apps > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Clarification on file locking in web-served apps
Vania,
Vania Smrkovski <[EMAIL PROTECTED]>
15/01/2004 12:14 PM
Please respond to vania
To: "SQLite-Users (E-mail)" <[EMAIL PROTECTED]>
cc:
Subject:[sqlite] Clarification on file locking in web-served apps
> Why the heck would anyone even need Transactions in the first place? If
the DB file is locked, no changes can be made, right?
The database file is only locked while a transaction is active. Since a
transaction is implicitly started and ended every time you do an update if
one's not already running this means that whenever you're modifying the
data the whole file is locked. When your transaction ends the file is
unlocked again.
Likewise, when you query the database the file is read-locked for the
duration of the query. In this instance multiple programs (or threads) can
query the database at the same time, but if any query is active updates
have to wait.
sqlite_open does not lock the database. You can keep the database open as
long as you like, and whenever you're not actually executing SQL the file
will be unlocked.
Note that the other thing transactions will give you is a guarantee of
atomicity. Either the whole transaction gets written or none of it does.
If your program crashes (or your machine loses power) before the end of
the transaction, the partial updates will be reversed next time you open
the database.
> So I'm wondering if I am missing a few pieces Is the database file
locked more than once during a process? That is, does it get locked as
the Update statement is getting a collection of rows with which to apply
the intended actions? Does it then release the lock as it prepares the
update on this subset of data, and then re-lock when it prepares to write?
If so, that would explain the need for a Transacction, as it leaves gaps
of access during the transaction.
If you do these as separate SQL statements without a transaction its
possible that someone else can get a write in, in-between your own query
and update.
> Ignoring Transactions for a second, if I have such a Select, will every
user ben locked behind a wall until the Select for user 1 is complete?
> And if this is not the case for Select, will it be so for Update/Insert
of this lenth?
Other selects can operate concurrently, but updates will have to wait
until all selects have finished.
Benjamin
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Clarification on file locking in web-served apps
Hello, Been reading through the Wiki and FAQ documentation on the SQLite site, and I have one point of confusion In an earlier email, I was given some great numbers indicating that, although SQLite locks an entire DB file, the speeds were pretty impressive (given my read of the numbers you gave me) and would be fine. But I then read some info on the use of Transactions and some detail on the locking behavior, and I'm confused As I read it, SQLite locks the database file as a whole, thus encouraging the use of several DB files to minimize multi-user delays. But then I put that in context of the notes on using Transactions Why the heck would anyone even need Transactions in the first place? If the DB file is locked, no changes can be made, right? So I'm wondering if I am missing a few pieces Is the database file locked more than once during a process? That is, does it get locked as the Update statement is getting a collection of rows with which to apply the intended actions? Does it then release the lock as it prepares the update on this subset of data, and then re-lock when it prepares to write? If so, that would explain the need for a Transacction, as it leaves gaps of access during the transaction. If not, then I am mystified My concern stems from my experience with my day job's T-SQL server where there are occasional Select stored procedures and some stored procedures with both Select and Update/Insert blocks that take as long as a minute or two to process. We have some clients represented in our database with years of clientele, and with hundreds of customers per week or even per day. Ignoring Transactions for a second, if I have such a Select, will every user ben locked behind a wall until the Select for user 1 is complete? And if this is not the case for Select, will it be so for Update/Insert of this lenth? My uses of SQLite, via the upcoming PHP5, are not intended for any such huge database extremes, but as in my earlier email, I do want to get a better grasp of its limitations. Since no public server is yet serving PHP5 pages (still in Beta), I can't really test it myself. Thanks for taking the time to help, ___ Vania Smrkovski -- Vania Smrkovski http://www.pandorasdream.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
