Different transactions must guarantedly select different items; avoid contentions

Question

As part of registering a new user; we assign them a resource (a Solr core, in this case) from a precompiled list (a table).

If 5 users sign up, they must be assigned 5 different cores; if the user successfully registers, the assignment is made final (see my depiction below).

But in real world, concurrently registering new users contend for the same row, not choose different rows. If X needs 5 seconds to register, Y and Z's registrations which are in X's "duration" will fail as they contend for the same row by X.

Question: How to make transactions choose without contentions, even under high concurrency such as 100 signups a second?

table: User
user_id   name  core   
      1    Amy h1-c1
      2    Anu h1-c1
      3    Raj h1-c1
      4    Ron h1-c2
      5    Jon h1-c2

table: FreeCoreSlots
core_id  core status   
      1 h1-c1   used
      2 h1-c1   used
      3 h1-c1   used
      4 h1-c2   used
      5 h1-c2   used #these went to above users already
      6 h1-c2   free
      7 h1-c2   free
      8 h1-c2   free
      9 h1-c2   free

Pseudo-code if stuff was isolated:

sql = SQLTransaction()
core_details = sql.get("select * from FreeCoreSlots limit 1")
sql.execute("update FreeCoreSlots set status = 'used' where id = {id}".format(
   id = core_details["id"]))
sql.execute("insert into users (name,core) values ({name},{core})".format(
   name = name,
   id   = core_details["id"]))
sql.commit()

If 100 signups happen a second, they would contend for the first row in FreeCoreSlots and cause serious fails.

There is a select... for update as in InnoDB SELECT ... FOR UPDATE statement locking all rows in a table as the solution, but they seem to suggest lowering the isolation. Is this method the right way?

I think this example does what you want using a stored procedure: http://stackoverflow.com/a/562744/1584772. At least, it is a good place to start (the key is making this all happen in one transaction). — dan1111, Oct 01 '12 at 12:13

podiluska · Answer 1 · 2012-10-01T12:12:51.917

5

By setting the transaction isolation level to serializable.

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Effectively, this blocks other transactions from altering the updated table while the transcation processes, ensuring that your data is updated in atomic, consistent fashion.

edited Oct 01 '12 at 12:12

answered Oct 01 '12 at 12:07

podiluska

50,950
7
98
104

please explain this too. Im refering this now but I dont get the gist. – Jesvin Jose Oct 01 '12 at 12:09

hol · Accepted Answer · 2012-10-06T11:00:46.670

The question I would ask is why it could take 5 seconds for a user to complete. Between the START TRANSACTION and the COMMIT should be only fractions of a second.

To prevent that you assign the same row in FreeCoreSlots to the same use another time you must use SELECT for UPDATE. The lock level is not really the problem in my opinion. The way you designed the database the next free row in FreeCoreSlots is actually locked until the transaction is done. See my test results below. And I do think that even for 100 new users per second it should still be sufficient. But if you even want to overcome this you must find a way to lock another next free row in FreeCoreSlots to every user. Unfortunately there is no function of "Select first row unless it has a lock". Maybe instead use some random or modulus logic. But as I said already I think that should not be your problem even for the unthinkable amount of 100 new users per second. If I err with this feel free to leave a comment and I am willing to have another look at it.

Here is my test result. Default InnoDB lock level: repeatable read without FOR UPDATE. This way it does NOT work.

User 1:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 -- returns id 1
User 2:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 -- also returns id 1 !!!
User 1:
    UPDATE FreeCoreSlots SET status = 'USED' where ID = 1;
User 2:
    UPDATE FreeCoreSlots SET status = 'USED' where ID = 1; -- WAITS !!!
User 1:
    INSERT INTO user VALUES (...
    COMMIT;
USER 2:
    wait ends and updates also ID = 1 which is WRONG

Lock level repeatable read but with FOR UPDATE. This way it does WORK.

User 1:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 FOR UPDATE -- returns id 1
User 2:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 FOR UPDATE -- WAITS
User 1:
    UPDATE FreeCoreSlots SET status = 'USED' where ID = 1;
User 2:
    still waits
User 1:
    INSERT INTO user VALUES (...
    COMMIT;
USER 2:
    Gets back Id 2 from the select as the next free Id

5 seconds was a 10x exaggeration anyway :-) I however got it to work in spite of waits by setting a read uncommitted isolation and `select a (select a free row as subquery) for update` which is kinda above my head. But your method is simpler to "do" in SQLAlchemy ORM. — Jesvin Jose, Oct 07 '12 at 17:59

score 0 · Answer 3 · answered Oct 07 '12 at 14:50

Defer the assignment until the last possible moment. Don't try to bind it up during the registration process.

Leverage the status column as a concurrency tactic. Apply a predicate that ensures that you will only update the row if it is still 'free'. With the default isolation level, the read will be non-blocking. Your update will either update 1 row or none. You can loop if not successful, and place a limit on the number of tries (in case the free slots are exhausted). You could also pre-fetch more than 1 free id, to save on round trips on your attempts. Lastly, this would be better as a stored procedure that returns the id of the newly-allocated slot. In that case, you employ a similar tactic in your stored procedure, where you fetch/update until your update is successful. In high-contention environments, I have selected a random row from the top N free ones, if serial assignment is not very important.

sql = SQLTransaction()

core_details = sql.get("select * from FreeCoreSlots where status = 'free' limit 1")
sql.execute("update FreeCoreSlots set status = 'used' where id = {id} and status = 'free'".format(
   id = core_details["id"]))

//CHECK ROW COUNT HERE (I don't know what language or library you are using.
//Perhaps sql.execute returns the number of rows affected).
//REPEAT IF ROW COUNT < 1

sql.execute("insert into users (name,core) values ({name},{core})".format(
   name = name,
   id   = core_details["id"]))
sql.commit()

Different transactions must guarantedly select different items; avoid contentions

3 Answers3

Linked