How can I ensure a write has completed in PostgreSQL before releasing a lock?
<p>I have a function on a Django model that calculates a value from a PostgreSQL 9.5 database and, based on the result, determines whether to add data in another row. The function must know the value before adding the row, and future values of the calculation will be dependent on the new row.</p>

<p>To enforce these rules, I'm trying to use <a href="https://www.postgresql.org/docs/9.5/static/explicit-locking.html#ADVISORY-LOCKS" rel="nofollow">advisory locks</a>. A simplified version of what I'm trying to do is below:</p>

<pre class="lang-python prettyprint-override"><code>from django.db import connection, models, transaction

...

def create_usage(self, num_credits):
    LOCK_SQL = '''SELECT pg_advisory_lock(1) FROM %s WHERE id = %s'''
    UNLOCK_SQL = '''SELECT pg_advisory_unlock(1) FROM %s WHERE id = %s'''

    cursor = connection.cursor()
    try:
        # Create an advisory lock on the instance's row
        print('obtaining lock for object {}'.format(id(self))
        cursor.execute(LOCK_SQL, [self._meta.db_table, self.id])
        print('obtained lock for object {}'.format(id(self))

        # Perform some read and update when the lock is obtained
        with transaction.atomic():
            # -- SELECT SUM(...) FROM table WHERE ...
            sum_credits = self.credits_used.aggregate(
                sum_credits=models.Sum('num_credits'))['sum_credits']
            print('existing credits: {}'.format(sum_credits))

            if sum_credits &lt; 100 - num_credits:
                print('inserting')
                # -- INSERT INTO table VALUE (...)
                self.credits_used.add(CreditUsage(num_credits=num_credits))
            else:
                print('not inserting')
    finally:
        # Release the lock when done, or when an exception occurs
        print('releasing lock for object {}'.format(id(self))
        cursor.execute(UNLOCK_SQL, [self._meta.db_table, self.id])
        print('released lock for object {}'.format(id(self))
</code></pre>

<p>(this is loosely inspired by <a href="https://www.caktusgroup.com/blog/2009/05/26/explicit-table-locking-with-postgresql-and-django/" rel="nofollow">this Caktus Group post</a>)</p>

<p>I have this code running in several processes connected to the same database. In the console, the order of the <code>'obtaining'</code> and <code>'releasing'</code> print statements are what I expect them to be (i.e., no lock is obtained before some other process releases it), but the data that I get from the database doesn't appear to update like I expect. For example, when running a view (that calls <code>obj.create_usage(5)</code>) in quick succession from different threads I'll get:</p>

<pre class="lang-none prettyprint-override"><code>obtaining lock for object A
obtained lock for object A
existing credits: 90
inserting
releasing lock for object A
obtaining lock for object B
released lock for object A
obtained lock for object B
existing credits: 90
inserting
obtaining lock for object C
releasing lock for object B
released lock for object B
obtained lock for object C
existing credits: 90
inserting
releasing lock for object C
released lock for object C
obtaining lock for object D
obtained lock for object D
existing credits: 95
obtaining lock for object E
inserting
releasing lock for object D
obtained lock for object E
released lock for object D
existing credits: 100
not inserting
releasing lock for object E
released lock for object E
</code></pre>

<p><em>NOTE: I used <code>A</code>, <code>B</code>, <code>C</code>, <code>D</code>, <code>E</code> instead of the objects' numeric IDs for readability.</em></p>

<p>Why wouldn't the writes register before the reads, given the DB locks? I tried without the <code>transaction.atomic</code> at first, but added it thinking that it would make a difference. It did not. Is there a way to enforce the insert to <em><strong>really</strong></em> complete before releasing the advisory lock?</p>

<p>I assume it's because of Django's (or middleware's) transaction management , I'm not completely sure, it's better to test it on your code, but it looks for me like: when you try to acquire a lock, Django might start a new transaction, so when you're actually getting lock at <code>cursor.execute(LOCK_SQL, [self._meta.db_table, self.id])</code> you are already isolated. </p>

<p>While you waiting for lock, another process (with acquired lock) does insert to the database and commits its transaction, but the first process won't see this change when it actually acquires the lock, because transaction has started before.</p>

<p>You could check your application settings for <code>ATOMIC_REQUESTS</code> or any middleware that could enable transactions per request.</p>

python django postgresql transactions locking