public inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Brandsberg <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Hui Jackson <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Strange behavior of transcations
Date: Sun, 5 Jun 2022 10:37:23 -0400
Message-ID: <CAFcck8H4OTcQtf0W9p9cyWdrutjsDe3tMiwYzOe1=fNB3D=Zkg@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwZ7fu2sZh25i-8LMWRTW4Trw2oGEXaRaVX9n9nePGnpeQ@mail.gmail.com>
References: <CAHXAyjyT2zDt9Ozq-VnM80x7f3tm0yJt3uUaWCkR8bfLvfLwJA@mail.gmail.com>
<CAKFQuwZ7fu2sZh25i-8LMWRTW4Trw2oGEXaRaVX9n9nePGnpeQ@mail.gmail.com>
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 <[email protected]>
wrote:
> On Sunday, June 5, 2022, Hui Jackson <[email protected]> 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 = (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 ]);
>> }
>>
>
>
> 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’m unsure why you’d get no updates instead
> of updates but no transaction…
>
> David J.
>
>
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Strange behavior of transcations
In-Reply-To: <CAFcck8H4OTcQtf0W9p9cyWdrutjsDe3tMiwYzOe1=fNB3D=Zkg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox