Doubts about when to use db.commit()

[Christian Varas]

Hello,

I have doubts about the use of db.commit(). I'm using db.commit() in task.py with celery and this is more or less what I have.

I have one task that updates records massively, and it has around 3 or 4 for loops nested while every loop updates different records. 

My question is where the db.commit() should go. I'dont know if it should go at the end of the whole process or between for loops.

Ex 1:

for i in values:

   db.table1.update_or_insert()

   for x in values2:

      db.table2.insert(=

   for x in values33:

      db.table2.update()

   db.commit()

Ex 2:

for i in values:

   db.table1.update_or_insert()

   for x in values2:

      db.table2.insert(=

   for x in values33:

      db.table2.update()

db.commit()

I've tested and worked in both cases, but I would like to know what is the appropriate place to declare it.

Cheers.

Chris.

[Luca de Alfaro]

This is a complicated question, as it involves also your choice of transaction isolation model (see e.g. https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html; postgres has a different default but similar levels). 

But in general, shorter transactions are better if there is any possibility of conflict.  

For performance, you could also look into doing bulk insert statements with the commit at the end. 

Others can feel free to correct me... 

Luca

--
You received this message because you are subscribed to the Google Groups "py4web" group.
To unsubscribe from this group and stop receiving emails from it, send an email to py4web+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/py4web/CA%2Bs%2BuJvGgaX_t60wwUxN1hz0REMh9%3DLq2DKAj5HqqauhUYdhsw%40mail.gmail.com.

[Christian Varas]

Thanks for the answer Luca.

Cheers.

Chris.