Concurrency

Single operations

Every operation takes an instance-level lock, so operations from several threads on one instance are serialised. They take turns; they do not run in parallel.

db = mysql('localhost', 'user', 'password', 'mydb')

# The four threads share the instance and each query waits its turn.
for _ in range(4):
    threading.Thread(target=lambda: db.select('users')).start()

Transactions belong to one thread

A transaction opened with transaction() or begin() belongs to the thread that opened it, until the outermost commit or rollback. While it is open, an operation from any other thread on that instance raises RuntimeError before SQL is sent and before count() and getLastId() change.

# thread A
with db.transaction():
    db.insert('orders', {'customer_id': 7})
    ...

# thread B, same instance, while A is still inside
db.select('orders')      # RuntimeError

The guard covers execute, query, insert, update, delete, select, the batch operations, begin, commit, rollback, close, connect, reconnect and resetSession. A foreign thread cannot enter or exit the owner's transaction() block either.

Nested blocks in the owning thread keep working through savepoints. Ownership is released when the outermost transaction ends.

One instance per thread

For parallel work, and for threads that each need their own transaction, give every thread its own instance:

import threading

local = threading.local()

def db():
    if not hasattr(local, 'conn'):
        local.conn = mysql('localhost', 'user', 'password', 'mydb')
    return local.conn

def work():
    with db().transaction():
        db().insert('events', {'name': 'signup'})

Or an external connection pool. The library does not ship one.

Metadata is per instance

count() and getLastId() describe the last successful operation on the instance. On a shared instance, an operation from another thread replaces them between your write and your read.

result = db.upsert('stock', {'sku': 'A-1', 'units': 10}, update_columns=['units'])
result.affected_rows    # not affected by later operations
result.last_id

upsert(), insert_many() and upsert_many() return a WriteResult, which holds its values independently of the cursor.