![]() ![]() Unfortunately, while great, transactions aren’t magic secret sauce you can add for easy concurrency. I often have people on Stack Overflow ask things to the tune of “Don’t transactions prevent this?”. Then run the commands in each session column in separate psql sessions by using two terminal windows **. INSERT INTO accounts(user_id, balance) VALUES (1, 300) If you want to try this or any of the other examples in this article, just run the following setup code: CREATE TABLE accounts (user_id integer primary key, balance integer not null) It’s important to know about it so you can code defensively. Most testing and development is done on standalone servers running single sessions, so unless you’re doing rigorous testing this sort of thing often doesn’t get noticed until production, and can be painful to debug. The balance is 200, but you took out 200 from a starting point of 300. UPDATE balance SET balance = 200 WHERE user_id = 1 (300 – 100 = 200) SELECT balance FROM accounts WHERE user_id = 1 (also returns 300) SELECT balance FROM accounts WHERE user_id = 1 (returns 300) Imagine two concurrent sessions, each subtracting 100 from the user’s balance, starting with an initial value of 300. However, this code is critically wrong, and will malfunction as soon as the same user is updated by two different sessions at the same time. UPDATE accounts SET balance = ? WHERE user_id =1 Īnd everything will appear to work fine to the developer. in the application, subtract 100 from balance if it's above It’s common to see this written as three steps: SELECT balance FROM accounts WHERE user_id = 1 Imagine your code wants to look up a user’s balance, subtract 100 from it if doing so won’t make it negative, and save it. Here I’ll explain what this common development mistake is, how to identify it, and options for how to fix it. It’s reminded me of another SQL coding anti-pattern that I see quite a lot: the naïve read-modify-write cycle. Shaun Thomas’s recent post about client-side loops as an SQL anti-pattern is well worth a read if you’re relatively new to SQL-based application development. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition. ![]() PostgreSQL High Availability Cookbook – 2nd Edition.
0 Comments
Leave a Reply. |