Обсуждение: Strange behavior of transcations
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.
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 ]);
}
On Sunday, June 5, 2022, Hui Jackson <jackhts4@gmail.com> 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 = 10await 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.
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 <david.g.johnston@gmail.com> wrote:
On Sunday, June 5, 2022, Hui Jackson <jackhts4@gmail.com> 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 = 10await 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.