如何避免MySQL InnoDB中的竞争条件？

We're using MySQL InnoDB. The system support multiple request that can happen simultaneously. Here is a simplified example of what we're trying to achieve. Let's assume we have a table that holds entities that represent light bulb. These light bulbs can be turned on (activated) by users. Users can add light bulbs to the table, which initially are deactivated, and only 10 of them can be marked as activated. So we have a limit of 10. Since the system supports concurrency, there is a risk of race condition based on the following example:

SESSION 1:

1: Get the number of active bulb

2: Check the number of activated bulbs

3: If limit not reached yet, activate the bulb and commit.

4: Otherwise rollback.

The issue here is that during step 2 and 4 another session could enter and could end up exceeding the limit.

This is a sql representation of what we are doing currently:

1. SELECT count(id) AS total FROM tbl WHERE is_active = 1;
2. if(total < 10) {
3. activate another bulb; } else {
4. rollback; }

What do you recommend as a solution for this scenario? We were considering "select for update" but that, if I'm not mistaken, locks selected rows and users can add bulbs to the table while selected rows are under lock.

Thank you.

One idea is to keep a separate locking table where you manage this limit requirement and make only atomic updates to the count of active bulbs like:

update active_bulb_count
    set active_count = active_count + 1
    where active_count < limit
      and <any other conditions needed>;

If no rows updated you know the limit has been met, so you wouldn't set the bulb active.

You should probably still consider your transaction setup for the session that performs this action. See more details on atomic update and transactions MySQL Atomic UPDATE in InnoDB vs MyISAM

BEGIN;   -- start a "transaction"
do the 4 steps
COMMIT;  -- or ROLLBACK

Note: Any SELECTs that may lead to an UPDATE need to say FOR UPDATE.

And/or, look into how to do more work in an UPDATE (see churd's answer). Or INSERT ... ON DUPLICATE KEY UPDATE ...

Keep in mind that INSERT and UPDATE can report how many rows were affected. This can be used to "assume the task will succeed", then check if it did and take evasive action if it failed.