Django ORM and locking table

Question

My problem is as follows:

I have a car dealer A, and a db table named sold_cars. When a car is being sold I create entry in this table.

Table has an integer column named order_no. It should be unique within cars sold by dealer.

So if dealer A sold cars a, b and c, then this column should be 1, 2, 3. I have to use this column, and not a primary key because I don't want to have any holes in my numeration - dealer A and B (which might be added later) should have order numbers 1, 2, 3, and not A: 1, 3, 5, and B: 2, 4, 6. So... I select last greatest order_no for given dealer, increment it by 1 and save.

Problem is that two people bought car from dealer A in the same millisecond and both orders got the same order_no. Any advice? I was thinking of closing this process in a transaction block, and locking this table until the transaction is complete, but can't find any info on how to to that.

Answer 1

I know this question is a bit older, but I just had the same issue and wanted to share my learnings.

I wasn't quite satisfied with st0nes answer, since (at least for postgres) a LOCK TABLE statement can only be issued within a transaction. And although in Django usually almost everything happens within a transaction, this LockingManager does not make sure, that you actually are within a transaction, at least to my understanding. Also I didn't want to completely change the Models Manager just to be able to lock it at one spot and therefore I was more looking for something that works kinda like the with transaction.atomic():, but also locks a given Model.

So I came up with this:

from django.conf import settings from django.db import DEFAULT_DB_ALIAS from django.db.transaction import Atomic, get_connection   class LockedAtomicTransaction(Atomic):     """     Does a atomic transaction, but also locks the entire table for any transactions, for the duration of this     transaction. Although this is the only way to avoid concurrency issues in certain situations, it should be used with     caution, since it has impacts on performance, for obvious reasons...     """     def __init__(self, model, using=None, savepoint=None):         if using is None:             using = DEFAULT_DB_ALIAS         super().__init__(using, savepoint)         self.model = model      def __enter__(self):         super(LockedAtomicTransaction, self).__enter__()          # Make sure not to lock, when sqlite is used, or you'll run into problems while running tests!!!         if settings.DATABASES[self.using]['ENGINE'] != 'django.db.backends.sqlite3':             cursor = None             try:                 cursor = get_connection(self.using).cursor()                 cursor.execute(                     'LOCK TABLE {db_table_name}'.format(db_table_name=self.model._meta.db_table)                 )             finally:                 if cursor and not cursor.closed:                     cursor.close() 

So if I now want to lock the model ModelToLock, this can be used like this:

with LockedAtomicTransaction(ModelToLock):     # do whatever you want to do     ModelToLock.objects.create() 

EDIT: Note that I have only tested this using postgres. But to my understanding, it should also work on mysql just like that.