How to set SQLite isolation levels in Python?

Question

I know that in the SQL standard there are four isolation levels when dealing with transactions:

READ UNCOMMITTED - will allow everything
READ COMMITTED - will not allow dirty reads 
REPEATABLE READ - will not allow dirty, non-repearable reads   
SERIALIZABLE - will not allow dirty, non-repearable, phantom reads

When dealing with MySQL I can do something like:

cursor = db.cursor()
cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

Or, if I'm dealing with Postgresql, I can do something like:

db.set_isolation_level(3) # corresponds to SERIALIZABLE

So, I wonder, if I can do something similar to that when dealing with SQLite.

I've only seen the following but I'm not sure what it means and how I can set other isolation levels (if they exist in the context of SQLite)

db.isolation_level = None

Fredra

Answer 1

There are PRAGMA statements in sqlite. It seems you can do this:

db.execute("PRAGMA read_uncommitted = true;");

Catalog

Thank you, sir! I only want to clarify one tiny thing - does sqlite support all isolation levels? So, will your example work for read_commited, repeatable read and serializable? – Fredra

@Fredra All transactions in SQLite are SERIALIZABLE, unless they are using a shared cache and have read_uncommitted set to true as Martin notes. – Walleye

Answer 2

To expand on Martin's answer...

All transaction in SQLite are SERIALIZABLE, because SQLite controls concurrency via a database-wide read-write lock, so that there can only be one writer at a time.

The exception to this is if you have set up shared cache mode between two or more connections in the same process and enabled the read_uncommited pragma, in which case those connections can operate in READ UNCOMMITED mode (but connections outside of the shared cache will block them as usual).

Walleye

Thank you, sir! But I still do not get one little thing. Does it mean that in order to have truly serizalizable transactions in SQLite using Python, I do not have to do anything like db.isolation_level = None and db.execute("PRAGMA read_uncommitted = true;")? Simple cursor.execute("begin"), cursor.execute(command_1), cursor.execute("command_2"), cursor.execute("commit") will be enough? – Fredra

Answer 3

According to the documentation SQLite supports WAL Mode (in fact, SNAPSHOT isolation level).

See details about isolation in SQLite.

Hammond