Who locked my column?

A look into MySQL Transaction Handling

Agenda

• Building up to it ... (a practical application)
• Creating an issue ... (i.e. requests interrupted)
• Understanding ... (locking strategies in action)
• Finding answers ... (silverbullets, hopefully)

Disclaimers

• Brace yourself for MySQL, JSON, PHP
• ... also PSR--1
• If you troll my domain, you are benchmark-testing 1blu
• Do not fear the dark, I am using bash

Building up to it

• Two applications communicating using REST API
• One Webshop
  • fancy Webfrontend
• One ERP system
  • boring fulfillment stuff - who cares...

Ideal communication

• Webshop PUTs `orders` to ERP
• (ERP handles fulfillment)
• Webshop GETs fulfillment `messages` from ERP
• Webshop PATCHes `messages` to acknowledge reception
• simple enough, let's go live...

curl -vX PATCH \
--data '{"processed":1}' \
'http://erp.arrmaniac.de/messages/2?processed=0&ship_state=2' \
| json_pp

Creating an issue

• answering many more stupid Webshop's questions 
• talking to other Webshops (eBay, Amazon, Zalando)
• talking to shipcode generators
• talking to payment providers
• and many other things the owner was not willing to pay for extra...

Creating an issue

• Uses different APIs 
• Takes time
• Means lots of transactions
• overlapping transactions
• seriously, quite a LOT
• The world is cold out there, I tells you.

Creating an issue

Understanding

• We have a concurrent transaction.
• It is obviously badly written.
• As those things typically are.

Understanding

• e.g. "Do PayPal refunds for all messages not refunded"
• A long lasting transaction,
• that locks too many rows or
• that locks columns in the table.

Understanding

BEGIN;
SELECT *
FROM `messages`
WHERE `processed` = 1
FOR UPDATE;
-- ...
-- ...
-- Waiting for COMMIT;
-- But it is not comming...