On Sun, Apr 14, 2013 at 6:01 AM, Dennis Lee Bieber <wlfr...@ix.netcom.com> wrote: > On Sun, 14 Apr 2013 00:03:25 +1000, Chris Angelico <ros...@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.
Okay, that's good. Point still stands, though, that the application has to use BEGIN/COMMIT correctly; the size of the logical unit of work should be defined by what's one logical action, not by what gives the best performance. >> * 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> Sure, but you could easily make a tiny "SQLite server" that accepts socket connections, reads integers, and writes back "OK" when the transaction's committed. The only difference is that you have to write two halves instead of letting the DB itself be the other half. >> * 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) > 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"). Ah, that'd be a problem. What if each row is in its own file, though? Would that work? That is, instead of: UPDATE durability_test_table SET counter=counter+1 WHERE id=:random_value you use: UPDATE durability_test_:random_value SET counter=counter+1 (except, of course, that SQL parameterization wouldn't work there, so it'd be Python string manipulation) - this way, transactions will lock only against other transactions manipulating the same entry, which is effectively the same as row-level locking. With 2-3 times as many "rows" as threads, there should be very little lock contention. ChrisA -- http://mail.python.org/mailman/listinfo/python-list
