[MySQL] Транзакции - не понимаю :(

mysql> ROLLBACK;

Дык, с ручным ROLLBACK - кто бы спорил. Но приведенный пример лишь небольшое упрощение.

На самом деле, есть процедурка. Ну навроде:

CREATE PROCEDURE proc()
BEGIN
    START TRANSACTION;
    INSERT INTO A SET v = 1; -- Как-то модифицируем A

    ... Тут что-то делаем, что в конечном итоге дает ошибку ...

    COMMIT;
END

Так вот, по крайней мере в таком (наивном наверное?) виде изменения в A автоматом не откатываются. Хотя очень хочется добиться отката.

PS: Наверное, с процедуркой пример нужно было сразу привести :)

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

Итого, если при выходе из транзакции у тебя таблицы в несогласованном состоянии, то будет откат.

Тут у тебя всё ок с согласованностью, так что за ошибками надо следить самому, или из-вне или в сохранённой процедуре.

Ммм... Нет, ну хорошо. Можно конечно и 'руками'. Например:

CREATE PROCEDURE proc()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' ROLLBACK;
    START TRANSACTION;
    INSERT INTO A SET v = 1; -- Как-то модифицируем A

    ... Тут что-то делаем, что в конечном итоге дает ошибку ...

    COMMIT;
END 

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

if @@error_count>1 then rollback; может помочь.

или продумываение логики процедуры, может помочь.

P.S. может есть и более элегантные решения, но я их не знаю

> Собственно проблема в том, что по крайней мере в указанном выше примере ничего не откатывается. Т.е. после возникновения ошибки вместо пустой таблички имеем v = {1, 2, 3}.

Насколько я понимю, в Postgres'е именно такое поведение. Если же тебе актуален именно MySQL - придется обрабатывать ошибки.

а я наврал стандартное поведение для InnoDB таблиц:

A duplicate-key error rolls back the SQL statement, if you have not specified the IGNORE option in your statement.

для MYIsam транзакции не поддерживаются т.е.

In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode.

Ну в сущности через обработчики ошибок и ручной rollback все и работает. Чуть более обще вопрос описан тут:

http://forums.mysql.com/read.php?97,144933,144933

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

> Вопрос: Что я делаю не так :-?

ВСЁ!!! Транзакция есть последовательность стейтментов, каждый из которых может как выполнится, так и не выполниться. Если не выполнился один стейтмент, то не выполнился именно он. Тразакция это кастрюля, стетменты это фрукты. Ты хочешь положить в кастрюлю 5 яблок, 1 арбуз и еще 5 яблок.

«Положить фрукт в кастрюлю» - это стейтмент. Пролезло фрукт в кастрюлю или нет - это результат выполнения стетмента. Высыпал насыпанные в кастрюлю фрукты на землю - сделал ROLLBACK Поставил кастрюлю на плиту - сделал COMMIT Положил яблоко. Второе. Третье. Четвертое. Пятое (выполнившиеся стейтменты) Попытался засунуть арбуз - не пролез (size constraint violated) Положил в кастрюлю шестое яблоко. Седьмое. Восьмое. Девятое. Десятое. Сделал COMMIT (поставил кастрюлю на плиту). Что содержится в кастрюле? Десять яблок - результат десяти успешных стетментов.

В штатном режиме, констрейнты проверяются во время выполнения стейтмента, а не во время коммита! Проверка констрейнтов во время коммита чревата большими проблемами.

Пример: В магазине распродажа - есть несколько бесплатных арбузов и остальные платные. Задача - вынести пять бесплатных арбузов - пять стейтментов «вынести арбуз» с констрейнтом «бесплатный».

Вариант первый ты берешь по одному арбузу, и выносишь их. Если на входе пищит детектор, то это не бесплатный арбуз, ты его возвращаешь на место и берешь следующий - констрейнт «бесплатность» проверяется для каждого стейтмента «вынести арбуз».

Вариант второй - ты складываешь пять как ты думаешь бесплатных арбузов в мешок (проверка констрейнта отложена до COMMITа, а COMMIT происходит в момент прохода через детектор). Идешь через детектор, а он ПИЩИТ - констрейнт «бесплатный арбуз» нарушен! Какой из арбузов нарушает констрейнт? Ты распаковываешь мешок, вываливаешь арбузы (делаешь ROLLBACK) и начинаешь проносить их через детектор по одному за раз, то есть делаешь стейтмент и сразу после него COMMIT чтобы проверитть а не нарушил ли ты констрейнт, то есть вернулся к предыдущему варианту.

однако сама база можно блокировать ВСЕ стетменты (кроме rollback),
чтобы не получился частичный коммит. Таким образом, если возникал
ошибка добавления арбуза, ты не только не сможешь поставить кастрюлю на
плиту, но и добавить 6-10-е яблоки.

для Postgres

create table test(id int, val int not null);

CREATE TABLE
blogdb=> begin;
BEGIN
blogdb=> insert into test values(1,10);
INSERT 0 1
blogdb=> insert into test(id) values(1);
ERROR: null value in column «val» violates not-null constraint
blogdb=> select * from test;
ERROR: current transaction is aborted, commands ignored until end of transaction block

> Транзакция есть последовательность стейтментов, каждый из которых может как выполнится, так и не выполниться. Если не выполнился один стейтмент, то не выполнился именно он.

Буква А в слове ACID. Если можно фиксировать транзакцию частично, это уже не транзакция.

Гм. Забавно. В phpMyAdmin база ведёт так, как ожидалось (откатывает всё нафиг), а вот из консоли происходит описанная в посте фигня.

Any suggestions?:)

видимо phpMyAdmin шлёт запросы в виде одного выражения разделённого точкой с запятой через mysqli_multi_query, а одно выражение mysql осиливает откатить :)