Transcript Link to Slides

SQL – Transactions in
SQLite
Transaction Lifecycle
It starts with a BEGIN statement (which defines the type of transaction).
Then, there are the statements within the transaction.
The transaction can end a few ways:
◦ With a COMMIT statement (indicating that the database modifications should be
made permanent)
◦ With a ROLLBACK statement (indicating an abort of the transaction and undoing of its
effects)
◦ With an error (which achieves the same effect as ROLLBACK)
We'll discuss ROLLBACK later in the class.
BEGIN and COMMIT
You start a new transaction with "BEGIN TRANSACTION;".
You finish a transaction with "COMMIT TRANSACTION;".
BEGIN TRANSACTION;
INSERT INTO students VALUES ('Josh', 3.5);
UPDATE students SET grade = grade + 0.5;
COMMIT TRANSACTION;
Auto commit and Python - SQLite
By default, SQLite auto commits after every statement. This means that outside
of an explicit transaction, every statement is its own transaction.
When you specify "BEGIN TRANSACTION", auto commit turns off until you say
"COMMIT TRANSACTION" (or "ROLLBACK TRANSACTION").
The python-SQLite module tries to be helpful by adding "BEGINs" and
"COMMITs" for you. We want to turn this behavior off by setting isolation_level
to None (See next slide).
Python-sqlite3 module also has commit (and rollback) methods on it's
Connection objects. DO NOT USE THEM! They don't play nicely with explicit
"BEGIN" and "COMMIT" (and "ROLLBACK").
import sqlite3
con1 = sqlite3.connect("test.db", isolation_level=None)
con2 = sqlite3.connect("test.db", isolation_level=None)
con1.execute("DROP TABLE IF EXISTS students;")
con1.execute("CREATE TABLE students (name TEXT);")
con1.execute("INSERT INTO students VALUES ('Grant');")
con1.execute("BEGIN TRANSACTION;")
con1.execute("INSERT INTO students VALUES ('Josh');")
con2.execute("BEGIN TRANSACTION;")
res = con2.execute("SELECT * FROM STUDENTS;")
print("con2:",list(res)) # con2: [('Grant',)]
res = con1.execute("SELECT * FROM STUDENTS;")
print("con1:", list(res)) # con1: [('Grant',), ('Josh',)]
con2.execute("COMMIT TRANSACTION;")
con1.execute("COMMIT TRANSACTION;")
Normal usage
In the previous example, one thread (one python script) had two connections to
the same database.
In normal usage, different people/programs would be talking to the same
database. Because SQLite uses locking to ensure serializability, it can't commit
database modifications until it holds the exclusive lock.
But it our case, the next python command can't run until the previous command
finished. So if the previous command is waiting for a lock to be released, it will
wait forever.
◦ (or until the default timeout length of 5 seconds passes)
Normally, transactions will take less than a second to run, so lock out isn't a very
big issue.
Database element should SQLite use to
lock with?
Value
Row
Table
Database
Five states of locking the database
UNLOCKED
◦ No locks are held on the database. The database may be neither read nor written. Any internally cached
data is considered suspect and subject to verification against the database file before being used. Other
processes can read or write the database as their own locking states permit. This is the default state.
SHARED
◦ The database may be read but not written. Any number of processes can hold SHARED locks at the
same time, hence there can be many simultaneous readers. But no other thread or process is allowed to
write to the database file while one or more SHARED locks are active.
RESERVED
◦ A RESERVED lock means that the process is planning on writing to the database file at some point in the
future but that it is currently just reading from the file. Only a single RESERVED lock may be active at
one time, though multiple SHARED locks can coexist with a single RESERVED lock. RESERVED differs from
PENDING in that new SHARED locks can be acquired while there is a RESERVED lock.
Five states of locking the database
PENDING
◦ A PENDING lock means that the process holding the lock wants to write to the database as soon as
possible and is just waiting on all current SHARED locks to clear so that it can get an EXCLUSIVE lock. No
new SHARED locks are permitted against the database if a PENDING lock is active, though existing
SHARED locks are allowed to continue.
EXCLUSIVE
◦ An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed
on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to
maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.
SQlite's Three Transaction Modes
The default mode (and the mode I've been demonstrating) is called DEFERRED.
◦ It requests a shared lock the first time it tries to read
◦ It requests an exclusive lock the first time it tries to write
IMMEDIATE transaction mode:
◦ It requests a RESERVED lock immediately (during the "BEGIN" command)
◦ It will fail/wait if another transaction is holding an exclusive lock
◦ It requests an exclusive lock prior to writing
◦ On commit, the reserved lock is promoted to an exclusive lock
EXCLUSIVE transaction mode:
◦ It requests an exclusive lock immediately
◦ It will fail/wait if another transaction is holding a shared or exclusive lock
All transactions release their all their locks when committing
Specifying a transaction mode
BEGIN TRANSACTION; -- Defaults to DEFERRED
BEGIN DEFERRED TRANSACTION; -- Same as above
BEGIN IMMEDIATE TRANSACTION; -- Starts in immediate mode
BEGIN EXCLUSIVE TRANSACTION; -- Starts in exclusive mode
COMMIT TRANSACTION; -- Commits regardless of transaction mode
ROLLBACK TRANSACTION; -- Rollback regardless of transaction mode
Which is the best transaction mode?
Default
Deferred
Immediate
Exclusive