Django Transactions and PostgreSQL Locks: Why Your Database Is Slower Than You Think

This is the end of a three-part series on how transactions work in Django. It covers everything from the application and framework code to the database layer.

• Part 1 — Python: How transactions work in Django
• Part 2 — Python: Django, what NOT to include in a transaction

At Q2BSTUDIO, a company specialized in development and technological services, we work with best practices to ensure efficient and cost-effective systems, optimizing every interaction with the database and ensuring proper transaction management.

Summary of Part 2 - What you should NEVER include in a Transaction

In the previous part, we explored which operations and code in the database should be avoided within a transaction to prevent slow responses and potential system crashes. We also explained which elements can be included in a transaction to maintain its atomic behavior.

• Allowed within a transaction:
• Reversible database operations
• Related business logic
• Risky within a transaction:
• Slow queries
• Operations on multiple tables
• Data migrations
• Schema structure changes
• Not allowed within a transaction:
• Irreversible operations
• Blocking calls (such as network requests or file reading)

Managing the code within transactions allows us to control the locks that are held and their duration. This minimizes the execution time of each transaction, reduces database latency, and prevents blocking other critical queries. However, how do database operations interact, and which ones can conflict?

Quick Summary

Database locks exist because multiple users can execute commands simultaneously. For example, if one user tries to delete a table while another is reading its data, this could cause errors or data corruption. To prevent this, each operation acquires and holds a lock that depends on the permissions it requires.

There are two main types of locks in PostgreSQL: table locks and row locks.

• Table-level locks affect the entire structure, making them the most risky. If a table lock is held for too long, other queries will be affected, potentially causing wait times and crashes.
• Row-level locks affect only a specific record. They are less risky, but if a data migration locks each row individually, it could inadvertently lock the entire table.

At Q2BSTUDIO, we optimize the use of transactions in Django to minimize lock risks and ensure optimal system performance.

Operations and Their Impact on Locks

The following operations can affect table access:

• Operations that block everything (reads and writes):
• ALTER INDEX/TABLE
• DROP TABLE
• TRUNCATE
• REINDEX
• CLUSTER
• Operations that block writes (insertion, update, and deletion of data):
• CREATE TRIGGER
• ALTER TABLE
• CREATE INDEX

Django migrations, for example, can contain schema changes that block table access. To evaluate what a migration will do before executing it, it is recommended to use:

./manage.py sqlmigrate APP_NAME MIGRATION_NUM

This will show which operations will be executed and allow you to anticipate if they will cause database locks.

Row Locks and Their Impact

Unlike table locks, row-level locks only block specific records. However, if multiple row modifications are executed within a transaction, the accumulation of locks can interfere with other processes trying to modify the same table.

For example, if within a transaction all rows of a table are iterated to modify data, each row will be locked until the end of the transaction, which could prevent other updates on the same table. In these cases, it is advisable to avoid wrapping the entire operation in a global transaction and instead apply smaller transactions that release their locks once the data for each row is processed.

View the SQL that a Migration Will Execute in Django

./manage.py sqlmigrate APP_NAME MIGRATION_NUMBER/NAME

This command will print the SQL operations that will be executed, allowing you to evaluate if they affect table access. For example, a migration that makes no database changes will only show a comment:

BEGIN;
 - 
 - Alter field transaction_status on transactions
 - 
COMMIT;

While a migration that adds a new column will show an ALTER TABLE command that can block other operations:

BEGIN;
 - 
 - Add field reverted to transactions
 - 
ALTER TABLE 'transactions' ADD COLUMN 'reverted' timestamp with time zone NULL;
COMMIT;

At Q2BSTUDIO, we recommend optimizing migrations to avoid unnecessary locks and ensure database stability.