Будут ли транзакции ждать друг друга или работать параллельно, если мы говорим о таблице с уникальным индексом в InnoDB?

У меня одна таблица в движке InnoDB выглядит так:

CREATE TABLE `sample` (
   `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `unique_str` varchar(255) NOT NULL
);
ALTER TABLE `sample`
   ADD UNIQUE `unique_str_index` (`unique_str`);

Как видите, эта таблица имеет уникальный индекс в столбце "unique_str".

Например, я хочу запустить два скрипта с транзакциями один за другим.

//First script, i use pdo for connection
$pdo->beginTransaction();
$pdo->exec("INSERT INTO sample (unique_str) VALUES('first')");
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
sleep(50); //I'll start second script here
$pdo->commit();

Я запущу второй скрипт, когда первый скрипт спит в течение 50 секунд.

//Second script
$pdo->beginTransaction();
$pdo->exec("INSERT INTO sample (unique_str) VALUES('third')");
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')"); 
$pdo->commit();

Во втором скрипте PHP застрянет на самой первой строке с командой «insert» и будет ждать, пока транзакция в первом скрипте будет зафиксирована. Когда первый скрипт выйдет из спящего режима и зафиксирует транзакцию, в таблице создадутся две новые строки («first» и «violate_str») и второй скрипт снова начнет работать: второй скрипт проделает две вставки, но при второй вставке ('violate_str') будет сгенерировано исключение.

Мой вопрос: "почему PHP ждет завершения первой транзакции и не работает на этот раз? MySQL блокирует вторую транзакцию? Почему?". Я думаю, что у меня есть некоторое непонимание принципов работы транзакций. Я думал, что транзакции будут создаваться одна за другой и вторая транзакция не будет заблокирована.


mysql php transactions innodb unique-index
person pinkychyaz    schedule 04.05.2019    source источник
comment
Вы уверены, что MySQL создал таблицу как innoDB, а не как MyISAM? я советую вам добавить Engine = InnoDB к оператору CREATE TABLE   -  person Raymond Nijland    schedule 04.05.2019
comment
почему PHP ждет завершения первой транзакции и на этот раз не работает? Блокирует ли MySQL вторую транзакцию? Почему? так работает механизм InnoDB, предполагая, что таблица действительно работает под управлением механизма InnoDB InnoDB проверяет ограничения UNIQUE и FOREIGN KEY строка за строкой. При выполнении проверок внешнего ключа InnoDB устанавливает общие блокировки на уровне строк для дочерних или родительских записей, которые он должен просматривать. InnoDB немедленно проверяет ограничения внешнего ключа; проверка не откладывается до фиксации транзакции источник   -  person Raymond Nijland    schedule 04.05.2019
comment
Да, таблица работает под InnoDB, я проверял. Вот вывод команды SHOW TABLE STATUS WHERE Name = 'sample' sample InnoDB 10 Dynamic 1 16384 16384 0 0 0 2 2019-05-04 15:15:21 2019-05-04 19:14:01 NULL utf8_general_ci NULL 0 N   -  person pinkychyaz    schedule 04.05.2019
comment
innoDB более или менее блокирует полный уникальный ключ, чтобы убедиться, что записи по-прежнему уникальны между транзакциями, что на самом деле не разрешено стандартами ANSI/ISO SQL при использовании транзакций. Согласно стандарту SQL, по умолчанию следует отложить проверку. То есть ограничения проверяются только после обработки всего оператора SQL. Пока InnoDB не реализует отложенную проверку ограничений,   -  person Raymond Nijland    schedule 04.05.2019

Ответы (1)


arrow_upward
1
arrow_downward

Ваш второй скрипт ожидает второй вставки, а не первой вставки.

Если я проверю ваш код с помощью операторов эха:

//First script, i use pdo for connection
$pdo->beginTransaction();
echo "begin done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('first')");
echo "insert('first') done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
echo "insert('violate_str') done\n";
sleep(50); //I'll start second script here
echo "sleep done\n";
$pdo->commit();
echo "commit done\n";

//Second script
$pdo->beginTransaction();
echo "begin done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('third')");
echo "insert('third') done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
echo "insert('violate_str') done\n";
$pdo->commit();
echo "commit done\n";

Я вижу вывод из первого скрипта:

begin done
insert('first') done
insert('violate_str') done

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

begin done
insert('third') done

Он ждет в этот момент. Итак, мы знаем, что он выполнил одну вставку без ожидания. Ожидается вторая вставка, конфликтующая со вставкой 'violate_str'. Это потому, что первый сценарий уже создал эту запись в уникальном индексе, хотя и не зафиксировал ее. Он по-прежнему удерживает блокировку этого значения в индексе, поэтому второму сценарию приходится ждать этой блокировки.

Как только сон заканчивается, первый скрипт фиксируется и выводит:

sleep done
commit done

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

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 
1062 Duplicate entry 'violate_str' for key 'unique_str'

Если бы первый скрипт делал rollback() вместо commit(), то он освобождал бы свою блокировку и отменял вставку. Это позволяет второму скрипту завершить работу и свободно вставить спорное значение.

person Bill Karwin    schedule 05.05.2019