Prevent database from locking with multiple ajax posts in django and django-rest-framework

Question

I have the following models

class Peri(models.Model):
    #fields

class Task(models.Model):
    #fields
    peri = models.ForeignKey(Peri)

I am creating a javascript client to create new Peri's and Tasks. I wanted to go easy on my self and when creating a new Peri Model to pre-create the necessary Tasks's. Each Peri will have at most 64 tasks. So I wanted to pre-create them all and then let user edit them from the javascript client/frontend. The creation is done using ajax and django-rest-framework. I use an ajax call to create the peri model and if it is successfull I create the Tasks

$.ajax({
    dataType: 'json',
    url: 'api/url',
    data: newPeriInstance,
    success: function (data, textStatus, jqXHR){
        for (var i = 0; i < 64; i++){
            $.ajax({
                dataType:'json',
                data: newTaskInstance,
                url: '/api/url/tasks/',
                success: function (data, textStatus, jqXHR){
                    //code for success
                },
                error: function (jqXHR, textStatus, errorThrown){
                    //code for error
                }
            });
        }
});

The problem is that I get an error for locked database. I am using sqlit for development stage, but I am not sure it this won't happen in a larger production scale. How can I get passed this issue?

Internal Server Error: /crm/api/peritasks/
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/django/core/handlers/base.py", line 114, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python2.7/dist-packages/django/views/decorators/csrf.py", line 57, in wrapped_view
    return view_func(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/rest_framework/viewsets.py", line 79, in view
    return self.dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/rest_framework/views.py", line 403, in dispatch
    response = self.handle_exception(exc)
  File "/usr/local/lib/python2.7/dist-packages/rest_framework/views.py", line 400, in dispatch
    response = handler(request, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/rest_framework/mixins.py", line 54, increate
    self.object = serializer.save(force_insert=True)
  File "/usr/local/lib/python2.7/dist-packages/rest_framework/serializers.py", line 595, in save
    self.save_object(self.object, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/rest_framework/serializers.py", line 1037, in save_object
    obj.save(**kwargs)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 545, in save
    force_update=force_update, update_fields=update_fields)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 573, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 654, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 687, in _do_insert
    using=using, raw=raw)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py", line 232, in _insert
    return insert_query(self.model, objs, fields, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 1511, in insert_query
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 898, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 69, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/sqlite3/base.py", line 450, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: database is locked

Answer 1

Whilst there is an argument for not pre-creating the Tasks, if there is a requirement for this, you could extend your current API view so that it accepts a number of tasks to create (or always create 64) and build a list and use Django's bulk_create method.

It would depend on which APIView you're using from DRF but as an idea:

class MyApiView(baseview):
    def create(*args):
        model = super(MyAPIView, self).create(*args)
        tasks = []
        for i in range(0, 64):
            tasks.append(Task(...))

        Task.objects.bulk_create(tasks)
        return model

Answer 2

SQLite does not allow row-level locking like many other database systems do, which is one of the many reasons why it's not the greatest development or production database. It only supports database-level locks for write operations, so when you have multiple requests coming in making writes, only one is allowed to do it. Eventually you are guaranteed to hit a timeout or a locking issue, which is what you are seeing here.

So, you have a few options in your situation for preventing the locking.

1. Don't create the tasks ahead of time. This is the easiest way as far as initial implementation, but it requires the rest of your code base to be able to handle partial or nonexistent task lists.

2. Create the tasks lists at the time of the parent object creation. This would require modifying the create method for the Peri model's view, but you would avoid the need for clients to do it. You can use the bulk_create method that Django provides to create all of the tasks in a single transaction, and you wouldn't hit many speed issues.

Answer 3

It's not a good idea to send that many Ajax calls in a for loop. Create your Tasks in your Peri model:

class Peri(models.Model):

    # fields

    def save(self, *args, **kwargs):
        # Create tasks here
        super(Peri, self).save(*args, **kwargs)