Gestion des transactions - Amazon Redshift
Mode par défaut de gestion des transactions des procédures stockéesGestion des transactions des procédures stockées en mode non atomique

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Gestion des transactions

Vous pouvez créer une procédure stockée avec un comportement de gestion des transactions par défaut ou un comportement non atomique.

Mode par défaut de gestion des transactions des procédures stockées

Le comportement de validation automatique du mode transactionnel par défaut entraîne la validation individuelle de chaque commande SQL qui s’exécute séparément. Un appel à une procédure stockée est traité comme une simple commande SQL. Les instructions SQL à l’intérieur d’une procédure se comportent comme si elles étaient dans un bloc de transaction qui commence implicitement quand l’appel démarre et se termine quand l’appel finit. Un appel imbriqué à une autre procédure est traité comme toute autre instruction SQL et opère au sein du contexte de la même transaction que l’appelant. Pour plus d’informations sur le comportement automatique de validation, consultez Isolement sérialisable.

Cependant, supposons que vous appelez une procédure stockée depuis un bloc de transaction spécifié par l’utilisateur (défini par BEGIN...COMMIT). Dans ce cas, toutes les instructions de la procédure stockée s’exécutent dans le contexte de la transaction spécifiée par l’utilisateur. La procédure n’est pas validée implicitement à la sortie. L’appelant contrôle la validation ou l’annulation de la procédure.

En cas d’erreur lors de l’exécution d’une procédure stockée, toutes les modifications apportées lors de la transaction en cours sont annulées.

Vous pouvez utiliser les instructions de contrôle de transaction suivantes dans une procédure stockée :

  • COMMIT : valide l’ensemble du travail effectué dans la transaction actuelle et démarre implicitement une nouvelle transaction. Pour de plus amples informations, veuillez consulter COMMIT.

  • ROLLBACK : restaure le travail effectué dans la transaction actuelle et démarre implicitement une nouvelle transaction. Pour de plus amples informations, veuillez consulter ROLLBACK.

TRUNCATE est une autre instruction qui peut être émise depuis une procédure stockée et qui influe sur la gestion de la transaction. Dans Amazon Redshift, TRUNCATE émet une validation implicitement. Ce comportement demeure le même dans le contexte des procédures stockées. Quand une instruction TRUNCATE est émise depuis une procédure stockée, elle valide la transaction en cours et en démarre une nouvelle. Pour de plus amples informations, veuillez consulter TRUNCATE.

Toutes les instructions qui suivent une instruction COMMIT, ROLLBACK ou TRUNCATE s’exécutent dans le contexte d’une nouvelle transaction. C’est le cas jusqu’à ce qu’une instruction COMMIT, ROLLBACK ou TRUNCATE soit détectée ou que la procédure stockée se termine.

Lorsque vous utilisez une instruction COMMIT, ROLLBACK ou TRUNCATE depuis une procédure stockée, les contraintes suivantes s’appliquent :

  • Si la procédure stockée est appelée depuis un bloc de transaction, elle ne peut pas émettre d’instruction COMMIT, ROLLBACK ou TRUNCATE. Cette restriction s’applique dans le corps de la procédure stockée elle-même et dans tout appel de procédure imbriquée.

  • Si la procédure stockée est créée avec les options SET config, elle ne peut pas émettre d’instruction COMMIT, ROLLBACK ou TRUNCATE. Cette restriction s’applique dans le corps de la procédure stockée elle-même et dans tout appel de procédure imbriquée.

  • Tout curseur ouvert, explicitement ou implicitement, est fermé automatiquement quand une instruction COMMIT, ROLLBACK ou TRUNCATE est traitée. Pour les contraintes sur les curseurs explicites et implicites, consultez Limites des procédures stockées.

Vous ne pouvez pas exécuter COMMIT ou ROLLBACK avec Dynamic SQL. Toutefois, TRUNCATE peut être exécuté avec Dynamic SQL. Pour de plus amples informations, veuillez consulter Instructions SQL dynamiques.

Lorsque vous travaillez avec des procédures stockées, considérez que les instructions BEGIN et END dans PL/pgSQL sont destinées uniquement au regroupement. Ils ne démarrent pas et ne finissent pas une transaction. Pour de plus amples informations, veuillez consulter Bloc.

L’exemple suivant illustre le comportement d’une transaction lors de l’appel d’une procédure stockée depuis un bloc de transaction explicite. Les deux instructions d’insertion émises depuis l’extérieur de la procédure stockée et celle émise depuis l’intérieur font toutes deux partie de la même transaction (3382). La transaction est validée quand l’utilisateur émet la validation explicite.

CREATE OR REPLACE PROCEDURE sp_insert_table_a(a int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
END;
$$;

Begin;
  insert into test_table_a values (1);
  Call sp_insert_table_a(2);
  insert into test_table_a values (3);
Commit; 

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |               stmt_text
--------+------+-----+---------+----------------------------------------
    103 | 3382 | 599 | UTILITY | Begin;
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3382 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3382 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3382 | 599 | UTILITY | COMMIT

À l’inverse, prenons un exemple où les mêmes instructions sont émises depuis l’extérieur d’un bloc de transactions explicite et où le paramètre de validation automatique de la session est activé. Dans ce cas, chaque instruction s’exécute dans sa propre transaction.

insert into test_table_a values (1);
Call sp_insert_table_a(2);
insert into test_table_a values (3);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |                                                                    stmt_text
--------+------+-----+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3388 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3388 | 599 | UTILITY | COMMIT
    103 | 3389 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3389 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3389 | 599 | UTILITY | COMMIT
    103 | 3390 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3390 | 599 | UTILITY | COMMIT

L’exemple suivant émet une instruction TRUNCATE après l’insertion dans test_table_a. L’instruction TRUNCATE émet une validation implicite qui valide la transaction courante (3335) et en démarre une nouvelle (3336). La nouvelle transaction est validée quand la procédure cesse.

CREATE OR REPLACE PROCEDURE sp_truncate_proc(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
  TRUNCATE test_table_b;
  INSERT INTO test_table_b values (b);
END;
$$;

Call sp_truncate_proc(1,2);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                             stmt_text
--------+------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3335 | 23636 | UTILITY | Call sp_truncate_proc(1,2);
    103 | 3335 | 23636 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3335 | 23636 | UTILITY | TRUNCATE test_table_b
    103 | 3335 | 23636 | UTILITY | COMMIT
    103 | 3336 | 23636 | QUERY   | INSERT INTO test_table_b values ( $1 )
    103 | 3336 | 23636 | UTILITY | COMMIT

L’exemple suivant émet une commande TRUNCATE depuis un appel imbriqué. L’instruction TRUNCATE valide tout le travail effectué jusque-là dans les procédures externes et internes d’une transaction (3344). Elle démarre une nouvelle transaction (3345). La nouvelle transaction est validée quand la procédure externe cesse.

CREATE OR REPLACE PROCEDURE sp_inner(c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO inner_table values (c);
  TRUNCATE outer_table;
  INSERT INTO inner_table values (d);
END;
$$;

CREATE OR REPLACE PROCEDURE sp_outer(a int, b int, c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO outer_table values (a);
  Call sp_inner(c, d);
  INSERT INTO outer_table values (b);
END;
$$;

Call sp_outer(1, 2, 3, 4);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                              stmt_text
--------+------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3344 | 23636 | UTILITY | Call sp_outer(1, 2, 3, 4);
    103 | 3344 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | CALL sp_inner( $1 , $2 )
    103 | 3344 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | TRUNCATE outer_table
    103 | 3344 | 23636 | UTILITY | COMMIT
    103 | 3345 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3345 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3345 | 23636 | UTILITY | COMMIT

L’exemple suivant montre que le curseur cur1 a été fermé quand l’instruction TRUNCATE a été validée.

CREATE OR REPLACE PROCEDURE sp_open_cursor_truncate()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  TRUNCATE table test_table_b;
  Loop
    fetch cur1 into rec;
    raise info '%', rec.c1;
    exit when not found;
  End Loop;
END
$$;

call sp_open_cursor_truncate();
ERROR: cursor "cur1" does not exist
CONTEXT: PL/pgSQL function "sp_open_cursor_truncate" line 8 at fetch

L’exemple suivant émet une instruction TRUNCATE et ne peut pas être appelé depuis un bloc de transaction explicite.

CREATE OR REPLACE PROCEDURE sp_truncate_atomic() LANGUAGE plpgsql
AS $$
BEGIN
  TRUNCATE test_table_b;
END;
$$;

Begin;
  Call sp_truncate_atomic();
ERROR: TRUNCATE cannot be invoked from a procedure that is executing in an atomic context.
HINT: Try calling the procedure as a top-level call i.e. not from within an explicit transaction block. 
Or, if this procedure (or one of its ancestors in the call chain) was created with SET config options, recreate the procedure without them.
CONTEXT: SQL statement "TRUNCATE test_table_b"
PL/pgSQL function "sp_truncate_atomic" line 2 at SQL statement

L’exemple suivant montre qu’un utilisateur qui n’est pas un super-utilisateur ni le propriétaire d’une table peut émettre une instruction TRUNCATE sur la table. L’utilisateur effectue cette opération à l’aide d’une procédure stockée Security Definer. L’exemple illustre les actions suivantes :

  • L’utilisateur1 crée la table test_tbl.

  • L’utilisateur1 crée une procédure stockée sp_truncate_test_tbl.

  • L’utilisateur1 accorde un privilège EXECUTE au niveau de la procédure stockée à l’utilisateur2.

  • L’utilisateur2 exécute la procédure stockée pour tronquer la table test_tbl. L’exemple montre le nombre de lignes avant et après la commande TRUNCATE. 

set session_authorization to user1;
create table test_tbl(id int, name varchar(20));
insert into test_tbl values (1,'john'), (2, 'mary');
CREATE OR REPLACE PROCEDURE sp_truncate_test_tbl() LANGUAGE plpgsql
AS $$
DECLARE
  tbl_rows int;
BEGIN
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount before Truncate: %', tbl_rows;
  TRUNCATE test_tbl;
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount after Truncate: %', tbl_rows;
END;
$$ SECURITY DEFINER;
grant execute on procedure sp_truncate_test_tbl() to user2;
reset session_authorization;


set session_authorization to user2;
call sp_truncate_test_tbl();
INFO:  RowCount before Truncate: 2
INFO:  RowCount after Truncate: 0
CALL
reset session_authorization;

L’exemple suivant émet deux fois COMMIT. Le premier COMMIT valide l’ensemble du travail effectué dans la transaction 10363 et démarre implicitement la transaction 10364. La transaction 10364 est validée par la deuxième instruction COMMIT. 

CREATE OR REPLACE PROCEDURE sp_commit(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table values (a);
  COMMIT;
  INSERT INTO test_table values (b);
  COMMIT;
END;
$$;

call sp_commit(1,2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;
 userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+-----------------------------------------------------------------------------------------------------------------
    100 | 10363 | 3089 | UTILITY | call sp_commit(1,2);
    100 | 10363 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10363 | 3089 | UTILITY | COMMIT
    100 | 10364 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10364 | 3089 | UTILITY | COMMIT

L’exemple suivant émet une instruction ROLLBACK si sum_vals est supérieur à 2. La première instruction ROLLBACK restaure tout le travail effectué dans la transaction 10377 et démarre une nouvelle transaction 10378. La transaction 10378 est validée quand la procédure se termine. 

CREATE OR REPLACE PROCEDURE sp_rollback(a int, b int) LANGUAGE plpgsql
AS $$
DECLARE
  sum_vals int;
BEGIN
  INSERT INTO test_table values (a);
  SELECT sum(c1) into sum_vals from test_table;
  IF sum_vals > 2 THEN
    ROLLBACK;
  END IF;
  
  INSERT INTO test_table values (b);
END;
$$;

call sp_rollback(1, 2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    100 | 10377 | 3089 | UTILITY | call sp_rollback(1, 2);
    100 | 10377 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10377 | 3089 | QUERY   | SELECT sum(c1) from test_table
    100 | 10377 | 3089 | QUERY   | Undoing 1 transactions on table 133646 with current xid 10377 : 10377
    100 | 10378 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10378 | 3089 | UTILITY | COMMIT

Gestion des transactions des procédures stockées en mode non atomique

Une procédure stockée créée en mode NONATOMIC a un comportement de contrôle des transactions différent de celui d’une procédure créée en mode par défaut. À l’instar de la validation automatique des commandes SQL en dehors des procédures stockées, chaque instruction SQL à l’intérieur d’une procédure NONATOMIC s’exécute dans sa propre transaction et est validée automatiquement. Si un utilisateur commence un bloc de transaction explicite dans une procédure stockée NONATOMIC, les instructions SQL contenues dans le bloc ne sont pas automatiquement validées. Le bloc de transaction contrôle la validation ou la restauration des instructions qu’il contient.

Dans les procédures stockées NONATOMIC, vous pouvez ouvrir un bloc de transaction explicite à l’intérieur de la procédure à l’aide de l’instruction START TRANSACTION. Cependant, s’il existe déjà un bloc de transaction ouvert, cette déclaration ne fera rien car Amazon Redshift ne prend pas en charge les sous transactions. La transaction précédente se poursuit.

Lorsque vous utilisez des boucles FOR à l’intérieur d’une procédure NONATOMIC, veillez à ouvrir un bloc de transaction explicite avant de parcourir les résultats d’une requête. Sinon, le curseur est fermé lorsque l’instruction SQL à l’intérieur de la boucle est automatiquement validée.

Voici quelques éléments à prendre en compte lors de l’utilisation du mode de comportement NONATOMIC :

  • Chaque instruction SQL contenue dans la procédure stockée est automatiquement validée s’il n’y a pas de bloc de transaction ouvert et si l’auto-validation de la session est définie sur ON.

  • Vous pouvez émettre une COMMIT/ROLLBACK/TRUNCATE déclaration pour mettre fin à la transaction si la procédure stockée est appelée depuis un bloc de transactions. Cela n’est pas possible en mode par défaut.

  • Vous pouvez lancer une instruction START TRANSACTION pour commencer un bloc de transactions à l’intérieur de la procédure stockée.

Les exemples suivants illustrent le comportement des transactions lors de l’utilisation de procédures stockées NONATOMIC. Pour tous les exemples suivants, la validation automatique est définie sur ON.

Dans l’exemple suivant, une procédure stockée NONATOMIC comporte deux instructions INSERT. Lorsque la procédure est appelée en dehors d’un bloc de transactions, chaque instruction INSERT au sein de la procédure est automatiquement validée. 

CREATE TABLE test_table_a(v int); 
CREATE TABLE test_table_b(v int); 

CREATE OR REPLACE PROCEDURE sp_nonatomic_insert_table_a(a int, b int) NONATOMIC AS
$$
BEGIN
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_insert_table_a(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1792 | 1073807554 | UTILITY | Call sp_nonatomic_insert_table_a(1,2);
      1 | 1792 | 1073807554 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1792 | 1073807554 | UTILITY | COMMIT
      1 | 1793 | 1073807554 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1793 | 1073807554 | UTILITY | COMMIT
(5 rows)

Cependant, lorsque la procédure est appelée à partir d’un bloc BEGIN..COMMIT, toutes les instructions font partie de la même transaction (xid=1799). 

Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_insert_table_a(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |                stmt_text                 
--------+------+------------+---------+------------------------------------------
      1 | 1799 | 1073914035 | UTILITY | Begin;
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1799 | 1073914035 | UTILITY | Call sp_nonatomic_insert_table_a(20,30);
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1799 | 1073914035 | UTILITY | COMMIT
(7 rows)

Dans cet exemple, deux instructions INSERT se trouvent entre START TRANSACTION...COMMIT. Lorsque la procédure est appelée en dehors d’un bloc de transaction, les deux instructions INSERT se trouvent dans la même transaction (xid=1866). 

CREATE OR REPLACE PROCEDURE sp_nonatomic_txn_block(a int, b int) NONATOMIC AS
$$
BEGIN
    START TRANSACTION;
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
    COMMIT;
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_txn_block(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1865 | 1073823998 | UTILITY | Call sp_nonatomic_txn_block(1,2);
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1866 | 1073823998 | UTILITY | COMMIT
(4 rows)

Lorsque la procédure est appelée à l’intérieur d’un bloc BEGIN...COMMIT, la START TRANSACTION à l’intérieur de la procédure ne fait rien parce qu’il y a déjà une transaction ouverte. La commande COMMIT de la procédure valide la transaction en cours (xid=1876) et en démarre une nouvelle.

Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_txn_block(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1876 | 1073832133 | UTILITY | Begin;
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1876 | 1073832133 | UTILITY | Call sp_nonatomic_txn_block(20,30);
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1876 | 1073832133 | UTILITY | COMMIT
      1 | 1878 | 1073832133 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1878 | 1073832133 | UTILITY | COMMIT
(8 rows)

Cet exemple montre comment travailler avec des boucles de curseur. La table test_table_a possède trois valeurs. L’objectif est de parcourir les trois valeurs et de les insérer dans la table test_table_b. Si une procédure stockée NONATOMIC est créée de la manière suivante, le curseur « cur1 » n’existe plus après l’exécution de l’instruction INSERT dans la première boucle. En effet, la validation automatique de la commande INSERT ferme le curseur ouvert.

insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
ERROR:  cursor "cur1" does not exist
CONTEXT:  PL/pgSQL function "sp_nonatomic_cursor" line 7 at fetch

Pour que la boucle du curseur fonctionne, placez-la entre START TRANSACTION...COMMIT.

insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  START TRANSACTION;
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
  COMMIT;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
INFO:  2
INFO:  3
CALL