Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nxrG2-0004Gq-AK for pgsql-sql@arkaria.postgresql.org; Sun, 05 Jun 2022 14:29:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nxrFz-0006yl-Tm for pgsql-sql@arkaria.postgresql.org; Sun, 05 Jun 2022 14:29:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nxrFz-0006yc-H6 for pgsql-sql@lists.postgresql.org; Sun, 05 Jun 2022 14:29:31 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nxrFx-0004RG-Aw for pgsql-sql@lists.postgresql.org; Sun, 05 Jun 2022 14:29:31 +0000 Received: by mail-ej1-x62e.google.com with SMTP id n10so24489465ejk.5 for ; Sun, 05 Jun 2022 07:29:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=nnTbjF2pNGmdjIgwfZZJqlq1N3D4yna+AW4TkjALE3w=; b=BBNMfPnulm5cU0CWVxNL38UmcMcVMpYv8cB6ti78C3Gf6Ail14p2myw2LWr2BmT8UM L37P8J5RvfaLY3v0M6CrMpuAXLutAJX0QIOUWPLTBvmUtOLGADqOjB+Q+TNk3KZVU5Rd LpTckO3f3dz8S9Hy3GiPhakkOgLe8YSJVI9VbnRr+7DdQoYTzW+wYB/4jQZKW8MfhLZU H3oQCSU9NZgNc4jirVLXNmkbE+KOH8xJa5ZMciZEfhjI5C4HBnNwONGLV+aQsBeSRC0b WSJ+Y8P9USb3yJMpJPu14fInZVta7vIoNa2gsfoxHeRMz2URJVgPuyXozLNNGnvSf29t TbdQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=nnTbjF2pNGmdjIgwfZZJqlq1N3D4yna+AW4TkjALE3w=; b=4uBt2xq65EX1GwQ6cEnAhDK2hYfpIKKBaJ0tBSWgMG+JDMNkYl5qyhWy68kK4gq0uu WUH2jOiiv/FohPHj5AjtAZHPz57rsRGczjocZXNRCYN1R/BEqsWxGRsJZ98VC178hzfI GBzHp9GGCT6aeRAjC5atijfzF3oJZqFPBgT9OLgwrqORizCOmVsvfqe4u/XB6G5ZpLqZ kUwE1yUxBFMmuFzs30KhsSA/KDaU83MLkFrvRxvFfrfCfxsWtmz5TpRWnpUS49TBIabC +pau4v4GVse71bLP03de2sg9HzECiG+dBsNgoDB6jtRAGzpgT5lETHaAGS1fQ0O5MeND q2JQ== X-Gm-Message-State: AOAM531c5+8Fxn1tOe3/1tH6qATW9Ba9K689qNV9Qy9erhLZ87Eao2fj WiR4SvvRZUZ+TH6a/jrepegyM/EQkAiHB8FjCs2gcBOt1JI= X-Google-Smtp-Source: ABdhPJyLehkz2tehrOGtVqR0R5gvrO2Ne2wfiVdgBBrdSY/JEKhpQqO1efV+WPKb2U0n3h2m1S7mhhpXO2jKHFEBcCc= X-Received: by 2002:a17:906:5204:b0:6fe:902a:da93 with SMTP id g4-20020a170906520400b006fe902ada93mr18462936ejm.155.1654439367938; Sun, 05 Jun 2022 07:29:27 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:640c:1fcc:b0:165:a4f7:ea4a with HTTP; Sun, 5 Jun 2022 07:29:27 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Sun, 5 Jun 2022 07:29:27 -0700 Message-ID: Subject: Re: Strange behavior of transcations To: Hui Jackson Cc: "pgsql-sql@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000d417b705e0b42e8c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d417b705e0b42e8c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sunday, June 5, 2022, Hui Jackson wrote: > I am working on node-postgres and there is a strange transactions. > The function aims at locking user's wallet until the refund process is > complete, then will update item's has_refund to true. > The problem I am facing is the program return no error, but the database > is not updated. Unless I spam the function for multiple times then the > value is updated. > If I remove begin and commit, then the function work perfectly. > const refundService =3D (itemId) =3D>{ > await pgPool.query('BEGIN;'); > const users =3D (await pgPool.query('SELECT * from app_user where > $1=3DANY(purchase_list);', [ itemId ])).rows; > for(let i =3D 0; i < users.length; i++){ > refund(users[i]) > } > await pgPool.query('UPDATE item_lists SET has_refund =3D $1 where id = =3D > $2;', [true, itemId ]); > await pgPool.query('COMMIT;'); > } > > const refund =3D (user) =3D>{ > const refund =3D 10 > await pgPool.query('UPDATE app_user SET wallet =3D wallet + $1', [ref= und > ]); > } > I think you are mis-using your pool. If you want transactions you need checkout a connection from the pool and use it for every command in the transaction. The one-shot query method on the pool is meant for standalone commands. I say think because I=E2=80=99m unsure why you=E2=80=99d get no = updates instead of updates but no transaction=E2=80=A6 David J. --000000000000d417b705e0b42e8c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sunday, June 5, 2022, Hui Jackson <jackhts4@gmail.com> wrote:
I am working on node-postgres and there is a strange transact= ions.
The function aims at locking user's wallet until the refund p= rocess=C2=A0is complete, then will update item's has_refund to true.The problem I am facing is the program return no error, but the database i= s not updated. Unless I spam the function for multiple times then the value= is updated.
If I remove begin and commit, then the function work perfec= tly.
const refundService= =3D (itemId) =3D>{
=C2=A0 =C2=A0 await pgPool.query('BEGIN;');
=C2= =A0 =C2=A0 const users =3D (await pgPool.<= span style=3D"color:rgb(220,220,170)">query('SELECT * from app_user where $1=3DANY(purchase_list);&#= 39;, [ itemId ])).rows;
=C2=A0 =C2=A0 for(let i =3D 0; i < users= .length; i++){
=C2=A0 =C2=A0 =C2=A0 =C2=A0 refund(users[i])
=C2=A0 =C2=A0 }
=C2=A0 =C2=A0 await pgPool.query('UPDATE item_lists SET has_refund =3D $1 where id = =3D $2;', [true, = =C2=A0itemId ]);
= =C2=A0 =C2=A0 await pgPool.query('COMMIT;'= ;);
}
=C2=A0 =C2=A0
const re= fund =3D (user) =3D>{
=C2=A0 =C2=A0 const refund =3D 10
=C2=A0 =C2=A0 await pgPool.query(= 9;UPDATE app_user SET wallet =3D wallet + $1', [refund ]);
}
=


I think you are mis-using y= our pool.=C2=A0 If you want transactions you need checkout a connection fro= m the pool and use it for every command in the transaction.=C2=A0 The one-s= hot query method on the pool is meant for standalone commands.=C2=A0 I say = think because I=E2=80=99m unsure why you=E2=80=99d get no updates instead o= f updates but no transaction=E2=80=A6

David J.

--000000000000d417b705e0b42e8c--