Databases
StackOverflow2013
Postsdbo
PODatabase transactions - How do they work?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase transactions - How do they work?
    text
    copied!<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> <p>Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload