I have a code, that should work under concurrent request and heavy load.
I wrote an example to give a better understanding of that I'm trying to do:
def add_tag():
with transaction.atomic():
image = Image.objects.get(pk=2)
tag = Tag.objects.get(pk=6)
image.tags.add(tag) # concurrent insert
return 'done'
class Command(BaseCommand):
def handle(self, *args, **options):
with ProcessPoolExecutor(max_workers=3) as executor:
futures = []
for _ in range(3):
futures.append(executor.submit(add_tag))
for future in as_completed(futures):
print(future.result())
And here is my models:
class Image(models.Model):
title = models.CharField(max_length=255)
tags = models.ManyToManyField('ov_tags.Tag')
class Tag(models.Model):
title = models.CharField(max_length=255)
I'm trying to insert in ManyToMany-relation table in parallel. Obviously, this causes an error, because of READ COMMITED isolation level:
django.db.utils.IntegrityError: duplicate key value violates unique constraint
Absolutely fine, but how to remove this error completely?
To protect my image, I tried to use select_for_update on Image select.
image = Image.objects.select_for_update().get(pk=2)
And... It works! I run it several times. There are no errors anymore and item inserted correctly. But I don't know why?
Is select_for_update locking relational table anyhow? Or is it happening on an application side? Is there a right way to achieve such behavior?
Can I use empty select to lock for insert?
SELECT "image_tags"."tag_id" FROM "image_tags" WHERE ("image_tags"."tag_id" IN (6) AND "image_tags"."image_id" = 2) FOR UPDATE
On a database level, you're only locking the specific Image instance that you're adding tags to. You're correct that this does not prevent inserts into the relational table. If another piece of code ignores the lock and simply inserts a new row in the relation table, you can still run into trouble.
It works for this piece of code because each transaction is "well-behaved". Each transactions first acquires a lock on the specific image, before it adds new entries to the relational table. This means that each process in the executor pool will wait for the current process to finish its transaction before it attempts to add new rows in the relational table.
This would also work if you'd lock the Tag instead of the Image, but it doesn't work if some code locks the Tag, while other code locks the Image. At that point, one process can acquire the lock on the Image, but the other process doesn't wait because it can still acquire the lock on the Tag, and both processes try to insert the same row into the relational table at the same time.
That's what I mean with "well-behaved": each part of your application must behave in a specific way (acquire the same lock). If just one part of your application ignores this requirement, you can run into race conditions. Only if all parts of your application behave well, will you be able to prevent race conditions in this way.
This is exactly what is happening, the select_for_update call is locking the Image table at the database level, so that no other transaction will be able to modify the selected rows until the end of the transaction.atomic block.
See for reference https://docs.djangoproject.com/en/1.11/ref/models/querysets/#select-for-update