Transactions - difference between SQL, SQLAlchemy and Django ORM

What’s a transaction?

In computing, a transaction is a set of related tasks treated as a single action. Together the tasks form a logical unit of work in which all of them must succeed or none of them can succeed. If some tasks succeed but at least one fails, then all successful tasks are reversed, returning the system to its original state before the transaction was initiated. In the context of a database, a transaction is used to create, update, or retrieve data.

A typical case would be a bank transfer. Peter wants to send $100 to Emma. The process would be to decrease Peter’s amount by $100 and increase Emma’s amount by $100.

Below, we’ll implement this scenario in SQL, SQLAlchemy and Django ORM.

SQL transaction

START TRANSACTION;  — initialize transaction

-- removing 10 from user id 1
UPDATE users SET amount = amount - 100 WHERE username = 1;

-- adding 10 to user with id 2
UPDATE users SET amount = amount + 100 WHERE username = 2;

-- committing the change (or roll back in case of failure)
COMMIT;

In SQL we have to start a transaction explicitly "START TRANSACTION". If all queries are successful, it will be committed. Otherwise, it will be rolled back.

SQLAlchemy transaction

user_1 = User.query.query.filter(id=1).first()
user_1.amount -= 100

user_1 = User.query.query.filter(id=2).first()
user_1.amount += 100

session.commit()

SQLAlchemy is transactional by default. Meaning that we can do a lot of queries and we have to "commit" only once. So we don’t have to "START TRANSACTION" like in SQL. The process is the same: if all queries are successful, they will be committed. Otherwise, they will be rolled back. SQLAlchemy docs.

Django ORM transaction

with transaction.atomic():
    user_1 = User.objects.get(id=1).update(amount-=100)
    user_2 = User.objects.get(id=2).update(amount+=100)

@transaction.atomic
def transaction_view(request):
    user_1 = User.objects.get(id=1).update(amount-=100)
    user_2 = User.objects.get(id=2).update(amount+=100)

Django’s default behavior is to run in auto-commit mode. Each query is immediately committed to the database.

Django ORM is not transactional by default like SQLAlchemy. Each query is immediately committed to the database. We have to use a context manager "with transaction.atomic()" or a decorator "@transaction.atomic" to start a transaction.

If we want to have all of the views "http requests" transactional, we can set "atomic_requests" to True in the configuration of the database. If we want to prevent views from running in a transaction, we can do:

@transaction.non_atomic_requests
def non_atomic_view(request):
    user_1 = User.objects.get(id=1).update(amount-=100)
    user_2 = User.objects.get(id=2).update(amount+=100)

But I wouldn’t recommend it. For more, check the Django docs.