MIME-Version: 1.0
From: Hui Jackson <jackhts4@gmail.com>
Date: Sun, 5 Jun 2022 19:10:01 +0800
Message-ID: 
 <CAHXAyjyT2zDt9Ozq-VnM80x7f3tm0yJt3uUaWCkR8bfLvfLwJA@mail.gmail.com>
Subject: Strange behavior of transcations
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003be49305e0b1663f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHXAyjyT2zDt9Ozq-VnM80x7f3tm0yJt3uUaWCkR8bfLvfLwJA%40mail.gmail.com>
Precedence: bulk

--0000000000003be49305e0b1663f
Content-Type: text/plain; charset="UTF-8"

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 = (itemId) =>{
    await pgPool.query('BEGIN;');
    const users = (await pgPool.query('SELECT * from app_user where
$1=ANY(purchase_list);', [ itemId ])).rows;
    for(let i = 0; i < users.length; i++){
        refund(users[i])
    }
    await pgPool.query('UPDATE item_lists SET has_refund = $1 where id =
$2;', [true,  itemId ]);
    await pgPool.query('COMMIT;');
}

const refund = (user) =>{
    const refund = 10
    await pgPool.query('UPDATE app_user SET wallet = wallet + $1', [refund
]);
}

--0000000000003be49305e0b1663f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I am working on node-postgres and there is a strange trans=
actions.<div>The function aims at locking user&#39;s wallet until the refun=
d process=C2=A0is complete, then will update item&#39;s has_refund to true.=
<br>The problem I am facing is the program return no error, but the databas=
e is not updated. Unless I spam the function for multiple times then the va=
lue is updated.<br>If I remove begin and commit, then the function work per=
fectly.<br><div style=3D"color:rgb(212,212,212);background-color:rgb(30,30,=
30);font-family:Consolas,&quot;Courier New&quot;,monospace;font-size:14px;l=
ine-height:19px;white-space:pre"><div><span style=3D"color:rgb(86,156,214)"=
>const</span> <span style=3D"color:rgb(220,220,170)">refundService</span> =
=3D (<span style=3D"color:rgb(156,220,254)">itemId</span>) <span style=3D"c=
olor:rgb(86,156,214)">=3D&gt;</span>{</div><div>=C2=A0 =C2=A0 <span style=
=3D"color:rgb(197,134,192)">await</span> <span style=3D"color:rgb(79,193,25=
5)">pgPool</span>.<span style=3D"color:rgb(220,220,170)">query</span>(<span=
 style=3D"color:rgb(206,145,120)">&#39;BEGIN;&#39;</span>);</div><div>=C2=
=A0 =C2=A0 <span style=3D"color:rgb(86,156,214)">const</span> <span style=
=3D"color:rgb(79,193,255)">users</span> =3D (<span style=3D"color:rgb(197,1=
34,192)">await</span> <span style=3D"color:rgb(79,193,255)">pgPool</span>.<=
span style=3D"color:rgb(220,220,170)">query</span>(<span style=3D"color:rgb=
(206,145,120)">&#39;SELECT * from app_user where $1=3DANY(purchase_list);&#=
39;</span>, [ <span style=3D"color:rgb(156,220,254)">itemId</span> ])).<spa=
n style=3D"color:rgb(156,220,254)">rows</span>;</div><div>=C2=A0 =C2=A0 <sp=
an style=3D"color:rgb(197,134,192)">for</span>(<span style=3D"color:rgb(86,=
156,214)">let</span> <span style=3D"color:rgb(156,220,254)">i</span> =3D <s=
pan style=3D"color:rgb(181,206,168)">0</span>; <span style=3D"color:rgb(156=
,220,254)">i</span> &lt; <span style=3D"color:rgb(79,193,255)">users</span>=
.<span style=3D"color:rgb(156,220,254)">length</span>; <span style=3D"color=
:rgb(156,220,254)">i</span>++){</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 <span=
 style=3D"color:rgb(220,220,170)">refund</span>(<span style=3D"color:rgb(79=
,193,255)">users</span>[<span style=3D"color:rgb(156,220,254)">i</span>])</=
div><div>=C2=A0 =C2=A0 }</div><div>=C2=A0 =C2=A0 <span style=3D"color:rgb(1=
97,134,192)">await</span> <span style=3D"color:rgb(79,193,255)">pgPool</spa=
n>.<span style=3D"color:rgb(220,220,170)">query</span>(<span style=3D"color=
:rgb(206,145,120)">&#39;UPDATE item_lists SET has_refund =3D $1 where id =
=3D $2;&#39;</span>, [<span style=3D"color:rgb(86,156,214)">true</span>, =
=C2=A0<span style=3D"color:rgb(156,220,254)">itemId</span> ]);</div><div>=
=C2=A0 =C2=A0 <span style=3D"color:rgb(197,134,192)">await</span> <span sty=
le=3D"color:rgb(79,193,255)">pgPool</span>.<span style=3D"color:rgb(220,220=
,170)">query</span>(<span style=3D"color:rgb(206,145,120)">&#39;COMMIT;&#39=
;</span>);</div><div>}</div><div>=C2=A0 =C2=A0 </div><div><span style=3D"co=
lor:rgb(86,156,214)">const</span> <span style=3D"color:rgb(220,220,170)">re=
fund</span> =3D (<span style=3D"color:rgb(156,220,254)">user</span>) <span =
style=3D"color:rgb(86,156,214)">=3D&gt;</span>{</div><div>=C2=A0 =C2=A0 <sp=
an style=3D"color:rgb(86,156,214)">const</span> <span style=3D"color:rgb(79=
,193,255)">refund</span> =3D <span style=3D"color:rgb(181,206,168)">10</spa=
n></div><div>=C2=A0 =C2=A0 <span style=3D"color:rgb(197,134,192)">await</sp=
an> <span style=3D"color:rgb(79,193,255)">pgPool</span>.<span style=3D"colo=
r:rgb(220,220,170)">query</span>(<span style=3D"color:rgb(206,145,120)">=
9;UPDATE app_user SET wallet =3D wallet + $1&#39;</span>, [<span style=3D"c=
olor:rgb(79,193,255)">refund</span> ]);</div><div>}</div></div></div></div>

--0000000000003be49305e0b1663f--