title: Database transactions - How do they work?
body: <p>I'm trying to learn more about database transactions, I found the ACID rule of thumb for writing transactions and thought of a few questions.</p>
  
  <blockquote>
    <p>The ACID rule of thumb:</p>
    
    <p>A transaction must be:</p>
    
    <ol>
    <li>Atomic - it is one unit of work and does not dependent on previous and
    following transactions.</li>
    <li>Consistent - data is either committed or roll back, no
    “in-between” case where something has
    been updated and something hasn’t.</li>
    <li>Isolated - no transaction sees the intermediate results of the current
    transaction.</li>
    <li>Durable - the values persist if the data had been committed even if
    the system crashes right after.</li>
    </ol>
  </blockquote>
  
  <p>I was wondering how they work under the hood, so I can better understand the factors that need to be considered when writing such a transaction. I guess the specific details will vary between the database implementations that are avaliable, but certain rules will always be in place.</p>
  
  <ol>
  <li>How does the database handle concurrent transactions whilst still supporting the Atomic rule?
  
  <ul>
  <li>Is there a queue of transactions that is processed in order?</li>
  <li>How is a lengthy transaction that is holding up all others handled?</li>
  </ul></li>
  <li>Are updates to tables done in memory so if a crash does occur before commit, there is no alteration to the database?
  
  <ul>
  <li>Or are there some intermediate tables that are updated to survive such a crash?</li>
  </ul></li>
  <li>Whilst a transaction is in progress, is all read and write access to the affected tables prevented?
  
  <ul>
  <li>Or would the database allow writes but the transaction would overwrite all changes upon commit?</li>
  </ul></li>
  </ol>