Subject: Strange behavior of transcations

From: Hui Jackson <[email-masked]>
Date: Sun, 5 Jun 2022 19:10:01 +0800

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
]);
}

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 5 Jun 2022 07:29:27 -0700

On Sunday, June 5, 2022, Hui Jackson <[email-masked]> wrote:

> 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.

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.

---

From: Erik Brandsberg <[email-masked]>
Date: Sun, 5 Jun 2022 10:37:23 -0400

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-masked]>
wrote:

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