Блог

Для этого вы можете использовать хранимую процедуру:

Я никогда не сталкивался с такого рода проблемами, и если я когда-нибудь столкнусь, я бы поступил следующим образом:

 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_when_duplicate`(val1 int, val2 int, val3 int)
BEGIN

     -- catch duplicate insert error
     DECLARE EXIT HANDLER FOR 1062
     BEGIN
        -- we could recursively try to insert the same val1 and val2 but increasing val3 by 1
        call sp_insert_when_duplicate(val1,val2,val3 1);
     END;

     -- by default mysql recursive limit is 0, you could set as 10 or 100 as per your wish
    SET max_sp_recursion_depth=10;

     -- [Trying] to insert the values, if no duplicate this should continue and end the script.. if duplicate, above handler should catch and try to insert again with 1  value for val3
    INSERT INTO `table` (`column_a`, `column_b`, `column_c`) values (val1,val2,val3);


END
 

использование будет:

 call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
select * from `table`;
 

Результат:

  ---------- ---------- ---------- 
| column_a | column_b | column_c |
 ---------- ---------- ---------- 
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
 ---------- ---------- ---------- 
 

то же самое относится и к транзакции:

 start transaction;
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
commit;

select * from `table`;


 ---------- ---------- ---------- 
| column_a | column_b | column_c |
 ---------- ---------- ---------- 
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
 ---------- ---------- ---------- 
 

однако я не пробовал параллельные транзакции!

 BEGIN;
SELECT @c := MAX(c)   1
    FROM t
    WHERE a = ? AND b = ?
    FOR UPDATE;           -- important
if row found              -- in application code (or Stored Proc)
then
    INSERT INTO t (a,b,c)
        VALUES
        (?, ?, @c);
else
    INSERT INTO t (a,b,c)
        VALUES
        (?, ?, 1);
COMMIT;
 

Надежда состоит в том, что программа FOR UPDATE остановится до тех пор, пока не получит блокировку и желаемое c значение. Затем остальная часть транзакции должна пройти гладко.

Я не думаю, что обстановка transaction_isolation имеет значение, но это стоит изучить.

Давайте назовем таблицу, содержащую эти 3 столбца, ThreeColumnTable чтобы избежать путаницы, возникающей из-за имени, которое вы дали — table .

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

Шаги решения:

1. Создайте таблицу, в которой хранится последнее значение, используемое для column_c . Давайте назовем эту таблицу LastUsedIdTable . LastUsedIdTable будет содержать только три столбца:
   • Имя таблицы, столбец которой вы хотите увеличить вручную (пример: ThreeColumnTable );
   • Имя самого столбца (пример: column_c );
   • Последнее значение, используемое для этого столбца (пример: 121 ).
2. Теперь, для удобства использования, напишите хранимую процедуру, которая выполняет транзакцию LastUsedIdTable . Этот процесс будет считывать последнее значение, используемое для column_c в вашем случае. Увеличьте его. Верните вам увеличенное значение. (Конечно, вы также можете выполнять прямой запрос каждый раз. Хранимая процедура — лучший выбор.)
3. Теперь возвращаемое значение column_c для вас заморожено, потому что хранимая процедура навсегда увеличила значение LastUsedIdTable для ThreeColumnTable строки. Любой, кто хочет добавить еще одну строку в ThreeColumnTable , вызовет сохраненную процедуру и получит неконфликтное и увеличенное значение, даже если вы еще не закончили вставку своего предыдущего значения в свой table .

Демонстрация работоспособности решения:

Чтобы обобщить демонстрацию, учтите, что у вас есть n одновременных запросов на вставку ThreeColumnTable .

Все n запросов должны будут сначала вызвать хранимую процедуру. Поскольку сохраненный процесс использует транзакцию LastUsedIdTable , одновременно только 1 запрос будет обращаться к строке for ThreeColumnTable , которая в настоящее время выглядит как:

  ----------------------------------- 
| ThreeColumnTable | column_c | 121 |
 ----------------------------------- 
 

Теперь первый запрос заблокирует эту строку и получит 122 в качестве значения, а также обновит значение в строке таблицы до 122. К тому времени, когда n запросов будут завершены, строка LastUsedIdTable ‘s ThreeColumnTable будет выглядеть так:

  ----------------------------------------- 
| ThreeColumnTable | column_c | (121   n) |
 ----------------------------------------- 
 

Теперь эти n запросов уже будут на пути к выполнению вставки ThreeColumnTable . Но поскольку все они имеют свои собственные уникальные значения column_c , их вставка будет бесконфликтной, независимо от порядка, в котором происходит вставка! Возможно, значение 121 n было вставлено первым, а 122 вставлено последним. На самом деле, вам даже не нужно column_a, column_b, column_c , чтобы кортеж был уникальным, потому column_c что он всегда будет уникальным.

Это решение отлично работает как для параллельных запросов, так и для последовательных. При очень незначительном (практически незначительном) снижении производительности это было бы преимуществом безопасности транзакций, уровня изоляции и производительности, которые вы пытаетесь достичь.

Важно: Используйте LastUsedId для хранения последнего значения всех таких столбцов первичного ключа во всех таблицах вашей базы данных. Поскольку эта таблица будет содержать только столько строк, сколько ключей во всей вашей базе данных, которые вы хотите увеличить вручную, она будет содержать только ограниченное и фиксированное количество строк. Это будет быстро и без гоночных условий.

Вы можете использовать фиктивное значение для column_c , чтобы заблокировать комбинацию (column_a, column_b) для других вставок, что особенно гарантирует, что она будет заблокирована, даже если строка для этой комбинации еще не существует.

 start transaction;

set @a = 1;
set @b = 1;

insert into `table` (column_a, column_b, column_c)
values (@a,@b,0)
on duplicate key update column_c = 0; -- , column_d = null, ...

select max(column_c)   1 into @c
from `table` where column_a = @a and column_b = @b;

update `table` set column_c = @c
where column_a = @a and column_b = @b and column_c = 0;

select @c;

commit;
 

Первый insert блокирует точную комбинацию ( column_a, column_b) , но не блокирует другие значения, поэтому вы можете вставлять другие комбинации во время выполнения первой транзакции.

Он будет работать с любым уровнем транзакции, потому что select max() будет корректным (и правильно блокирует разрыв), даже если другой сеанс обновит строки той же комбинации (кроме заблокированной строки с column_c = 0 ) после первой вставки; но если вы используете READ UNCOMMITTED или SERIALIZABLE , временное значение (с column_c = 0 ), конечно, будет вскоре виднодля других сеансов. В случае, если это вас беспокоит, используйте более высокий уровень (например, оставьте значение по умолчанию).

@c будет, по мере необходимости, содержать ваш последний идентификатор @a и @b будет заменен вашими значениями и не обязательно должен быть переменным.

Если вы помещаете этот код в хранимую процедуру, имейте в виду, что MySQL не поддерживает вложенные транзакции, поэтому всегда рискованно запускать (и особенно фиксировать) транзакцию в процедуре, поэтому вы должны выполнять обработку транзакций вне процедуры.

on duplicate key существует только для того, чтобы сделать его надежным для дураков. Если все работает нормально, вам это не нужно, но это гарантирует, что код будет работать, даже если кто-то (вручную) добавил недопустимую строку с column_c = 0 и оставил ее там. Или, если, в случае, если вы помещаете код в процедуру, кто-то вызвал его, не запустив сначала транзакцию, а другой сеанс одновременно вставляет эту комбинацию, это может привести к ошибке дублирования ключа (для update ) и, следовательно, может привести к остаточной строке column_c = 0 (с которой вы, конечно, могли быудалить в обработчике исключений в вашей процедуре). Возможно, было бы интересно получить информацию (от плачущих пользователей), если такая ситуация возникла, поэтому вы можете захотеть удалить on duplicate key (по крайней мере, для тестирования).

Вариант 1

Это должно быть атомарным и, похоже, вставляет правильные значения:

 INSERT 
  INTO table_name (column_a, column_b, column_c)
SELECT 
    :column_a,
    :column_b,
    COALESCE((
       SELECT MAX(column_c) 
         FROM table_name
        WHERE column_a = :column_a
          AND column_b = :column_b          
    ),0)   1;
 

Где :column_a и :column_b ваши новые значения.

К сожалению, если вы хотите использовать LAST_INSERT_ID() функцию, она будет работать только со AUTO_INCREMENT значениями.

Вы могли бы добавить суррогатный первичный ключ:

 CREATE TABLE `table_name` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT
     `column_a` int(11) unsigned NOT NULL,
     `column_b` int(11) unsigned NOT NULL,
     `column_c` int(11) unsigned NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE INDEX (`column_a`, `column_b`, `column_c`)
) ENGINE=InnoDB;
 

И запустите тот же INSERT запрос, что и выше. Теперь вы LAST_INSERT_ID() будете ссылаться на недавно вставленную строку.

Если вы добавите суррогатный ключ, возможно, стоит пересмотреть, нужен ли column_c он по-прежнему.

Вариант 2

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

 INSERT 
  INTO table_name (column_a, column_b, column_c)
SELECT 
    :column_a,
    :column_b,
    @c := COALESCE((
       SELECT MAX(column_c) 
         FROM table_name
        WHERE column_a = :column_a
          AND column_b = :column_b          
    ),0)   1;

SELECT @c;
 

Вариант 3

Если это единственное место, где вы вставляете или обновляете эти столбцы в своей таблице, вы можете выполнить некоторую ручную блокировку на основе имен. Имитация блокировок записей в GET_LOCK() рамках одной транзакции.

Запустите транзакцию.

Выберите конкретное имя для строк, которые вы хотите заблокировать. например 'insert_table_name_aaa_bbb' . Где 'aaa' — значение для column_a и 'bbb' — значение для column_b.

Вызов SELECT GET_LOCK('insert_table_name_aaa_bbb',30) для блокировки имени 'insert_table_name_aaa_bbb' .. он вернет 1 и установит блокировку, если имя станет доступным, или вернет 0, если блокировка недоступна через 30 секунд (второй параметр — это время ожидания).

Выполняйте свои SELECT INSERT запросы и здесь.

Используйте DO RELEASE_LOCK('insert_table_name_aaa_bbb') , когда закончите.

Зафиксируйте транзакцию.

Имейте в виду; GET_LOCK() повторный вызов в транзакции снимет ранее установленную блокировку. Кроме того, эта именованная блокировка будет применяться только к этому сценарию или где используется точное имя. Блокировка применяется только к имени!

GET_LOCK() Документы

Если для вас важна целостность данных, то рассмотрите следующее:

 DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id SERIAL PRIMARY KEY
,m CHAR(1) NOT NULL
,n CHAR(1) NOT NULL
) ENGINE=InnoDB;

INSERT INTO my_table (m,n) VALUES 
('a','b'),
('a','b'),
('a','c'),
('a','b'),
('j','p'),
('j','b'),
('j','p'),
('a','c');

SELECT x.*
     , COUNT(*) i
  FROM my_table x
  JOIN my_table y
    ON y.m = x.m
   AND y.n = x.n
   AND y.id <= x.id
 GROUP 
    BY x.id
 ORDER
    BY m,n,i;

 ---- --- --- --- 
| id | m | n | i |
 ---- --- --- --- 
|  1 | a | b | 1 |
|  2 | a | b | 2 |
|  4 | a | b | 3 |
|  3 | a | c | 1 |
|  8 | a | c | 2 |
|  6 | j | b | 1 |
|  5 | j | p | 1 |
|  7 | j | p | 2 |
 ---- --- --- --- 
 

Этот дизайн не предполагает никаких удалений — или удалений только при очень специфических обстоятельствах

OTOH, если целостность данных не имеет значения, тогда подумайте, подходит ли реляционная база данных для ваших нужд.