Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Active flag column with concurrent updates

So I am dealing with a project where we have entities that have a name, and while multiple entities can have the same name only one of those entities can be active at a time. Therefore for every update/insert if the new/updated entity has active = TRUE, all other entries with that name have to have active = FALSE.

My problem is that if two inserts are happening concurrently, there seem to be cases where two entries end up active = TRUE with the same name, I guess because when the database is checked for any entries with that name to set to false, neither of the rows are present, and so the program concludes that both entries are allowed to have active = TRUE despite having the same name.

To give an example in pseudo code:

CREATE TABLE entity (
id INT(11),
name VARCHAR(50),
active bit(1)
);

when I get a new record with the name ‘name’, I do
SELECT id FROM entity WHERE name = ‘name’ AND active = TRUE;
I get the id for that object, let’s call it , then for that record I update

UPDATE entity SET active=FALSE WHERE id=<IDNUM>

Then I insert the new record

INSERT INTO entity (id,active) VALUES ('name',TRUE)

As far as I can tell, this setup cannot work unless I somehow lock the entire table for each insert/update to prevent concurrent updates.

The alternative I am thinking of is not having an active column, but having a second table like

CREATE TABLE active_entity (
name VARCHAR(50),
active_record INT(11)
);

where active_record is a foreign key going to the entity table and when I need active records, just do a join through that. Is there any way to avoid this?

For reference, I am using mysql 8.0, but I am curious how this would work with other databases

Tags:

2

Answers


  1. 0

    The following suggestion might work here as an alternative to your current approach. Instead of your current table design, use this one:

    CREATE TABLE entity (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    -- other columns here
);

    Assuming the auto increment sequence be always increasing, you can simply find the active name record by using:

    SELECT *
FROM entity
WHERE name = 'blah'
ORDER BY id DESC
LIMIT 1;

    In the event that two inserts with the same name come in at almost the same time, MySQL will always guarantee that one of them gets assigned the highest (latest) id value. Note that there are scenarios in which the next generated auto increment value need not be greater than the previous one. But if you never delete records from the entity table, this should not happen.

    Login or Signup to reply.
  2. 0

    Presumably you have autocommit enabled (it is the default). This means there is a gap between the time your update is committed and your insert is performed where someone else can slip in with another insert.

    There are two possible solutions that involve obtaining a record lock on the row you are updating.

    1. Turn off autocommit on this session, and use explicit Start Transaction and Commit statements around your update and insert statements. Now the Update will set a record lock on the prior active record and any second person will wait until you have committed both changes before their SQL will run.
    2. Perform your Update within the database as a Before Insert trigger. Once again, your updated row will be locked until your new one can be inserted. This solution is also performed much more quickly, because it all happens within the server and does not have to turn around a separate client request. This means your row lock is in place for a shorter time.

    You should also not do the initial Select to obtain the current key. Simply Update entity Set active = FALSE Where name='name' and active = TRUE

    Note that this suggestion still can fail upon initial entry of a new name because the lockout is based upon a pre-existing record. This may be something you can live with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search