<story><title>Practical Guide to SQL Transaction Isolation</title><url>https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html</url></story><parent_chain><item><author>IgorPartola</author><text>One tool to add to your toolbox as a developer is named locks. Say you have a table for orders and a table for order items and a table for payments. Collectively they represent an order, but that is a higher level concept that isn&amp;#x27;t something an RDBMS understands. When the user on your site simultaneously sends two updates, such as &amp;quot;remove item from the cart&amp;quot; and &amp;quot;pay and finish&amp;quot;, you will have a slew of simultaneous updates to the rows across the three tables. Unless you use the highest isolation level, you will get inconsistencies. If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order. You could do an initial read from the orders table with FOR UPDATE which will provide you an implicit lock on that order, but you still have issues with new rows being inserted into your order items table that will not be range locked.&lt;p&gt;So use names&amp;#x2F;advisory locks: create a named lock called &amp;quot;order-1234&amp;quot; where 1234 is tbe ID of the order. Acquire it before doing any manipulations to the order across all its tables. Then release it. Semantics of names locks differ across RDBMS&amp;#x27;s. Unsurprisingly Postgres has saner and more flexible semantics than MySQL, but both are perfectly suitable for the purpose. This technique can save you a ton of headaches.</text></item></parent_chain><comment><author>stickfigure</author><text>&lt;i&gt;If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order.&lt;/i&gt;&lt;p&gt;It sounds like you misunderstand optimistic concurrency. If your transaction touches a piece of data, and that data changes (by another transaction) while your transaction is in progress, then your transaction will rollback on commit. With a tiny bit of library code, your transaction will retry until success. In the case of severe contention you may see repeated retries and timeouts, but you will never see deadlocks - at least one transaction will successfully commit each &amp;quot;round&amp;quot;.&lt;p&gt;I disagree - database locks should not be used by the average developer. Understand optimistic concurrency and use serializable isolation. If (and only &lt;i&gt;if&lt;/i&gt;!) you have a known performance problem with highly contended data, consider a locking strategy. 99 out of 100 developers will never need it, and most of that 1% will botch locks on the first try anyways.&lt;p&gt;If your database doesn&amp;#x27;t support optimistic concurrency, get a better database.</text></comment>
<story><title>Practical Guide to SQL Transaction Isolation</title><url>https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html</url></story><parent_chain><item><author>IgorPartola</author><text>One tool to add to your toolbox as a developer is named locks. Say you have a table for orders and a table for order items and a table for payments. Collectively they represent an order, but that is a higher level concept that isn&amp;#x27;t something an RDBMS understands. When the user on your site simultaneously sends two updates, such as &amp;quot;remove item from the cart&amp;quot; and &amp;quot;pay and finish&amp;quot;, you will have a slew of simultaneous updates to the rows across the three tables. Unless you use the highest isolation level, you will get inconsistencies. If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order. You could do an initial read from the orders table with FOR UPDATE which will provide you an implicit lock on that order, but you still have issues with new rows being inserted into your order items table that will not be range locked.&lt;p&gt;So use names&amp;#x2F;advisory locks: create a named lock called &amp;quot;order-1234&amp;quot; where 1234 is tbe ID of the order. Acquire it before doing any manipulations to the order across all its tables. Then release it. Semantics of names locks differ across RDBMS&amp;#x27;s. Unsurprisingly Postgres has saner and more flexible semantics than MySQL, but both are perfectly suitable for the purpose. This technique can save you a ton of headaches.</text></item></parent_chain><comment><author>lomnakkus</author><text>&amp;gt; So use names&amp;#x2F;advisory locks: create a named lock called &amp;quot;order-1234&amp;quot; where 1234 is tbe ID of the order.&lt;p&gt;If all your locking is single-transaction, then I think a better technique might actually to have a separate &lt;i&gt;table&lt;/i&gt; where you have the individual lock IDs and use SELECT FOR UPDATE on the appropriate row there[1] -- only to obtain the lock. (Of course, most of the caveats you&amp;#x27;ve spelled out in this thread still apply wrt. ordering, etc.)&lt;p&gt;Reasoning: Most named lock&amp;#x2F;advisory lock implementations don&amp;#x27;t participate in the current transaction, so you can end with &amp;quot;stuck&amp;quot; locks where the lock was acquired, but never released because the transaction before the &amp;quot;release&amp;quot; action failed. It goes without saying that this is not a problem that can be solved client-side, so you end up having to have &amp;quot;lock cleanup&amp;quot; (and how can you be sure that cleaning up a lock is actually the right thing, etc.).&lt;p&gt;[1] You might also have to UPSERT rows depending on how &amp;quot;dynamic&amp;quot; your lock IDs are, etc.</text></comment>