title: Transactions and Order Safety with MySQL (InnoDB)
body: <h1>Scenario</h1>
  
  <p>Say I have a list of voucher codes that I am giving away, I need to ensure that if two persons place an order at the exact same time, that they do not get the same voucher.</p>
  
  <h1>Tables</h1>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `order` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `voucher_id` bigint(20) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `voucher_id` (`voucher_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  ALTER TABLE `order` ADD CONSTRAINT `order_fk` FOREIGN KEY (`voucher_id`) REFERENCES `voucher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  CREATE TABLE IF NOT EXISTS `voucher` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <h1>Sample data</h1>
  
  <pre><code>INSERT INTO `voucher` (`code`) VALUES ('A'), ('B'), ('C');
  </code></pre>
  
  <h1>Sample Query</h1>
  
  <pre><code>SELECT @voucher_id := v.id FROM `voucher` v LEFT JOIN `order` o ON o.voucher_id = v.id WHERE o.id IS NULL;
  INSERT INTO `order` (`voucher_id`) VALUES (@voucher_id);
  </code></pre>
  
  <h1>Question</h1>
  
  <p>I believe the <code>UNIQUE KEY</code> on <code>voucher_id</code> in the <code>order</code> table will prevent two orders having the same <code>voucher_id</code>, giving an error / throwing an exception if the same voucher id is inserted twice. This would require a while loop to retry upon failure.
  The alternative is read locking the vouchers table before the <code>SELECT</code> and releasing that lock after the <code>INSERT</code>, ensuring the same voucher isn't picked twice.</p>
  
  <p>My question is therefore:</p>
  
  <ul>
  <li>Which is faster? 
  <ul>
  <li>A while loop in PHP code.</li>
  <li>Read locking the vouchers table.</li>
  </ul></li>
  <li>Is there another way?</li>
  </ul>
  
  <h3>Edits</h3>
  
  <p><code>ALTER TABLE</code>order<code>CHANGE</code>voucher_id<code></code>voucher_id<code>BIGINT(20) UNSIGNED NOT NULL</code>
  will cause the <code>INSERT</code> to fail if <code>@voucher_id</code> is null (as desired, as there would be no vouchers left).</p>