Выполнение транзакций MySQL

263

26 ноября 2016, 20:18

Почему при выполнении транзакции с запросом с ошибкой коммит всё равно срабатывает для запросов, выполненных успешно? Например, при попытке вставки записей в таблицу, где есть всего два поля name и description:

START TRANSACTION;
INSERT INTO ibareport.test (`name`, `description`) VALUES ('1', '1');
INSERT INTO ibareport.test (`name`, `description`) VALUES ('2', '2', '2');
INSERT INTO ibareport.test (`name`, `description`) VALUES ('3', '3');
COMMIT;

я получаю следующей результат:

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

И можно ли (и если да, то как?) выполнить транзакцию, чтобы в случае ошибки все запросы откатывались?

Answer 1

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

mysql> begin;
Query OK, 0 rows affected (0,00 sec)
mysql> insert into testtrans (`name`, `description`) VALUES ('1', '1');
Query OK, 1 row affected (0,00 sec)
mysql> insert into testtrans (`name`, `description`) VALUES ('1', '1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into testtrans (`name`, `description`) VALUES ('2', '2');
Query OK, 1 row affected (0,00 sec)

В параллельном сеансе при этом

mysql> select * from testtrans;
Empty set (0,00 sec)

Т.е. мы всё-таки ещё в транзакции.

После commit в первом сеансе транзакция реально закоммитилась вместо более ожидаемого запрета на коммит и принудительного rollback. И затем данные появились и в другом сеансе.

Если mysql вернула ошибку на запрос - то это, оказывается, ваша обязанность увидеть ошибку и сказать rollback вместо commit. Сама субд вам позволит закоммитить эту транзакцию. Странный mysql.

Answer 2

Попробуй использовать следующий вид инсерта

INSERT INTO ibareport.test (`name`, `description`) VALUES ('1', '1'), ('1', '1'), ('1', '1');

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

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

DECLARE @ok as int = 0
create table #pre_data1 (id int, n int)
create table #pre_data2 (id int, n int)
create table #data1 (id int, n int)
create table #data2 (id int, n int)

BEGIN TRY
 insert into #pre_data1 (id,n) values (1,1),(2,2)
 insert into #pre_data1 (id,n) values (4,4),(5,5,5)
END TRY
BEGIN CATCH
 set @ok = 1
END CATCH
IF @ok = 0 BEGIN
 insert into #data1
 select * from #pre_data1
 insert into #data2
 select * from #pre_data2
END

Т.е. мы пробуем засунуть данные в таблицы #pre_dataX, и если нам удается это сделать, а значит ошибок не было, то может перегонять эти данные в основные таблички #dataX. В противном же случае, в основные таблички ничего инсертиться не будет, а данные останутся во временных таблицах