Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Interesting that the deadlock does not lead to both transactions being canceled which I would have assumed when seeing a "deadlock" error. –  Nicolas Oct 23 '12 at 11:39
    
I can see why that might seem like a natural conclusion, but the two transactions are on two different connections and are probably unaware of each other's existence (from the application's perspective). Once one of them rolls back, there's no reason to stop the other one from proceeding. –  Michael - sqlbot Nov 25 '12 at 23:23
add comment

I would recommend first, get a tool or make one yourself to put mysql under similar load, as there's no magic recipe for mysql and you should be able to test each different optimization and be able to decide if it was good for you or not.

I assume you already do your basic innodb tunning (bufferpool and logs size).

My first recommendation would be to move the mysql server out of the web server where application will reside. Likely your bottleneck will be on your web application concurrency than in mysql.

For the amounts of available rows I don't think you will put so much stress on MySQL, if you want to tune it (measure first!), only if your mysql it's not on the same sever as webapp (no high risk of crashing) you can get better performance by making innodb flush changes to disk only once a second and all changes during that second should happen only inside innodb memory.

As always you should make sure that all the working dataset of those users fits in memory (that's also a very good reason to move your mysql out of a server where other memory hungry applications run.

Good luck!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.