Does creating atomic transaction in django create a lock automatically

Question

I have a code block within transaction.atomic() within a view. My question is does django create some inbuilt table locking behind the scenes.

with transaction.atomic():
    #code block that does database operations
    update_user() #this updates user table
    create_customer_products() #this updates user id to customer products table

The reason is I get a "Lock wait timeout exceeded; try restarting transaction" error when I run the code block.

The setup is django mysql on centos

This is an Error returned from MySQL. https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im — planet260, Oct 05 '17 at 09:47
yes this is an error returned by mysql. And that makes me wonder if there is a lock set by django when transaction.atomic() is used. Because I am not locking the table myself anywhere. — Prabhakar Shanmugam, Oct 05 '17 at 09:53
In order to modify or insert a record in an innodb table, the transaction needs to acquire an exclusive lock in mysql. If the same record (or gap) is already locked by another transaction, then MySQL waits for the lock to be released or the above mentioned timeout occurs. Based on the above code there is no way we can tell what has gone wrong (if anything). You can check out innodb monitor to get more information, but without a deadlock its use will be limited as well. — Shadow, Oct 05 '17 at 10:03
@Shadow I got the answer what I wanted. So Django atomic transaction does create "Locks" behind the scenes. If you can post as an answer and not as a comment I can accept the same. — Prabhakar Shanmugam, Oct 05 '17 at 10:08

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

In order to modify or insert a record in an innodb table, the transaction needs to acquire an exclusive lock in MySQL:

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

...

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

If the same record (or gap) is already locked by another transaction, then MySQL waits for the lock to be released or the above mentioned timeout occurs.

Based on the above code there is no way we can tell what has gone wrong (if anything). You can check out innodb status monitor to get more information, but without a deadlock its use will be limited as well.

This behaviour is intrinsic to MySQL, the application's programming language and libraries cannot influence this.

Does creating atomic transaction in django create a lock automatically

1 Answers1