SELECT FOR UPDATE with INSERT INTO

Question

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 FOR UPDATE so a lottery ticket (ranging from 1 - 16) could not be sold more than once.

Now I'm wondering, how this FOR UPDATE is working.

I've seen on the web that it is something like :

SELECT * FROM [table] WHERE [column] = [value] FOR UPDATE
UPDATE [table] SET [column] = [new_value]

My question is however regarding the following, would it also work for an INSERT?

My database design:

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;

Which means this design would require a FOR UPDATE similar to:

SELECT * FROM Lottery WHERE id = [id] FOR UPDATE
INSERT INTO LotteryBids SET [LotteryBids.values & Lottery.id]

or

SELECT * FROM Lottery WHERE id = [id] FOR UPDATE
INSERT INTO LotteryBids, Lottery SET [LotteryBids.values] WHERE Lottery.id = [id]

However, I have no idea whether this way of FOR UPDATE would be even slightly possible.

I'm pretty new to this massive data interaction, and I have no idea where to start.

I hope some of you guys could help me.

Kind Regards, Larssy1

---

I assume that the following thoughts would translate to a possible solution:

Thought 1:

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.

Thought 2:

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.

---

Used queries

Select a lottery (including additional information):

SELECT au.*, asp.* FROM Lottery au, LotteryPrefered asp
WHERE au.preferedAuctionId = asp.id AND au.id = '" . $_auctionId . "'

OR (excluding additional information)

SELECT au.* FROM Lottery au
WHERE au.id = '" . $_auctionId . "'

Purchase a ticket:

INSERT INTO LotteryBids (bidId, accountId, auctionId, ticketId, datetime)
VALUES ('" . $guid . "', '" . $_accountId . "', '" . $_auctionId . "',
  '" . $_ticketId . "', NOW())

Answer 1

SELECT ... FOR UPDATE with UPDATE

Using transactions with InnoDB (auto-commit turned off), a SELECT ... FOR UPDATE allows one session to temporarily lock down a particular record (or records) so that no other session can update it. Then, within the same transaction, the session can actually perform an UPDATE on the same record and commit or roll back the transaction. This would allow you to lock down the record so no other session could update it while perhaps you do some other business logic.

This is accomplished with locking. InnoDB utilizes indexes for locking records, so locking an existing record seems easy--simply lock the index for that record.

SELECT ... FOR UPDATE with INSERT

However, to use SELECT ... FOR UPDATE with INSERT, how do you lock an index for a record that doesn't exist yet? If you are using the default isolation level of REPEATABLE READ, InnoDB will also utilize gap locks. As long as you know the id (or even range of ids) to lock, then InnoDB can lock the gap so no other record can be inserted in that gap until we're done with it.

If your id column were an auto-increment column, then SELECT ... FOR UPDATE with INSERT INTO would be problematic because you wouldn't know what the new id was until you inserted it. However, since you know the id that you wish to insert, SELECT ... FOR UPDATE with INSERT will work.

Example

See if ticketid 5 is available for auction 12:

SELECT * FROM LotteryBids
WHERE auctionid = 12 AND ticketid = 5
FOR UPDATE

If no rows returned, then it doesn't exist. Insert it:

INSERT INTO LotteryBids (bidId, accountId, auctionId, ticketId, datetime)
VALUES ('abcd-efgh-ijkl-mnop', 100, 12, 5, NOW())

Keep it simple

Dealing with concurrency issues in such a manner can be difficult to understand, and I've often seen people overly complicate matters. I recommend asking a separate question detailing what you are trying to accomplish, provide your proposed solution, and ask for suggestions.