title: Database Schema Design: Tracking User Balance with concurrency
body: <p>In an app that I am developing, we have users who will make deposits into the app and that becomes their balance.</p>
  
  <p>They can use the balance to perform certain actions and also withdraw the balance. What is the schema design that ensures that the user can never withdraw / or perform more than he has, even in concurrency.</p>
  
  <p>For example:</p>
  
  <pre><code>CREATE TABLE user_transaction (
    transaction_id SERIAL NOT NULL PRIMARY KEY,
    change_value   BIGINT NOT NULL,
    user_id        INT NOT NULL REFERENCES user
  )
  </code></pre>
  
  <p>The above schema can keep track of balance, (select sum() from user_transction); However, this does not hold in concurrency. Because the user can post 2 request simultaneously, and two records could be inserted in 2 simultaneous database connections.</p>
  
  <p>I can't do in-app locking either (to ensure only one transcation gets written at a time), because I run multiple web servers.</p>
  
  <p>Is there a database schema design that can ensure correctness? </p>
  
  <p>P.S. Off the top of my head, I can imagine leveraging the uniqueness constraint in SQL. By having later transaction reference earlier transactions, and since each earlier transaction can only be referenced once, that ensures correctness at the database level.</p>