Столбец активного флага с одновременными обновлениями

Итак, я имею дело с проектом, в котором у нас есть объекты, имеющие имя, и хотя несколько объектов могут иметь одно и то же имя, только один из этих объектов может быть активным одновременно. Поэтому для каждого обновления/вставки, если новый/обновленный объект имеет

active = TRUE

, все остальные записи с таким именем должны иметь

active = FALSE

.

Моя проблема в том, что если две вставки происходят одновременно, то, похоже, бывают случаи, когда две записи заканчиваются

active = TRUE

с тем же именем, я думаю, потому что, когда база данных проверяется на наличие записей с этим именем, для которых установлено значение false, ни одна из строк не присутствует, и поэтому программа приходит к выводу, что обе записи могут иметь

active = TRUE

несмотря на то, что у него одно и то же имя.

Чтобы привести пример в псевдокоде:

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

когда я получаю новую пластинку с названием «имя», я делаю ВЫБЕРИТЕ идентификатор ИЗ объекта ГДЕ имя = 'имя' И активный = ИСТИНА; Я получаю идентификатор этого объекта, назовем его, затем обновляю эту запись.

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

Затем я вставляю новую запись

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

Насколько я могу судить, эта настройка не может работать, если я каким-то образом не заблокирую всю таблицу для каждой вставки/обновления, чтобы предотвратить одновременные обновления.

Альтернатива, о которой я думаю, — это отсутствие активного столбца, а наличие второй таблицы, например

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

где

active_record

это внешний ключ, идущий к таблице сущностей, и когда мне нужны активные записи, просто выполните соединение через него. Есть ли способ избежать этого?

Для справки: я использую mysql 8.0 , но мне интересно, как это будет работать с другими базами данных. 

Следующее предложение может работать здесь как альтернатива вашему нынешнему подходу. Вместо текущего дизайна таблицы используйте этот:

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

Предполагая, что последовательность автоматического увеличения всегда увеличивается, вы можете просто найти запись активного имени, используя:

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

В случае, если две вставки с одинаковым именем поступают почти одновременно, MySQL всегда гарантирует, что одной из них будет присвоен самый высокий (последний)

id

ценить. Обратите внимание, что существуют сценарии, в которых следующее сгенерированное значение автоматического приращения не обязательно должно быть больше предыдущего. Но если вы никогда не удаляете записи из

entity

стол, этого не должно произойти. 

Удалять

active

столбец из этой таблицы, но создайте еще одну таблицу, в которой будут храниться одна строка и один столбец с текущей активной строкой.

id

. Измените это сохраненное значение в триггере AFTER INSERT:

CREATE TABLE entity (     id INT(11),     name VARCHAR(50)     ); CREATE TABLE active_entity (     active_id INT(11)     ) SELECT 0 active_id;  CREATE TRIGGER store_active AFTER INSERT ON entity FOR EACH ROW UPDATE active_entity SET active_entity.active_id = CASE @active                                WHEN TRUE                                THEN NEW.id                                ELSE active_entity.active_id                                END;