title: SELECT FOR UPDATE with INSERT INTO
body: <p>I have some kind of game lottery up in the making, and I've got advice to switch from MyISAM to the InnoDB and start using <code>FOR UPDATE</code> so a lottery ticket (ranging from 1 - 16) could not be sold more than once.</p>
  
  <p>Now I'm wondering, how this <code>FOR UPDATE</code> is working.</p>
  
  <p>I've seen on the web that it is something like : </p>
  
  <pre><code>SELECT * FROM [table] WHERE [column] = [value] FOR UPDATE
  UPDATE [table] SET [column] = [new_value]
  </code></pre>
  
  <p>My question is however regarding the following, would it also work for an <code>INSERT</code>?</p>
  
  <p>My database design:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `Lottery` (
    `id` varchar(50) NOT NULL,
    `preferedLotteryId` varchar(50) NOT NULL,
    `winningTicketId` int(11) NOT NULL DEFAULT '-1',
    `createdOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
    `startedOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
    `finishedOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
    `active` tinyint(1) NOT NULL,
    `deliveredOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
    `preferedDeliverMethod` int(2) NOT NULL DEFAULT '-1',
    `deliveredMethod` int(2) NOT NULL DEFAULT '-1',
    `deliveredByAccountId` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  CREATE TABLE IF NOT EXISTS `LotteryBid` (
    `bidId` varchar(50) NOT NULL,
    `accountId` varchar(50) NOT NULL,
    `auctionId` varchar(50) NOT NULL,
    `ticketId` int(50) NOT NULL,
    `datetime` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
    PRIMARY KEY (`bidId`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Which means this design would require a <code>FOR UPDATE</code> similar to:</p>
  
  <pre><code>SELECT * FROM Lottery WHERE id = [id] FOR UPDATE
  INSERT INTO LotteryBids SET [LotteryBids.values &amp; Lottery.id]
  </code></pre>
  
  <p>or</p>
  
  <pre><code>SELECT * FROM Lottery WHERE id = [id] FOR UPDATE
  INSERT INTO LotteryBids, Lottery SET [LotteryBids.values] WHERE Lottery.id = [id]
  </code></pre>
  
  <p>However, I have no idea whether this way of <code>FOR UPDATE</code> would be even slightly possible.</p>
  
  <p>I'm pretty new to this massive data interaction, and I have no idea where to start.</p>
  
  <p>I hope some of you guys could help me.</p>
  
  <p>Kind Regards,
  Larssy1</p>
  
  <hr>
  
  <p>I assume that the following thoughts would translate to a possible solution:</p>
  
  <p>Thought 1:</p>
  
  <blockquote>
    <p>A user is purchasing a lottery ticket with ticket id 5. Currently this
    lottery has 3 tickets sold, so I want to lock the row count for this
    lottery until I have committed/finished this insertion.</p>
  </blockquote>
  
  <p>Thought 2:</p>
  
  <blockquote>
    <p>A user is purchasing a lottery ticket with ticket id 5. Currently this
    auction has no ticket with ticket id 5, so i want to reserve an entry
    for this auction with the similar auction id and ticket id. And
    prevent further additions until this query has finished.</p>
  </blockquote>
  
  <hr>
  
  <p><strong>Used queries</strong></p>
  
  <p>Select a lottery (including additional information):</p>
  
  <pre><code>SELECT au.*, asp.* FROM Lottery au, LotteryPrefered asp
  WHERE au.preferedAuctionId = asp.id AND au.id = '" . $_auctionId . "'
  </code></pre>
  
  <p>OR (excluding additional information)</p>
  
  <pre><code>SELECT au.* FROM Lottery au
  WHERE au.id = '" . $_auctionId . "'
  </code></pre>
  
  <p>Purchase a ticket:</p>
  
  <pre><code>INSERT INTO LotteryBids (bidId, accountId, auctionId, ticketId, datetime)
  VALUES ('" . $guid . "', '" . $_accountId . "', '" . $_auctionId . "',
    '" . $_ticketId . "', NOW())
  </code></pre>