MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

configurationinnodbMySQLperformance

I want to configure a MySQL server for the following scenario.

A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a specific time when around 200 users will try to access the page. In fact they are probably refreshing the page like crazy a few minutes before that.

There will be about 150 timeslots (rows) available that can be selected by the users. A web application will handle a transaction for each request trying to LOCK IN SHARE MODE a row ("timeslot") to check if it is still available and set it to a "booked" state if it is available. My database is using the InnoDB engine.

The the scenario is a lot of users doing a vast amount of SEELCTs and subsequently within a few seconds a vast amount of UPDATEs. Thinking of a basic Ubuntu MySQL server, what configuration options would you suggest to take a look at to make this "event" happen as smooth as possible. Things I want to avoid for example is the server to be overly durable writing changes to the hard drive during this period.

— Edit

Just for information: The accepted answer below shows how this can be achieved without any LOCKING and explains very well how the MySQL will behave when trying the LOCKING approach.

However we managed to go through the scenario by changing the commit behavior of MySQL to only write to disk once every second and allowed 2GB of memory usage. We also had 8 thin instances running a rails application below a 4 worker nginx (basically default setup with SSL). We ran it on a desktop i5 system with 12GB of memory (which was much too much) and the server hardly reached a load of 1 (out of 4). So, if you are running a similar scenario: don't panic.

Best Answer

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.

Related Question