<img width="150" height="99" src="https://www.codingblocks.net/wp-content/uploads/2023/04/coding-blocks-episode-207-artwork.jpg" class="webfeedsFeaturedVisual wp-post-image" alt="" decoding="async" loading="lazy" style="display: block; margin-bottom: 5px; clear:both;max-width: 100%;" link_thumbnail="" /><img width="150" height="99" src="https://www.codingblocks.net/wp-content/uploads/2023/04/coding-blocks-episode-207-artwork.jpg" class="attachment-thumbnail size-thumbnail wp-post-image" alt="" decoding="async" loading="lazy" style="float:left; margin:0 15px 15px 0;" />
<p>We&#8217;ve got a new / old opening&#8230;Allen goes off / on script?  Michael denies Joe the &#8220;swing&#8221; vote, and Joe is all in on AI assistance</p>



<span id="more-38923"></span>



<p>Testing for concurrency issues is hard because it&#8217;s non-deterministic &#8211; basically you get unlucky due to the timing of things</p>



<h2 id="serializability">Serializability</h2>



<ul>
<li>The problems we&#8217;ve been discussing the past few episodes have been around since the 1970&#8217;s</li>



<li>The answer is always &#8211; just use serializable isolation!</li>



<li>Serializable isolation is the strongest isolation
<ul>
<li>The database prevents ALL race conditions
<ul>
<li>Even if transactions run in parallel, they&#8217;re guaranteed to act/result the same as if they had run one at a time, one after another</li>
</ul>
</li>
</ul>
</li>



<li>If they&#8217;re so much better, why have/use weaker isolation levels?</li>
</ul>



<h3 id="common-implementations">Common Implementations</h3>



<ul>
<li>Executing the transactions serially, actually</li>



<li>Two phase locking &#8211; was one of the only real available solutions for several decades</li>



<li>Optimistic concurrency control &#8211; things like serializable snapshot isolation</li>



<li>We&#8217;ll be talking about these in terms of a single node database</li>
</ul>



<h2 id="actual-serial-execution">Actual Serial Execution</h2>



<ul>
<li>The easiest way to get rid of race conditions is to really just run things one after another &#8211; no concurrency</li>



<li>This was only implemented for the first time around 2007 &#8211; prior the performance was too poor
<ul>
<li>This is truly a loop over transactions submitted to the db engine</li>
</ul>
</li>



<li>What changed to make it possible?
<ul>
<li>RAM became cheap enough to store entire active datasets in memory &#8211; when this is done transactions can execute much faster as you don&#8217;t have to wait to load the data from disk</li>



<li>DB designers concluded that most OLTP transactions are usually short-lived and make a small number of reads and writes &#8211; so they can be run on a consistent snapshot using snapshot isolation outside of the serial execution loop</li>
</ul>
</li>



<li>Used by VoltDB/HStore, Redis, and Datomic
<ul>
<li>Sometimes single-threaded systems can perform better than concurrent ones simply because there&#8217;s no locking
<ul>
<li>However, you&#8217;re bound by a single CPU core</li>



<li>Transactions will need to be set up differently than in typical concurrent systems</li>
</ul>
</li>
</ul>
</li>
</ul>



<h3 id="encapsulating-transactions-in-stored-procedures">Encapsulating transactions in stored procedures</h3>



<ul>
<li>They talked about how the early implementations in db&#8217;s had the intention of making the entire flow part of the transaction &#8211; to book a flight, a person would be shown a list of flights, they&#8217;d choose the one they want, and it&#8217;d be stored
<ul>
<li>The problem with that approach is it can take a long time for that flow to be completed</li>



<li>For that reason, web-applications limit transactions to a single web / http request</li>
</ul>
</li>



<li>There can still be situations where the transaction can occur with multiple interactions between the application and the database
<ul>
<li>From the application, query to see if the seat is still available on the flight&#8230;Ok, it is..now send another query to the db to update the seat to the customer&#8230;now query the db again to get any additional information
<ul>
<li>Doing it this way in a serial transaction db would be too slow because there&#8217;s too much network latency / waiting</li>
</ul>
</li>
</ul>
</li>



<li>In a single-threaded serial transaction, everything must be done all at once in a stored procedure
<ul>
<li>Keeping everything in memory and providing the stored proc everything it needs ensures the transaction is fast without waiting for any network or disk IO
<ul>
<li>Great picture in the book in Figure 7-9 that describes this</li>
</ul>
</li>
</ul>
</li>
</ul>



<h3 id="pros-and-cons-of-stored-procedures">Pros and Cons of Stored Procedures</h3>



<ul>
<li>They&#8217;ve been part of the SQL standard since&#8230;.1999
<ul>
<li>Sometimes get a bad rep
<ul>
<li>Each vendor&#8217;s implementation has their own language</li>



<li>The book mentions that the SQL language hasn&#8217;t kept up with other programming languages and look/are archaic in comparison</li>



<li>It&#8217;s hard to manage code stored on the database server
<ul>
<li>Harder to debug</li>



<li>More difficult to keep in source control</li>



<li>More difficult to test</li>



<li>More difficult to gather metrics for monitoring</li>
</ul>
</li>



<li>Because db&#8217;s are typically shared by many applications or a LOT of application code, non-performant stored procedure code can cause massive problems &#8211; usually worse problems than poorly written application code</li>
</ul>
</li>
</ul>
</li>



<li>These issues have and can be remedied
<ul>
<li>Modern serializable databases use regular programming languages
<ul>
<li>VoltDB &#8211; Java/Groovy</li>



<li>Datomic &#8211; Java/Clojure</li>



<li>Redis &#8211; Lua</li>
</ul>
</li>
</ul>
</li>



<li>When the database is in memory and the transactions are single threaded, stored procedures can actually be quite good
<ul>
<li>Because there&#8217;s no IO / networking overhead, transactions can occur quickly on a single thread</li>
</ul>
</li>



<li>VoltDB also executes stored procedures for replication!
<ul>
<li>This means the stored procedures have to be deterministic &#8211; datetimes have to use deterministic apis</li>
</ul>
</li>
</ul>



<h3 id="partitioning">Partitioning</h3>



<ul>
<li>As mentioned before, doing serial transactions means you are limited to a single core of a single CPU
<ul>
<li>Read only transactions could occur on a separate thread using snapshot isolation</li>



<li>If you need high write throughput, the single thread on a single core could be a problematic bottleneck</li>
</ul>
</li>



<li>This is where partitioning comes into play &#8211; if you can divvy your data up in a way that would allow transactions to stay within a single partition, then you&#8217;ll have the ability to linearly scale your CPU cores/threads to the number of partitions you have
<ul>
<li>If your transaction has to go across multiple partitions, then the stored procedure must ensure that each partition is handled appropriately to keep everything serialized properly</li>



<li>VoltDB can handle multiple partitions
<ul>
<li>Doing cross-partition writes is much slower than single partition writes &#8211; VoltDB reports 1k cross-partition writes per second</li>
</ul>
</li>
</ul>
</li>



<li>Determining if transactions can occur on a single partition takes a bit of planning
<ul>
<li>key-value data is likely a single partition transaction</li>



<li>data with multiple secondary indexes will likely require cross-partition transactions
<ul></ul>
</li>
</ul>
</li>



<li></li>
</ul>



<h2 id="resources-we-like">Resources We Like</h2>



<ul>
<li>Designing Data Intensive Applications<a href="https://www.codingblocks.net/get/designing-data-intensive-applications">https://www.codingblocks.net/get/designing-data-intensive-applications</a></li>
</ul>



<figure class="wp-block-image size-full"><img decoding="async" loading="lazy" width="381" height="499" src="https://www.codingblocks.net/wp-content/uploads/2019/09/designing-data-intensive-applications.jpg" alt="Designing Data Intensive Applications" class="wp-image-8330"/></figure>



<h2 id="tips-of-the-episode">Tips of the Episode</h2>



<ul>
<li>Copilot Labs is an optional extension for Github Copilot that adds some nifty new features to VSCode with Copilot. It installs as a new sidebar icon and has 4 major features:<ul><li>Code explanation &#8211; What does this block of code do? Does the code I wrote do what I think it does?</li><li>Code translation &#8211; Not familiar with a language you&#8217;re reading? Convert it to one that you do!</li><li>IDE brushes &#8211; Modify existing code using a variety of brushes like you would in an art program &#8211; Add Types, Fix bugs, improve readability, resilience, add documentation and it looks like there&#8217;s a way to add custom branches!</li><li>Test generation &#8211; JS and TS only right now</li></ul><a href="https://githubnext.com/projects/copilot-labs/">https://githubnext.com/projects/copilot-labs/</a></li>



<li>from AndrewEver wanted a Windows-like <code>ALT+TAB</code> experience on your Mac? Introducing AltTab for Mac<a href="https://alt-tab-macos.netlify.app/">https://alt-tab-macos.netlify.app/</a></li>



<li>A reason to use the terminal in Visual Studio Code
<ul>
<li>Any operations like a git status that show a list of files are easy to ctrl / cmd click to open directly in the editor</li>
</ul>
</li>



<li>Bitwarden as a LastPass replacement &#8211; Less than 1/3 the price<a href="https://bitwarden.com/pricing/">https://bitwarden.com/pricing/</a><a href="https://www.lastpass.com/pricing">https://www.lastpass.com/pricing</a></li>
</ul>


Podurama

Home

Trending

Personal

Your Podcasts

New Episodes

My Files

Favourite Episodes

Tags (Folders)

Recently Played

Bookmarks

Followed Playlists

Understanding Serial Transactions for Databases like Redis

Coding Blocks