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 1nxrNw-0004ZT-Ts for pgsql-sql@arkaria.postgresql.org; Sun, 05 Jun 2022 14:37:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nxrNv-0002l3-KB for pgsql-sql@arkaria.postgresql.org; Sun, 05 Jun 2022 14:37:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nxrNv-0002jl-4H for pgsql-sql@lists.postgresql.org; Sun, 05 Jun 2022 14:37:43 +0000 Received: from mail-pg1-x531.google.com ([2607:f8b0:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nxrNp-0005LM-TV for pgsql-sql@lists.postgresql.org; Sun, 05 Jun 2022 14:37:42 +0000 Received: by mail-pg1-x531.google.com with SMTP id f65so778709pgc.7 for ; Sun, 05 Jun 2022 07:37:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=heimdalldata.com; s=google; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=X1iGcgNGi9tE/p5iKZDgxEYDH8sAFKbd9GFEvzjgyKs=; b=rlzNnX3miT2K/lCXWD6LG+rpAByw5A9+cl6Q1e4YN/bhSwsPJr9F9ZrT7ccWYCW/Xi +/KIeLoI04/kSD6XwKvhaVDAc8pf7JPnb/hGY7yEzKPWXTrXginIkilsmtrquH89697f 0MD1dBa2QP0xjwqbVLTKMWR7KauKBxKjGX9tA= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=X1iGcgNGi9tE/p5iKZDgxEYDH8sAFKbd9GFEvzjgyKs=; b=T+9y96OOvpX77qgFcaYpSJ3ypK3ic319H/XwrNIlHgmuUjI7/mZC/MEwtHZwcf23wT YoK4vnRiP1T5LDV6Eg+4demCwtbc6Y4301E4t9SJQYRb/+FsVx2idI9bI2dS/BnbH1DA bojv0gKz6MXi3jzwfT/tzuH1ujOEqascAix51fuHxfuxOEINFnc3rBjtd+gGMl7xiaWG XKYiaJqsaJyP2YNRzdIsvz/yv3/vAj/O01XHtUXWXOnrNSRL6T5xTH6fX1K9OTuXGd/d kWlfFJJ6c2tGH6jgUpJKBKFofd7sd+OEc7yBE5FIIXWYcOE87Efxs1Lw1sGOpBAVJi3l GR2w== X-Gm-Message-State: AOAM530JLzgILFHrU6molqmDQ+8hY+ZdNDTANBKjUEaBAZBg1Djsq+Yz N3FNhwFZyH5SLpC04hv9USGK3C8quvkxU4+BdDQduQ== X-Google-Smtp-Source: ABdhPJx/FyF+P9kJeP1GjVwHLQqBRBPKbsY44RW+giOZ9x8Fjq8bV/UCYhyMeMGLpAhjPFNu/esUMHZxnv0HIt69zGA= X-Received: by 2002:a63:f403:0:b0:3fc:e1c2:6a53 with SMTP id g3-20020a63f403000000b003fce1c26a53mr15770437pgi.302.1654439855634; Sun, 05 Jun 2022 07:37:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Erik Brandsberg Date: Sun, 5 Jun 2022 10:37:23 -0400 Message-ID: Subject: Re: Strange behavior of transcations To: "David G. Johnston" Cc: Hui Jackson , pgsql-sql Content-Type: multipart/alternative; boundary="000000000000e5d2e205e0b44b1a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e5d2e205e0b44b1a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Correct, he is reading from the connection while writing to it. Unless all the rows have been read, you can't use it to do a write, at the protocol level. Use two connections from the pool for this, one for the read, and the other for the writes. On Sun, Jun 5, 2022, 10:29 AM David G. Johnston wrote: > 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', [ >> refund ]); >> } >> > > > 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 standalo= ne > commands. I say think because I=E2=80=99m unsure why you=E2=80=99d get n= o updates instead > of updates but no transaction=E2=80=A6 > > David J. > > --000000000000e5d2e205e0b44b1a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Correct, he is reading from the connection while writing = to it.=C2=A0 Unless all the rows have been read, you can't use it to do= a write, at the protocol level.=C2=A0 Use two connections from the pool fo= r this, one for the read, and the other for the writes.

On Sun, Jun 5, 2022,= 10:29 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, June 5, 2022, Hui Jackson <jackhts4@gmail.com&g= t; wrote:
I am working o= n node-postgres and there is a strange transactions.
The function aims = at locking user's wallet until the refund process=C2=A0is complete, the= n 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&= gt;{
=C2=A0 =C2=A0 = await pgPool.query('BEGIN;');
=C2=A0 =C2=A0 const users= =3D (await pgPool.query('SELECT * fro= m app_user where $1=3DANY(purchase_list);', [ itemId ])).rows;
=C2=A0 =C2=A0 for(let i =3D 0; i < users.length; i++){<= /div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 refund(users[i])
=C2=A0 =C2=A0 }
=C2=A0 =C2=A0 await pgPool.query('UPDATE i= tem_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 refund =3D (user) =3D&= gt;{
=C2=A0 =C2=A0 c= onst refund =3D 10
=C2=A0 =C2=A0 await pgPool.query('UPDATE app_user SET wallet =3D wal= let + $1', [refund = ]);
}

I think you are mis-using your pool.=C2=A0 If you want transact= ions you need checkout a connection from the pool and use it for every comm= and in the transaction.=C2=A0 The one-shot query method on the pool is mean= t for standalone commands.=C2=A0 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.

--000000000000e5d2e205e0b44b1a--