public inbox for [email protected]
help / color / mirror / Atom feedStrange behavior of transcations
3+ messages / 3 participants
[nested] [flat]
* Strange behavior of transcations
@ 2022-06-05 11:10 Hui Jackson <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Hui Jackson @ 2022-06-05 11:10 UTC (permalink / raw)
To: [email protected]
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
]);
}
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange behavior of transcations
@ 2022-06-05 14:29 David G. Johnston <[email protected]>
parent: Hui Jackson <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2022-06-05 14:29 UTC (permalink / raw)
To: Hui Jackson <[email protected]>; +Cc: [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange behavior of transcations
@ 2022-06-05 14:37 Erik Brandsberg <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Erik Brandsberg @ 2022-06-05 14:37 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Hui Jackson <[email protected]>; pgsql-sql <[email protected]>
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.
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-06-05 14:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-05 11:10 Strange behavior of transcations Hui Jackson <[email protected]>
2022-06-05 14:29 ` David G. Johnston <[email protected]>
2022-06-05 14:37 ` Erik Brandsberg <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox