Home Does multi-row multi update vs many updates statement works exactly the same? [PosgreSQL]
Reply: 0

Does multi-row multi update vs many updates statement works exactly the same? [PosgreSQL]

Zanko
1#
Zanko Published in 2017-09-13 21:03:08Z

I can't seem to find any resources addressing this,

UPDATE "my-table" AS t 
   SET "val"=v."val",
        "msg"=v."msg" 
FROM (
   VALUES(1,123,'hello'),
         (2,456,'world!')
) AS v("id","val","msg") 
WHERE v.id = t.id

vs

UPDATE "my-table" 
    SET val = 123, msg = 'hello' 
WHERE id = 1;

UPDATE "my-table" 
    SET val = 456, msg = 'world' 
WHERE id = 2;

What I am interested in is whether the concurrency control between each case is the same for read committed isolation level?

After reading this I feel quite safe about how it handles UPDATE concurrently https://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-READ-COMMITTED

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

but there is no mentioning about multi value update and how it behaves?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.56796 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO