You make a sound point. From my perspective the Sqlite synchronization
mechanisms are a flawed part of an otherwise elegantly simple design, as
reading this forum indicates. Synchronization problems are the major
item of confusion among users. A more robust and less intricate
interface would be a productivity improver.
Follow Einstein - "Make it as simple as possible, but no simpler".
We use the mutex approach and are rewarded with simpler logic, no ugly
busy waits or wasteful pauses, more confidence when deploying
applications and no 3AM calls. A sophistication is to use read locks
where appropriate. A read (shared) lock can be easily contrived from
the Windows sync primitives and exists with pthreads.
Threaded programs are here to stay, so the synchronization method would
be better built from threads up instead of at process level with
threading support overlaid later.
Lee Crain wrote:
Guys,
I read this forum regularly and I've given a lot of thought to all of
these conversations about low level logic and trying to squeeze the last
ounce of performance out of SQLite.
That's not for me. Simplicity equates to robustness and my company needs
robustness. And my time is really expensive so I need to not spend too
much time tweaking my SQLite based application to get everything to work
reliably and efficiently.
I've wrapped all of my company's SQLite database accesses in my own API
layer that encapsulates all of our applications' business rules and forces
ALL transactions, no matter how lengthy or trivial, to be atomic by using
a MUTEX to avoid the types of scenarios described below. This includes
simple database accesses, even reads.
Yes, I know it makes things slower. But, it's solid. And I won't be
getting calls at 3:00 a.m. about some mysterious database problem.
If my company wants something faster, they need to buy SQL Server. At
$5000, it's a bargain. At my salary, I'm not.
My 2 cents,
Lee Crain
_________________________
-----Original Message-----
From: Richard Klein [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 24, 2007 10:39 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Race condition -- fixed?
As I was thinking about the locking mechanism in SQLite 3,
it occurred to me that the following race condition could
occur.
Imagine a joint bank account with a balance of $10,000.
The wife makes a withdrawal of $1,000 at ATM 'A' (serviced
by process A in the bank's mainframe), while at the same
time the husband makes a deposit of $1,000 at ATM 'B'
(serviced by process B). The steps performed by each
process are as follows:
Process A
---------
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE accountId = '123-45-6789';
UPDATE accounts SET balance = <balance - withdrawal>
WHERE accountId = '123-45-6789';
COMMIT;
Process B
---------
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE accountId = '123-45-6789';
UPDATE accounts SET balance = <balance + deposit>
WHERE accountId = '123-45-6789';
COMMIT;
Both processes open the accounts database, obtain SHARED
locks, and proceed at about the same pace. Process A
updates her local cache with a new balance of $900, while
process B updates his local cache with a new balance of
$11,000.
Now suppose B gets to the COMMIT first. He tries to get a
PENDING lock and succeeds. He then tries to promote his
PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead,
because process A holds a SHARED lock. So, he goes to sleep,
hoping that when he awakens the SHARED lock will be gone.
Meanwhile, process A reaches her COMMIT, tries to get a
PENDING lock, but gets a SQLITE_BUSY instead, because
process B already holds a PENDING lock. Process A then
releases her SHARED lock (so that process B can be promoted
from PENDING to EXCLUSIVE and do his commit), and goes to
sleep, hoping that when she wakes up the PENDING lock will
be gone.
Process B then wakes up, finds the database UNLOCKED, obtains
his EXCLUSIVE lock, commits his local cache's balance of
$11,000 to the database, releases his lock, and exits.
Process A then wakes up, finds the database UNLOCKED, obtains
an EXCLUSIVE lock, commits her local cache's balance of $9,000
to the database, releases her lock, and exits. *The database
now erroneously shows a balance of $9,000.*
The problem is that the moment that process B commits his local
cache's balance of $11,000 to the database, he causes process A's
local cache to become *stale*, i.e. inconsistent with the database.
After scouring the documentation, I came across the following
article:
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError
which seems to describe the exact scenario I described above.
According to this article, SQLite has been fixed so that if
a process encounters a SQLITE_BUSY during an explicit trans-
action, then the transaction will *automatically* be rolled
back, and the app will receive an error code of SQLITE_IOERR
*instead of* SQLITE_BUSY.
I understand this to mean that whenever coding an explicit
transaction, the programmer must always be prepared to receive
an SQLITE_IOERR when stepping through any SQL statement, and
must deal with this error by going back to the start of the
transaction and starting over.
- Richard Klein
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
