MySQL: обработка отката во вложенных хранимых процедурах с транзакциями

Question

У меня есть 4 хранимых процедуры, давайте назовем их spA, spB, spC и spD.

Каждая из этих хранимых процедур имеет начальную транзакцию, фиксацию и откат.

spA в настоящее время является родительской хранимой процедурой (SP), и внутри нее я вызываю spB, spC, spD в соответствующем порядке. По-видимому, поскольку все SP имеют фиксацию в них, при сбое spC операции DML, выполняемые в spB, не возвращаются, поскольку они уже зафиксированы, и то же самое, когда spD завершается ошибкой, операции в spB и spC не откатываются.

Причина, по которой я сделал коммит в spB, spC и spD, заключается в том, что позже я мог бы просто использовать любой из этих SP непосредственно для выполнения операций, относящихся к SP.

В настоящее время я планирую использовать флаг входного бита, чтобы решить, следует ли совершать транзакцию. В MS SQL есть возможность проверить @@transcount, однако я не могу найти что-то подобное в MySQL.

Мне было интересно, есть ли лучший способ справиться с этим сценарием.

Answer 1

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

Попытка обмануть ROLLBACK и COMMIT, как Microsoft SQL Server делает с @@trancount приводит к аномалиям, с которыми еще сложнее работать, чем с указанными выше рекомендациями, для управления транзакциями на уровне приложений.

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

Answer 2

Управляйте своей собственной переменной, похоже, что MySQL не справляется с этим поведением. поэтому добавьте входной бит p_tran_call и установите его в 1, если spA вызывает else 0, затем выполните if, если p_tran_call равен 0, затем начните tran и откат.

уверен, что так я и сделал.

процедура создания spB(бит p_tran_control), являющаяся

   declare exit handler for sqlexception
   begin
         if p_tran_control = 0 the 
             rollback;
         end if;
   end; #end of handler

  if p_tran_control = 0 then
         begin transaction;
   end if;


    #more code;


   if p_tran_control = 0 then
        commit;
    end if;

конец

создать процедуру spA begin

  begin transaction;


 call spB(1);


  commit transaction;

конец

Теперь, что я заметил, транзакции не переходят ни на какие вложенные вызовы. так что если вы вызываете spB из spA, который работает, но если вы вызываете spC из spB, который был вызван из spA, который содержит транзакцию, то spC будет фиксировать. даже когда автокоммит выключен;