I think I understand the basics of SQL and ACID properties, but I'm new to
SQLite and not really experienced in any of these. So I'm having some
trouble figuring out the detailed consequences of IMMEDIATE, EXCLUSIVE and
DEFERRED and the autocommit mode of python's sqlite3.
I expect my transactions to be fairly short, and to average three or less
per second, so conflict is likely, but not likely to overload whatever
mechanisms are involved. However, it will be very common for a transaction
to begin with SELECT queries, and to compose an UPDATE based on what it has
and what it finds. It will be quite possible, even frequent, for multiple
processes to decide to update the same records.
As background, I'd like to know what happens with each of the kinds of
transaction. Do any of them do rollbacks, and if so what does that look
like (in Python), Do any of them throw exceptions?
I'm guessing I'm going to want one of IMMEDIATE or EXCLUSIVE, but I'm not
sure which one. I'm also wondering if setting
conn = sqlite3.connect("mydb", isolation_level=IMMEDIATE)
does what I need. Reading the docs, it would appear this does not start a
transaction until the UPDATE, and I think I want the transactions to start
before the first SELECT. Should I instead do
c = conn.cursor()
c.execuite("BEGIN TRANSACTION IMMEDIATE")
and is IMMEDIATE the right thing, or do I need EXCLUSIVE.
That's a bunch of questions, so please answer any where you're _sure_ you
know the answer.
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
sqlite-users mailing list