Strategies for dealing w/ database deadlock & lock wait timeout in tasks

[David]

I'm trying to devise the best strategy for dealing with database deadlock and lock wait timeouts within tasks that occasionally will all try to update the same row (based on events from an external API). 

I'm running MySQL 5.1.41 in Read Committed, which is the only thing that the Celery FAQ mentions: http://celery.readthedocs.org/en/latest/faq.html#mysql-is-throwing-deadlock-errors-what-can-i-do). Innodb_lock_wait_timeout has been bumped up to 300, but I'm wondering if a better strategy would be to make this value something really low, add a decorator for tasks that need to update a row, and upon detecting a deadlock or lock wait, just rollback and retry the task. Thanks!

(Running Celery 2.4.4 and Django-celery 2.4.2)

[Harel Malka]

Might not be viable for your scenario, but perhaps you can decouple the database update as a separate task running on a separate queue. 
Although unlike your scenario, I've had a case where a very large amount of inserts would stall the entire database. I took the actual insert action into a separate task and a dedicated queue with 1 or two instances trickling the inserts slowly and reducing the impact on the database. 

Also, are you running under Django? If so try to run it in auto commit mode. For me Django was destroying the database and effectively killing apache due to idle transactions when I ran it without auto commit. 

Harel

--
You received this message because you are subscribed to the Google Groups "celery-users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/celery-users/-/Abg3rCsWV0UJ.
To post to this group, send email to celery...@googlegroups.com.
To unsubscribe from this group, send email to celery-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/celery-users?hl=en.

--

--8<----------------------------------------------------
Harel Malka
Software Architect & Developer
http://www.harelmalka.com
http://www.freecrm.com

[David]

Thanks for the response Harel!

Yes, running under Django - isn't autocommit the default?

Decoupling the update sounds like an interesting short-term solution - rather than simply trickling inserts/updates slowly, this approach would give you the opportunity to batch together some operations or re-order them to reduce deadlock, although this would be a little more involved than just factoring out database code and putting them in separate tasks. Any other thoughts/approaches?

[Harel Malka]

Django, as I painfully learned is not on autocommit by default. 

Add 'autocommit': True to your database config dict. I have no idea why this is not the default because really, django is a database & apache killer without it.

As far as the decoupled update task - try to have the decoupled task be the final update/insert statements. Try to make it atomic in the sense that the task receives simple values and puts them in a table. 

Another option is to limit the number of workers running your problematic task, and yet another option is to use something like memcached to lock out the process from other tasks based on some criteria. 

I.e., if you're working on record types A and B you can place a key in memcached A-12-B-34=True which then means that record A with id of 12 and record B with id of 34 are locked for updates so any task that attempts to work on the same objects will skip it and move on. You clear the lock when you're finished.

--

You received this message because you are subscribed to the Google Groups "celery-users" group.

To view this discussion on the web visit https://groups.google.com/d/msg/celery-users/-/1samlDmd53EJ.

To post to this group, send email to celery...@googlegroups.com.
To unsubscribe from this group, send email to celery-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/celery-users?hl=en.