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]
