Hopefully, I'm missing or misinterpreting something, because I don't see an explicit SELECT ... LOCK IN SHARE MODE as a viable way to approach this.
Using SELECT ... LOCK IN SHARE MODE explicitly sets an IS lock ("intention shared") on the row(s) matched, but other threads can also set IS locks on the same row(s) at the same time, with no feedback from the server indicating whether anyone else already has such a lock on the row.
So, let's say two threads set an IS lock on the row, then both of them issue an UPDATE.
One unlucky thread sees this:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
It's a deadlock because two threads are asking for mutually-exclusive things. Thread #1 is asking for its IS lock to be escalated to an X (exclusive) lock so it can update the row... but this can't be permitted because thread #2 already has an IS lock, which prevents #1 from getting its X lock ... and, thread #2 is also trying to escalate to an X lock, which is being blocked by thread #1's IS lock.
You might assume that the first thread to request the lock would be the one that gets to update the row, but that's not the case, either. My testing shows that in this case the winning thread is the one whose UPDATE is noticed first, not the one that gets an IS lock first. This is sensible, since that thread has done slightly more work than the other, and, after all, both threads had previously only expressed the intention to lock the row exclusively.
But... with all of that said, it seems to me that there's a much simpler approach to this, that would be far easier on your server.
If visitors to your site are trying to claim one specific timeslot, identified by a unique timeslot_id, then I'm not sure why this approach wouldn't work:
START TRANSACTION;
UPDATE timeslot_guest_map SET guest_id = ? WHERE timeslot_id = ? AND guest_id IS NULL;
SELECT ROW_COUNT();
COMMIT;
If ROW_COUNT() returns '1' then you got the time slot. If ROW_COUNT() returns 0 then sorry, someone else got there first. In this setup, InnoDB implicitly handles the row locking, and thread #2 will block until thread #1 commits, at which time guest_id will no longer be null on that row so it will not be updated.
On the other hand, if the "timeslots" are actually identical, such as might be the case when 150 "general admission" seats were available in an auditorium, the approach could be like this:
START TRANSACTION;
UPDATE timeslot SET user_id = ? WHERE user_id IS NULL LIMIT 1;
SELECT ROW_COUNT();
COMMIT;
Here, thread #1 implicitly gets an X lock on the first available row, updates it, and commits. Thread #2 has to wait for an implicit IS lock on the first row, because of the X held by thread #1... but when thread #1 commits, thread #2 gets its IS lock so it can examine that row, and finds out that the row no longer matches... so the next available row gets matched, locked, and updated, if there is one.
The workload involved in either of these scenarios sounds to me like it should be trivial for MySQL, assuming you take care to properly commit (or rollback) your transactions. Otherwise, you'll end up with a backlog of threads waiting on locks held by abandoned transactions.
As @Valor suggested, you're far more likely to have problems providing your web server with the resources it will need in order to handle the concurrent connections... as was likely the case here and here, to cite a couple of recent examples of MySQL being the victim of memory exhaustion on the server, not the perpetrator.
Best Answer
As your link points out,
ALTER TABLEcan break a transactionIn fact, according to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide
the following commands can break a transaction
ALTER TABLEBEGINCREATE INDEXDROP DATABASEDROP INDEXDROP TABLERENAME TABLETRUNCATE TABLELOCK TABLESUNLOCK TABLESSET AUTOCOMMIT = 1START TRANSACTIONThus, you cannot run any of these and have a stable mysqldump
SUGGESTION
You badly need to create a MySQL Replication setup
That way, the Master and stay up 24/7.
On the Slave, you can do the following:
STOP SLAVE;mysqldump --single-transactionSTART SLAVE;and never disturb the Master.
CAVEAT
Since setting up Replication requires a stable mysqldump, you may need downtime for the one-time setup. My answer would be a lot different if your MySQL was in Linux.