Menu

Orders stall only during sales: work done while holding a SELECT FOR UPDATE lock
📰

Orders stall only during sales: work done while holding a SELECT FOR UPDATE lock

DEV Community: postgres·Kazu·12 days ago
#C5OFiGyQ
#devtopostgresql#devto#dev#stock#orders
Reading 0:00
15s threshold

If you keep stock as one row per item and every order takes that row with SELECT ... FOR UPDATE before updating it, there is a ceiling on how many orders for the same item can complete per second. That is because the row lock is not released until COMMIT , and other orders that come for the same row wait on the lock in the meantime. 1 Call the time from when SELECT ... FOR UPDATE returns with the lock until COMMIT the hold time. The ceiling is then 1 ÷ hold time per second, which is 10 per second for a hold time of 0.1s. The hold time includes everything the application server does while holding the lock, such as calls to external APIs.

The excess orders pile up unfinished only during periods like a sale, when orders for the same item arrive faster than this ceiling, and response time for that item's orders keeps growing. An order waiting for the lock holds its connection while it waits, so other requests on the same connection pool also slow down while they wait for a free connection.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.