7 ms·
Why we spent the last month eliminating Postgres substransactions
- redis_mlc 5y agoI read the article and have the following two comments: 1) The troubleshooting and graphs are very nice. 2) The savepoint logic is, and was, absolute trash. The correct logic if you're doing an insert in SQL is to try the insert and trap the status, not whatever was shown in that post (SELECT before INSERT is a race condition.) It appears an application programmer is confusing application state transactions with simple SQL logic, or there's some obfuscation being caused by RoR. I would never +1 code like that. IOW, you 100% deserved the intermittent system degradation. Source: DBA.
- syspec 5y agoExcellently written article!
- Diggsey 5y agoThe motivating example for this seems to be completely broken: --- Start a transaction BEGIN SAVEPOINT active_record_1 --- Look up the account SELECT * FROM credit_accounts WHERE customer_id = 1 --- Insert the account; this may fail due to a duplicate constraint INSERT INTO credit_accounts (customer_id) VALUES (1) --- Abort this by rolling back ROLLBACK TO active_record_1 --- Retry here: Start a new subtransaction SAVEPOINT active_record_2 --- Find the newly-created account SELECT * FROM credit_accounts WHERE customer_id = 1 --- Save the data RELEASE SAVEPOINT active_record_2 COMMIT The second SELECT will never see the newly added row because it's still operating against the same snapshot as the outer transaction, so the use of subtransactions is completely pointless?