How To Do Two Things At Once

Managing Database Concurrency
With Django

David Seddon

david@seddonym.me

http://seddonym.me

We're hiring!
https://www.growthstreet.co.uk/careers

Concurrent connections

table_one

table_two

table_three

Databases allow many processes at once to modify their data

Transaction

How does your software handle
database concurrency?

a. The database does it automatically

b. I write code to handle it

c. Not applicable (my software has no concurrency)

d. Er, I have no idea

Database concurrency is something you need to think about.

Darren Sedwerb

Developer at Bankity Bank

The code (version 1)

def withdraw(amount, internal_account, external_account):
    "Withdraw money from the internal account to an external bank account."

    if internal_account.has_at_least(amount):
         internal_account.reduce_balance(amount)
         send_money(amount, external_account)
    else:
        raise Exception('Insufficient funds.')

Account balances

Users

Accounts system: version 1

How concurrency breaks stuff

Worker 1

Worker 2

Source balance
£100
£0
-£50

Check balance >= £100

Check balance >= £50

Reduce balance by £100

Reduce balance by £50

if internal_account.has_at_least(amount):
     internal_account.reduce_balance(amount)
     send_money(amount, external_account)

So, how do we
avoid this?

Database isolation levels

SERIALIZABLE

REPEATABLE READ (MySQL default)

READ COMMITTED (PostgreSQL default)

READ UNCOMMITED

strict, accurate

permissive, fast

Database transactions

Transactions are a way of wrapping queries up into discrete blocks

Query

Transaction

Concurrency: reading

Transaction A

Transaction B

id	value
1	0 (1) ?

SET value = 1

SELECT value

What happens?

Isolation mode: READ COMMITTED

Read committed - reading

Records from other sessions will become visible as they are committed

Transaction

id	value
1	0 0 1

SET value = 1

id	value
1	1

Concurrency: reading

Transaction A

Transaction B

id	value
1	0 (1) ?

SET value = 1

SELECT value

What happens?

Isolation mode: READ COMMITTED

Concurrency: reading

Transaction A

Transaction B

id	value
1	0 0 1

SET value = 1

SELECT value

Transaction B reads value 0 straight away.

Isolation mode: READ COMMITTED

Concurrency: writing

Transaction A (writer)

Transaction B (writer)

id	value
1	0 0 ? ? ?

SET value = 1

SET value = 2

Isolation mode: READ COMMITTED

What happens?

Read committed - writing

Records that sessions are writing to are marked as read only immediately.

Other writers will wait until the lock is released.

Transaction

id	value	read only
1	0 0 1	NO YES NO

SET value = 1

id	value
1	1

Concurrency: writing

Transaction A (writer)

Transaction B (writer)

id	value
1	0 0 ? ? ?

SET value = 1

SET value = 2

Isolation mode: READ COMMITTED

What happens?

Concurrency: writing

Transaction A (writer)

Transaction B (writer)

id	value	read only
1	0 0 1 1 2	NO YES NO YES NO

SET value = 1

SET value = 2

Isolation mode: READ COMMITTED

B waits until A commits, then sets value to 2.

Select for update

Transaction A (writer)

Transaction B (writer)

id	value	read only
1	0 0 0 1 1 2	NO YES YES NO YES NO

SET value = 1

SET value = 2

Isolation mode: READ COMMITTED

SELECT FOR UPDATE is a way of making
a read query behave like a write query.

SELECT FOR UPDATE

How can Darren protect against concurrent withdrawals?

Solution: Pessimistic locking

Source balance
£100
£0

SELECT FOR UPDATE

Check balance >= £50

Reduce balance by £100

Worker 1

Worker 2

Check balance >= £100

Internal Account
22

SELECT FOR UPDATE

Insufficient funds!

(waits until worker 1 commits)

from django.db import transaction
from .models import InternalAccount


def withdraw(amount, internal_account, external_account):
    "Withdraw money from the internal account to an external bank account."

    # Wrap in a database transaction
    with transaction.atomic():


        # Wait for a lock on the source account
        InternalAccount.objects.select_for_update().get(id=internal_account.id)
    
        if internal_account.has_at_least(amount):
             internal_account.reduce_balance(amount)
             send_money(amount, external_account)
        else:
            raise Exception('Insufficient funds.')

Pessimistic locking in Django

Select for updates must be wrapped in an atomic transaction.

Summary

Database concurrency is something you need to think about.
Make sure you know what isolation level your database is using, and how concurrent reading and writing is handled.
Select for update makes a read query behave like a write.
Pessimistic locking is a simple way to make your code wait until it's safe.

David Seddon http://seddonym.me

These slides: https://slides.com/davidseddon/db-concurrency

Growth Street - we're hiring:
https://www.growthstreet.co.uk/careers

Bonus:

Common pitfalls

Pitfall 1


def transfer(source, destination, amount):
    "Make a transfer between two InternalAccounts of the supplied amount."

    with transaction.atomic():

        # Wait for a lock on the source and destination accounts
        InternalAccount.objects.select_for_update().filter(id__in=[source.id, destination.id])

        # etc...

Spot the bug

Pitfall 1 - lazy select_for_update

Account.objects.select_for_update().filter(id__in=[source_id, destination_id])

Querysets are lazy!

In this case, the select_for_update will never be run.

bool(Account.objects.select_for_update().filter(id__in=[source_id, destination_id]))

Solution: wrap select_for_updates that use filter in a bool if you don't evaluate them straight away.

Pitfall 2 - Deadlocks

ERROR: deadlock detected
  Detail:
     Process 13560 waits for ShareLock on transaction 3147316424; blocked by process 13566.
     Process 13566 waits for ShareLock on transaction 3147316408; blocked by process 13560.

What the...?

How a deadlock happens

ids = [1, 2]
bool(
  MyModel.objects\
    .select_for_update()\
    .filter(id__in=ids)
)

Process 1

Process 2

id	read only
1	YES
2	YES

id	read only
1	YES
2	YES

Waiting for each other

ids = [2, 1]
bool(
  MyModel.objects\
    .select_for_update()\
    .filter(id__in=ids)
)

Preventing deadlocks

ids = [1, 2]
bool(
  MyModel.objects\
    .select_for_update()\
    .filter(id__in=ids).
    .order_by('id')
)

Process 1

Process 2

ids = [2, 1]
bool(
  MyModel.objects\
    .select_for_update()\
    .filter(id__in=ids).
    .order_by('id')
)

Solution: when using select_for_updates on multiple records, make sure you acquire the locks in a consistent order.

Pitfall 3 - Testing

This wraps your test in a transaction. This can be a problem if you are testing something that doesn't run in a transaction in real life.

Example: a select_for_update included in a celery task that is not wrapped in a transaction, will pass tests but fail when it runs in production.

django.test.TestCase

Does not wrap your test in a transaction. Slower, but better for code where you need to test behaviour relating to transactions.

django.test.TransactionTestCase

Appendix

ATOMIC_REQUESTS

# settings.py

ATOMIC_REQUESTS = True

The request/response cycle will be wrapped in a database transaction.

If an exception is raised, the
transaction is rolled back.

Savepoints

Savepoints allow you to roll back
within transactions

~~Query~~

Transaction

Query

> Savepoint

~~Query~~

> Rollback

Nested atomic blocks

Transaction

Query

> Savepoint

Query

> Savepoint

Query

> Savepoint

Query

with transaction.atomic():
    foo()
    
    with transaction.atomic():
       
        bar()
    





    with transaction.atomic():
       
        baz()


        with transaction.atomic():

            foobar()

Nested atomic blocks

with transaction.atomic():
    
    foo()  - Will be committed
    
    try:
        with transaction.atomic():
            bar()  - Will be rolled back
            raise Exception
    except:
        pass

    baz()  - Will be committed

Exceptions raised within an atomic block will roll back that atomic block.

Both entries must be added, or neither.

If only one entry is added, bad things happen.

Our example accounting system

Amount

-£50

Source ledger

Amount

+£50

Destination ledger

Managing Database Concurrency With Django

By David Seddon

Managing Database Concurrency With Django

Two key concepts you can't afford to ignore

3,833

How To Do Two Things At Once

Managing Database Concurrency With Django

Database concurrency is something you need to think about.

Darren Sedwerb

Developer at Bankity Bank

So, how do we avoid this?

Database transactions

Summary

Bonus:

Common pitfalls

Pitfall 2 - Deadlocks

How a deadlock happens

Preventing deadlocks

Pitfall 3 - Testing

Appendix

Savepoints

Nested atomic blocks

Nested atomic blocks

Managing Database Concurrency With Django

More from David Seddon

Managing Database Concurrency
With Django

So, how do we
avoid this?