11–20 of 60 posts
Thats not postgresql problem, thats your code IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js
Earlier quoted context omitted.
Here's a real-world example where atomic updates aren't an option - an order status transition that reads the current status from one table, validates the transition, and inserts into another: await db().transaction(async (tx) => { await hooks?.onTxBegin?.(); const [order] = await tx.select().from(orders) .where(eq(orders.id, input.id)) .for("update"); const [status] = await tx.select().from(orderStatuses) .where(eq(…
The standard pattern to avoid select for update (which can cause poor performance under load) is to use optimistic concurrency control. Add a numeric version column to the table being updated, read and increment it in the application layer and use the value you saw as part of the where clause in the update statement. If you see ‘0 rows updated’ it means you were beaten in a race and should replay the operation.
Optimistic updates looks great when there is no contention, and they will beat locking in a toy benchmark, but if you're not very careful they can cause insane amplification under load.
It's a similar trap as spinlocks. People keep re-discovering this great performance hack that avoids the slow locks in the standard. And some day the system has a spike that creates contention, and now you have 25 instances with 24 of them spinning like crazy, slowing to a crawl the only one that could be making progress.
It's possible to implement this pattern correctly, and it can be better in some specific situations. But a standard FOR UPDATE lock will beat the average badly implemented retry loop nine times out of ten.
[dead]
And if for some reason you refuse to, then this "barrier" or "hooks" approach to testing will in practice not help. It requires you to already know the potential race conditions, but if you are already aware of them then you will already write your code to avoid them. It is the non-obvious race conditions that should scare you.
To find these, you should use randomized testing that runs many iterations of different interleavings of transaction steps. You can build such a framework that will hook directly into your individual DB query calls. Then you don't have to add any "hooks" at all.
But even that won't find all race condition bugs, because it is possible to have race conditions surface even within a single database query.
You really should just use SERIALIZABLE and save yourself all the hassle and effort and spending hours writing all these tests.
Earlier quoted context omitted.
Here's a real-world example where atomic updates aren't an option - an order status transition that reads the current status from one table, validates the transition, and inserts into another: await db().transaction(async (tx) => { await hooks?.onTxBegin?.(); const [order] = await tx.select().from(orders) .where(eq(orders.id, input.id)) .for("update"); const [status] = await tx.select().from(orderStatuses) .where(eq(…
Seems you could use a single SQL statement for that particular formulation. Something like this, using CTEs is possible, but alternately one can reformat them as subqueries. (note: not sure how the select of orders is intended to be used, so the below doesn't use it, but it does obtain it as an expression to be used) WITH o AS ( SELECT FROM orders WHERE orders.id = $1 ), os AS ( SELECT FROM orderStatuses WHERE orderS…
Postgres has SERIALIZABLE transaction isolation level. Just use it and then you never have to worry about any of these race conditions. And if for some reason you refuse to, then this "barrier" or "hooks" approach to testing will in practice not help. It requires you to already know the potential race conditions, but if you are already aware of them then you will already write your code to avoid them. It is the non-o…
Thats not postgresql problem, thats your code IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js
There's essentially no difference between putting the logic in the app vs a stored procedure (other than round trip time)
[dead]
Use transactions table (just a name, like orders)
On it have an Insert trigger.
It should make a single update with simple “update … set balance += amount where accoundId = id”. This will be atomic thanks to db engine itself.
Also add check constraint >= 0 for balance so it would never become negative even if you have thousands of simultaneous payments. If it becomes negative, it will throw, insert trigger will rethrow, no insert will happen, your backend code will catch it.
—
That’s it: insert-trigger and check constraint.
No need for explicit locking, no stored procedures, no locks in you backend also, nada. Just a simple insert row. No matter the load and concurrent users it will work like magic. Blazingly fast too.
That’s why there is ACID in DBs.
—
Shameless plug: learn your tool. Don’t approach Postgresql/Mssql/whathaveyousql like you’re a backend engineer. DB is not a txt file.