Как увеличивать счетчик в условиях конкуренции?

Question

[ragnar_ok]

MySQL InnoDB. Дана таблица со счетчиком.

create table foo
(
    entity_id        int unsigned not null
        primary key,
    count int unsigned not null
)

Сначала с помощью select ... for update проверяется существование записи по primary, и, если записи нет, то происходит insert, иначе update с увеличением счетчика на "1". В случае, если записи нет, при "одновременных" запросах закономерно получаю ошибку дубликата (уровень изоляции read committed).

session1> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

session1> start transaction;
Query OK, 0 rows affected (0.00 sec)

session2> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

session2> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1> select * from foo where entity_id = 8 for update;
Empty set (0.00 sec)

session2> select * from foo where entity_id = 8 for update;
Empty set (0.00 sec)

session1> insert into foo (entity_id, count) values (8, 1);
Query OK, 1 row affected (0.00 sec)

session1> commit;
Query OK, 0 rows affected (0.01 sec)

session2> insert into foo (entity_id, count) values (8, 1);
ERROR 1062 (23000): Duplicate entry '8' for key 'foo.PRIMARY'

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

insert into foo (entity_id, count)
values (8, 1)
on duplicate key update count = count + 1;

Если так, то выходит, что `select ... for update` для решения задачи не нужен в обоих случаях (когда записи нет и когда запись есть)? Оптимальнее сразу выполнять запрос ниже без предварительного `select ... for update` в явно открытой транзакции?

Comments

[Adamos]

Ну да, запрос атомарный и никаким конкуренциям не подверженный. Стандартное решение подобных задач.
Это предварительные селекты - велосипеды от невежества.

[Руслан .]

У insert есть опция игноривать duplicate key.
Можно вот так попробовать:
Вначале

INSERT  IGNORE INTO your_table VALUES (1);
SELECT ROW_COUNT();

И если количество строк вернулось 0,
тогда запускать второй запрос с update

Answer 1

[YepBro]

Да, лучше использовать конструкцию

insert into foo (entity_id, count)
values (8, 1)
on duplicate key update count = count + 1;

Comments

[YepBro]

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

О какой ошибке идет речь?

[ragnar_ok]

INSERT INTO ... VALUES (...): устанавливает эксклюзивную блокировку на вставленную строку. Обратите внимание, что эта блокировка не является блокировкой следующего ключа и не предотвращает вставку другими пользователями записей в интервал перед вставленной строкой. Если произойдет ошибка дублирующегося ключа, оператор устанавливает блокировку совместного доступа на запись дублирующегося индекса.

Задал вопрос после прочтения этого: "оператор устанавливает блокировку совместного доступа на запись дублирующегося индекса". Сможете раскрыть в каком случае устанавливается shared, а не exclusive в этом примере?

www.mysql.ru/docs/man/InnoDB_Locks_set.html

[Adamos]

ragnar_ok, у вас чистая правка конкретной строки или создание новой. А это предупреждение - про то, что в это время происходит с AUTOINCREMENT полями, например. С ними таким образом работать не стоит.