title: How can I ensure a write has completed in PostgreSQL before releasing a lock?
body: <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>