jmelesky
/
pdorg_site


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320
							<html> <head>
<title>Two Locking War Stories</title>

<style>

body {
  background-color: #dac8c0;
}

.slide {
  font-family: Big Caslon;
  border: 2px solid #887777;
  background-color: #e7e3e0;
  padding: 2%;
  width: 94%;
}

h1 {
  text-align: center;
}

h2 {
  text-align: center;
}

h3 {
  text-align: center;
}

pre {
  border: 1px solid #664444;
  background-color: #fff8ea;
  padding: 2px;
}

.credit {
  padding: 2px;
  font-size: -2;
  text-align: right;
  vertical-align: bottom;
}


</style>

<script src="scripts/jquery-1.2.3.js" type="text/javascript"></script>
<script src="scripts/slideshow.js" type="text/javascript"></script>
</head>

<body>

<div class='slide'>
<h1>Two War Stories</h1>

<h3>(in which Locking appears as a primary antagonist)</h3>

<div><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/></div>

<div class="credit">John Melesky<br/>
(PDX PUG, February 2012)</div>

</div>


<div class='slide'>
<h1>Imagine, if you will...</h1>

</div>


<div class='slide'>
<h1>The log</h1>

<pre><code>SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
</code></pre>

</div>


<div class='slide'>
<h1>The problem</h1>

</div>


<div class='slide'>
<h1>The problem</h1>

<ul>
<li>Even read-only locks need to write</li>
</ul>

</div>


<div class='slide'>
<h1>The problem</h1>

<ul>
<li>Even read-only locks need to write</li>
<li>That write can have contention</li>
</ul>

</div>


<div class='slide'>
<h1>The solution</h1>

</div>


<div class='slide'>
<h1>The conclusion</h1>

</div>


<div class='slide'>
<h1>The conclusion</h1>

<ul>
<li>Concurrency is baaad, mmkay?</li>
</ul>

</div>


<div class='slide'>
<h1>The conclusion</h1>

<ul>
<li>Concurrency is baaad, mmkay?</li>
<li>MySQL had it right all along</li>
</ul>

</div>


<div class='slide'>
<h1>Questions?</h1>

</div>


<div class='slide'>
<h1>Now, imagine....</h1>

</div>


<div class='slide'>
<h1>Egads! Deadlocks?</h1>

</div>


<div class='slide'>
<h1>The culprit(s)</h1>

<pre><code>SELECT * FROM sum_table WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-01' AND '2011-01-10' FOR UPDATE;
SELECT * FROM sum_table WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-03' AND '2011-01-13' FOR UPDATE;
</code></pre>

</div>


<div class='slide'>
<h1>The problem</h1>

</div>


<div class='slide'>
<h1>The problem</h1>

<ul>
<li>Lock acquisition is atomic</li>
</ul>

</div>


<div class='slide'>
<h1>The problem</h1>

<ul>
<li>Lock acquisition is only atomic per-lock</li>
</ul>

</div>


<div class='slide'>
<h1>The problem</h1>

<ul>
<li>Lock acquisition is only atomic per-lock</li>
<li>Locks need to be acquired in a stable order</li>
</ul>

</div>


<div class='slide'>
<h1>The solution</h1>

</div>


<div class='slide'>
<h1>The real problem</h1>

</div>


<div class='slide'>
<h1>The real problem</h1>

<pre><code>UPDATE sum_table SET last_update = now()
  WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-01' and '2011-01-10';
</code></pre>

</div>


<div class='slide'>
<h1>The real solution</h1>

</div>


<div class='slide'>
<h1>The real solution</h1>

<pre><code>UPDATE sum_table SET last_update = now()
  WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-01' and '2011-01-10'
  ORDER BY sum_id, sum_date;
</code></pre>

</div>


<div class='slide'>
<h1>The conclusion</h1>

</div>


<div class='slide'>
<h1>The conclusion</h1>

<ul>
<li>Transactional atomicity is not the same as being deadlock-proof</li>
</ul>

</div>


<div class='slide'>
<h1>The conclusion</h1>

<ul>
<li>Transactional atomicity is not the same as being deadlock-proof</li>
<li>MySQL had it right all along</li>
</ul>

</div>


<div class='slide'>
<h1>The conclusion</h1>

<ul>
<li>Transactional atomicity is not the same as being deadlock-proof</li>
<li><a href="http://dev.mysql.com/doc/refman/5.5/en/update.html">MySQL had it right all along</a></li>
</ul>

</div>


<div class='slide'>
<h1>The MySQL way</h1>

<pre><code>UPDATE t SET id = id + 1;
</code></pre>

</div>


<div class='slide'>
<h1>The MySQL way</h1>

<pre><code>UPDATE t SET id = id + 1 ORDER BY id DESC;
</code></pre>

</div>


<div class='slide'>
<h1>Questions?</h1>

</div>


<div class='slide'>
<h1>Thank you!</h1>

</div>


</body></html>