(second transaction’s SELECT returns 200) SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE (gets stuck and waits for transaction 1) SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE (returns 300) Then the first transaction would continue, but the SELECT would return 200 instead of 300, so the correct value would be produced. In the example above and in PostgreSQL’s default transaction isolation level the second SELECT would simply not return until the first transaction ran its UPDATE then COMMITed. FOR UPDATE (or FOR SHARE) it will pause until the transaction that holds the lock rolls back or commits. Any other transaction that tries to UPDATE the row or SELECT. Instead of SELECT balance FROM accounts WHERE user_id = 1 write SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE. The simplest solution to fix an existing broken application with the fewest changes is generally to add row level locking. See the discussion of SERIALIZABLE below, and the documentation. Note that in the non-default SERIALIZABLE isolation it prevents the error, but does so differently. This method is usually the simplest and fastest choice where it’s applicable. It isn’t useful if the application needs to do some complex logic based on the current balance to determine if the update should proceed, for example. This option is only viable for simpler cases, though. The transaction isolation documentation covers this in more detail under READ COMMITTED. This works even if the two statements are in concurrent transactions, because the first takes a lock on the row and the second waits on the lock until the first commits or rolls back. UPDATE accounts SET balance = balance - 100 WHERE user_id = 1 (sets balance=100) UPDATE accounts SET balance = balance - 100 WHERE user_id = 1 (sets balance=200) The best solution is often to just do the work in SQL, avoiding the read-modify-write-cycle entirely. Optimistic concurrency control, otherwise known as optimistic locking.Avoiding the read-modify-write with a calculated update.Some popular solutions to this problem are: Thankfully PostgreSQL (and SQL in general) has a few tools that will help you, and there are some application side options too. In a transaction the outcome in this case is exactly the same: Session 1 The only way to let you completely ignore concurrency issues is to LOCK TABLE every table you might use before starting the transaction (and even then you have to always lock in the same order to prevent deadlocks). 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |