Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!ecngs!feeder2.ecngs.de!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ?
Date: Sat, 13 Apr 2013 16:01:11 -0400
Organization: > Bestiaria Support Staff <
References: <kk4nkd$ain$1@dont-email.me> <kk6tk2$inl$1@dont-email.me> <kk9433$l9s$1@dont-email.me> <kk9ef7$9ll$1@dont-email.me> <kk9s22$i7n$1@dont-email.me> <kka56d$beu$1@dont-email.me> <5168b87f$0$29977$c3e8da3$5496439d@news.astraweb.com> <kkbe5r$isk$1@dont-email.me> <mailman.550.1365853145.3114.python-list@python.org> <kkbmfq$7ed$1@dont-email.me> <CAPTjJmrK6MRaBkcaVjoRiuoZ=z9C+iRz7Q0wbVEfXi8H+u7wqw@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.574.1365883280.3114.python-list@python.org>
Lines: 107
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:43543

On Sun, 14 Apr 2013 00:03:25 +1000, Chris Angelico <rosuav@gmail.com>
declaimed the following in gmane.comp.python.general:

> True ACID compliance demands support at every level:
> 
> 1) The application has to operate in logical units of work, which -
> apart from with DB2 - requires an explicit "BEGIN" query, or
> single-statement transactions.
>
	While SQLite3 normally runs in an auto-commit mode, the Python
DB-API spec, in general, requires that auto-commit be turned off. "The
Definitive Guide to SQLite" states that the Python adapter scans
queries, and will start a transaction if the query is one that will
change data (insert/replace/update). Read-only queries stay auto-commit
until one of the data change queries is submitted and not committed.

> 
> 3) The operating system and filesystem must support a forced file
> synchronization (fsync/fdatasync), so the database engine can wait for
> the data to be written to disk.
> 
> 4) The underlying media (hard disk, SSD, USB stick, etc) must respond
> to the fsync call by actually writing the content to persistent
> storage before returning.
> 
> Failure at any level means the overall system is not ACID compliant.
> PostgreSQL has a huge amount of code in it to try to deal with (or at
> least recognize) a level-3 failure, but nothing in the database engine
> can deal with level 1 or 4 issues.
> 
> You'd have to actually test it. The easiest way is to get two
> computers, side by side, and run the database engine on one and a
> monitor on the other. To test some SSDs at work, I knocked together a
> little program that worked somewhat thus:
> 
> * Connect to the database over TCP/IP (easy, as we were doing this
> with PostgreSQL)

	You don't with SQLite -- or, properly, it is not to an SQLite
port... It would be something like an NFS mounted file share -- and we
all know how uncertain file locking is over NFS. <G>

> * Create a table with a number of rows with an ID and a counter,
> initialized to 0
> * Repeatedly, in parallel, perform a transaction:
>   - Increment the counter on one of the rows (at random)
>   - Increment a "possible" in-memory counter for that row
>   - Commit the database transaction
>   - Increment a "confirmed" in-memory counter for that row
> * When an error of "database seems to be down" is detected, wait for
> it to come up again, then query the table. The counters must all be at
> least their corresponding "possible" value and at most the
> "confirmed".
>
	SQLite is a "file server" database (like M$ JET engine [aka:
"Access"]). It's locking system is multi-stage. It allows multiple
concurrent readers on a "shared" lock state. Only one connection can
perform write operations ("reserved" lock) alongside the readers. A
second connection attempting to perform a write will be rejected with a
database locked condition. Then it really gets nasty -- the writer
attempts to commit the update: The first step is to block other
connections from even entering the read state (the "pending" lock).
However, the writer itself is blocked until all remaining readers have
exited; only then does it have exclusive access to and SQLite makes
changes to the database file itself (prior to that, the writer
connection is changing page images in memory)

	So in your example above, the first process to submit an update
command is going to lock all the others from submitting updates AND will
itself be held from committing the update until all the other processes
have closed (commit or rollback their "read sessions"). Since the Python
adapter basically does auto-commit for all until an update is attempted,
the first process to submit the update will get the reserved lock, and
the other reading sessions can pop in and out freely. On the attempt to
commit, the other sessions will be blocked from even entering a read
session, and there should be no other session trying to start a write
transaction, so the odds are that the commit goes through (there is a
very small window in the locking sequence in which two Python
connections that submit update queries might get into the "shared read"
state, and one then has to back out when the other gets the "reserved"
lock.

	In the commit phase, SQLite first tries to ensure the rollback
journal is flushed to disk -- but that apparently is out of its control;
it can submit a sync command to the OS, but has to rely on what the OS
tells it about the state of the writes to disk (the book indicates that
some IDE drives would lie when queried about sync status, while still
having unwritten data in the on-board buffers). After the rollback
journal it submits the data to the database. I

Crash during journal write: restart finds no journal, that transaction
is lost but the database itself is clean

Crash after journal during database update, restart finds journal,
assumes database is suspect, and rolls back the pages, database is
restored to pre-transaction state

Crash after database sync during removal of journal, restart either
finds journal still there and rolls back the pages restoring to
pretransaction state, or the file was removed from the directory and
SQLite determines database file is good with the last transaction in
place.

-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/