relstorage.adapters.sqlite.connmanager

class Sqlite3ConnectionManager(driver, pragmas, path, options)

Bases: AbstractConnectionManager

SQLite doesn’t really have isolation levels in the traditional sense; as far as that goes, it always operates in SERIALIZABLE mode. Instead, the connection’s isolation_level parameter determines how autocommit behaves and the interaction between it at the SQLite and Python levels:

- If it is ``None``, then the Python Connection object has nothing
  to do with transactions. SQLite operates in its default autocommit
  mode, beginning and ending a (read or write, as needed)
  transaction around every statement execution.

- If it is set to IMMEDIATE or DEFERRED, than the Python
  Connection object watches each statement sent
  to ``Cursor.execute`` or ``Cursor.executemany`` to see
  if it's a DML statement (INSERT/UPDATE/DELETE/REPLACE, and
  prior to Python 3.6, basically anything else except a
  SELECT). If it is, and the sqlite level is not already
  in a transaction, then the Connection begins a transaction
  of the specified type before executing the statement. The
  Connection COMMITs when commit() is called or when a DDL
  statement is executed (prior to Python 3.6).

  For SELECT statements, the connection remains in its current state,
  which would be autocommit (read  committed) unless a transaction
  has been opened.

Now, those are all write statements that theoretically would begin a write transaction and take a database lock, so it would seem like there’s no difference between IMMEDIATE and DEFERRED. But there is: recall that temporary tables are actually in a temporary database, so when you write to one of those, sqlite’s internal transaction locks only apply to it. A DEFERRED transaction thus allows other connections to write to their own temporary tables, while an IMMEDIATE transaction blocks other from writing to their temporaries.

We thus tell Python SQLite to operate in DEFERRED mode; for load connections, we must explicitly execute the BEGIN to get a consistent snapshot of the database, but for store, we can let the Python Connection detect when we execute a write operation and begin the transaction then, letting SQLite upgrade the locks to explicit mode when we attempt a write to the main database.

Taking an exclusive lock on the main database can be accomplished with any UPDATE statement, even one that doesn’t match any actual rows.

Parameters:

pragmas (dict) – A map from string pragma name to string pragma value. These will be executed at connection open time. Except for WAL, and a few other critical values, we allow changing just about all the settings, letting the user turn off just about all the safety features so that the user can tune to their liking. The user can also set the max_page_count to operate as a quota system.

open(isolation=None, read_only=False, deferrable=False, replica_selector=None, application_name=None, **kwargs)

Open a database connection and return (conn, cursor).

open_for_pre_pack()

Open a connection to be used for the pre-pack phase. Returns (conn, cursor).

restart_load(conn, cursor, needs_rollback=True)

Reinitialize a connection for loading objects.