I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause Here is a reproducible sample: CREATE TABLE U ( -- user id int not null primary key, name varchar(222) ); CREATE TABLE O ( -- object id int not null primary key, name varchar(222) ); CREATE TABLE OU ( -- user object id int not null auto_increment primary key, object_id int, user_id int, -- (object_id, user_id) should be UNIQUE (not enforced by a constraint) CONSTRAINT fk_object_id FOREIGN KEY (object_id) REFERENCES O(id) ON DELETE CASCADE, CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES U(id) ON DELETE CASCADE ); INSERT INTO U VALUES (1, 'foo'), (2, 'bar'); -- Start 2 concurrent transactions (A, B) BEGIN; -- A BEGIN; -- B INSERT INTO O VALUES (1, 'a'); -- A INSERT INTO O VALUES (2, 'b'); -- B SAVEPOINT s_a; -- A SAVEPOINT s_b; -- B SELECT * FROM OU WHERE object_id = 1 FOR UPDATE; -- A SELECT * FROM OU WHERE object_id = 2 FOR UPDATE; -- B INSERT INTO OU(object_id, user_id) VALUES (1, 1); -- A INSERT INTO OU(object_id, user_id) VALUES (2, 1); -- B one of the transactions will fail with a Deadlock found when trying to get lock; try restarting transaction error. The problem is that it rolls back the **whole** transaction without any way for me to gracefully handle the error. In that example, the object 2 is lost. I would **really** like for it to still persist and handle the second insert a few seconds / minutes after another transaction. The way the code is currently designed does not allow fine tuning of the transaction block (I cannot commit after the first insert then start another transaction) Is there any way to tell MySQL to not rollback on that deadlock error, or to rollback specifically to the savepoint? **edit** here is the innodb engine status *** (1) TRANSACTION: TRANSACTION 3727668996, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 20145497, OS thread handle 0x7f4fdba2b700, query id 15334836096 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.33 user1 update INSERT INTO Tablename (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655988, 1, 26399, NULL, '2017-04-20 19:04:21') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 201 page no 1012 n bits 840 index product_id of table dbname.Tablename trx id 3727668996 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION 3727668998, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 20145647, OS thread handle 0x7f5d6d253700, query id 15334836100 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.35 user1 update INSERT INTO Tablename (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655991, 1, 26399, NULL, '2017-04-20 19:04:21') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 201 page no 1012 n bits 840 index product_id of table dbname.Tablename trx id 3727668998 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 201 page no 1012 n bits 840 index product_id of table dbname.Tablename trx id 3727668998 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (2)

While running PostgreSQL 13.12 (occurs in several versions of PG11/PG13) using SQLAlchemy 1.3, we are occasionally hitting issues where increased concurrency leaves certain transactions (and their nested transactions) in the "Idle in Transaction" state with the RELEASE SAVEPOINT ... query. Looking at the currently running queries, it is not clear why transactions have stopped moving forward. I have also observed this behavior without any hanging locks: | pid | datname | duration | query | state | application_name | wait_event_type | | --- | ------- | -------- | ----- | ----- | ---------------- | --------------- | | 27662 | app | 22:22:37.429569 | select pg_advisory_xact_lock(resource_id) from resource where uuid = '018afcac-a5ab-7a5c-9eb4-2d8b0be4b556' | active | http.api.1 | Lock | | 25830 | app | 22:22:29.236398 | RELEASE SAVEPOINT sa_savepoint_5 | idle in transaction | http.api.0 | Client | | 21490 | app | 22:22:29.015862 | select pg_advisory_xact_lock(resource_id)
from resource
where uuid = '018afcac-a5ab-7a5c-9eb4-2d8b0be4b556' | active | http.api.0 | Lock | | 27674 | app | 22:22:27.780581 | RELEASE SAVEPOINT sa_savepoint_3 | idle in transaction | http.api.2 | Client | | 29120 | app | 22:22:26.053851 | select pg_advisory_xact_lock(resource_id)
from resource
where uuid = '018afcac-a5ab-7a5c-9eb4-2d8b0be4b556' | active | http.api.2 | Lock | Any way to debug this? This API call generally works, with the session being cleaned up and does not fail consistently. We are using SQL Alchemy's connection pooling to manage the connections - closed sessions will return the connection to the pool and issue a rollback to clean up the connection (when commit should have committed all other statements).

The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT :

for ptrEnt in entite loop
      -- positionnement du point de retour pour une entité
    savepoint rbk;
      -- purge de la table des redevances de l''entite
    test := pckg_activ.initredevanceentite(ptrEnt.cod_ent);
      -- calcul de la redevance
      test := pckg_activ.calculredevanceentite(ptrEnt.cod_ent);
    -- controle calcul de la bonne execution pour l''entité
    if (test = 0) then
        -- initialisation des departements associes a une entite
        test := pckg_activ.initdepartement(ptrEnt.cod_ent);
      -- validation des modifications
      commit;
      -- generation de l''etat recapitulatif
      test := pckg_activ.recapentitegestionnaire(ptrEnt.cod_ent,ficHisto);
    else
        rollback TO SAVEPOINT rbk;
      report := jour + 7;
      update sav_rdv_date
          set date_dem = report
          where cod_ent = ptrEnt.cod_ent;
        RAISE NOTICE '!!erreur de patrimoine => abandon du traitement pour cette entite';
      commit;
    end if;
  end loop;

**entite** is a cursor that has been declared and the **for loop** is in a *BEGIN* bloc. I get the error ERROR:

error at or near "TO"

. I think that the syntax is correct , it it related to the cursor ?

I am having trouble in executing the following FUNCTION in PostgreSQL

CREATE OR REPLACE FUNCTION pckg_fcvoi.supprimer_voies_plus_42c (CodeEntite text) RETURNS VOID AS $body$
DECLARE

    cpt_voies_ACTIV  numeric := 0;
    cpt_voies_User   numeric := 0;
    cpt_voies_42C    numeric := 0;
    cpt_voies_sup    numeric := 0;
    /*----------------------------------------------------------------*/
    /* Liste des voies non importees de 42C, susceptibles d'etre      */
    /* supprimees.                                                    */
    /*----------------------------------------------------------------*/
    cursor_VOIES_PLUS_42C CURSOR FOR
      SELECT com_commune.cod_ent,
             com_voie.cod_insee,
             com_voie.cod_rivoli,
             com_voie.lib
        FROM com_voie,
             com_commune
       WHERE com_commune.cod_insee = com_voie.cod_insee
         AND com_commune.cod_ent   = CodeEntite
         AND com_voie.origine_voie = 'D';

BEGIN
    -- DBMS_OUTPUT.ENABLE(1000000);

    SELECT COUNT(*) INTO STRICT cpt_voies_ACTIV
      FROM com_voie,
           com_commune
     WHERE com_commune.cod_insee = com_voie.cod_insee
       AND com_commune.cod_ent = CodeEntite;
    SELECT COUNT(*) INTO STRICT cpt_voies_User
      FROM com_voie,
           com_commune
     WHERE com_commune.cod_insee = com_voie.cod_insee
       AND com_commune.cod_ent = CodeEntite
       and com_voie.origine_voie = 'U';
    SELECT COUNT(*) INTO STRICT cpt_voies_42C
      FROM com_voie,
           com_commune
     WHERE com_commune.cod_insee = com_voie.cod_insee
       AND com_commune.cod_ent = CodeEntite
       and com_voie.origine_voie = 'S';
    SELECT COUNT(*) INTO STRICT cpt_voies_sup
      FROM com_voie,
           com_commune
     WHERE com_commune.cod_insee = com_voie.cod_insee
       AND com_commune.cod_ent = CodeEntite
       and com_voie.origine_voie = 'D';

    RAISE NOTICE '  - Nombre de voies dans la base ACTIV pour % : %', CodeEntite, cpt_voies_ACTIV;
    RAISE NOTICE '  - Nombre de voies utilisateur        pour % : %', CodeEntite, cpt_voies_User;
    RAISE NOTICE '  - Nombre de voies importees de 42C   pour % : %', CodeEntite, cpt_voies_42C;
    RAISE NOTICE '  - Nombre de voies supprimables       pour % : %', CodeEntite, cpt_voies_sup;

    /*---------------------------------------------------------------------------------------*/
    /* Pour toutes les voies susceptibles d'etre supprimees                                  */
    /* dont le cod_ent est a traiter :                                                       */
    /* - Supprimer la voie                                                                   */
    /* - si la suppression echoue                                                            */
    /*   alors                                                                               */
    /*     afficher la voie est supprimable mais dossier attache                             */
    /*   sinon                                                                               */
    /*     afficher la voie est supprimee                                                    */
    /*   finsi                                                                               */
    /*---------------------------------------------------------------------------------------*/
    RAISE NOTICE 'Voies ne pouvant etre supprimees de la base ACTIV pour % :', CodeEntite;
    RAISE NOTICE '---------------------------------------------------------';
    cpt_voies_sup := 0;
    FOR enreg_VOIES_PLUS_42C IN cursor_VOIES_PLUS_42C LOOP
      BEGIN
        DELETE FROM com_voie
         WHERE cod_insee  = enreg_VOIES_PLUS_42C.cod_insee
           AND cod_rivoli = enreg_VOIES_PLUS_42C.cod_rivoli;
/*        DBMS_OUTPUT.PUT_LINE('Suppression de '||enreg_VOIES_PLUS_42C.COD_ENT||';'||enreg_VOIES_PLUS_42C.COD_INSEE||';'||enreg_VOIES_PLUS_42C.COD_RIVOLI||';'||enreg_VOIES_PLUS_42C.LIB);*/
        cpt_voies_sup := cpt_voies_sup + 1;
      EXCEPTION
        /*-----------------------------------------------------------*/
        /* La suppression de la voie a echoue                        */
        /*-----------------------------------------------------------*/
        WHEN OTHERS THEN
          IF position('ACTIV.FK_CORRESP_VOIE' in SQLERRM) > 0
          THEN
            /*-------------------------------------------------------------*/
            /* La suppression de la voie a echoue :                        */
            /* supprimer la correspondance voie-gestionnaire, puis la voie */
            /*-------------------------------------------------------------*/
            BEGIN
/*              DBMS_OUTPUT.PUT_LINE(enreg_VOIES_PLUS_42C.COD_ENT||';'||enreg_VOIES_PLUS_42C.COD_INSEE||';'||enreg_VOIES_PLUS_42C.COD_RIVOLI||';'||enreg_VOIES_PLUS_42C.LIB||'; Declaree dans SAV_CORRESP_VOIE_GEST');*/
              SAVEPOINT SP_FK_CORRESP_VOIE;
              DELETE FROM sav_corresp_voie_gest
               WHERE cod_insee  = enreg_VOIES_PLUS_42C.cod_insee
                 AND cod_rivoli = enreg_VOIES_PLUS_42C.cod_rivoli;
              DELETE FROM com_voie
               WHERE cod_insee  = enreg_VOIES_PLUS_42C.cod_insee
                 AND cod_rivoli = enreg_VOIES_PLUS_42C.cod_rivoli;
/*              DBMS_OUTPUT.PUT_LINE('Suppression de '||enreg_VOIES_PLUS_42C.COD_ENT||';'||enreg_VOIES_PLUS_42C.COD_INSEE||';'||enreg_VOIES_PLUS_42C.COD_RIVOLI||';'||enreg_VOIES_PLUS_42C.LIB);*/
              cpt_voies_sup := cpt_voies_sup + 1;
            EXCEPTION
              WHEN OTHERS THEN
                ROLLBACK TO SAVEPOINT SP_FK_CORRESP_VOIE;
            END;
          ELSE
            /*-----------------------------------------------------------*/
            /* La suppression de la voie a echoue : afficher la cause    */
            /*-----------------------------------------------------------*/
            RAISE NOTICE '%;%;%;%;%', enreg_VOIES_PLUS_42C.COD_ENT, enreg_VOIES_PLUS_42C.COD_INSEE, enreg_VOIES_PLUS_42C.COD_RIVOLI, enreg_VOIES_PLUS_42C.LIB, SUBSTR(SQLERRM, 1, 200);
          END IF;
      END;
    END LOOP;
    RAISE NOTICE '  - Nombre de voies supprimees         pour % : %', CodeEntite, cpt_voies_sup;

  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Exception dans pckg_fcvoi.supprimer_voies_plus_42c(%) : %, %', CodeEntite, SQLSTATE, SUBSTR(SQLERRM, 1, 100);
      RAISE NOTICE 'ARRET DU TRAITEMENT !';
      ROLLBACK;
  END;

I am having this error > syntax error at or near "TO". It is related to the exception block? Any solutions?

I am not happy with the Wikipedia article about nested transactions . I would not use the word "commit" for a nested transaction. For me "commit" means "durability". A nested transaction does not provide durability. 1. Is it ok to use the word "commit" for nested transactions? 2. What word could be used, if it is not ok?

What is the correct way on setting and rolling back to savepoint on SQLAnywhere. Having this snippet: begin transaction; SAVEPOINT spt_abc; insert into eins (pl1) values (5); SAVEPOINT spt_123; UPDATE eins SET pl1 = 10 where pl1 = 5; ROLLBACK TO SAVEPOINT spt_abc; commit; Getting this error message while running it in dbisql: > Could not execute statement. Savepoint 'spt_abc' not found SQLCODE=-220, ODBC 3 State="HY000" Line 6, column 1 You can continue > executing or stop. > > ROLLBACK TO SAVEPOINT spt_abc the very same happens when trying to rollback to spt_123.