<\/span><\/h2>\n\n\n\nFive units left on the shelf. Then two orders come in, near enough at once, each asking for four. The first request checks the stock, but hasn’t booked its four yet when the second one comes along and checks the very same balance. Both get waved through. Now the system has eight units spoken for, and the warehouse is five short.<\/p>\n\n\n\n
This is a concurrency problem, not a screen-refresh problem. The database has to prevent two transactions from claiming the same units.<\/p>\n\n\n\n
More frequent screen refreshes won’t prevent that race. The check and the reservation have to be coordinated at the database level.<\/p>\n\n\n\n
A reservation can be handled in one database operation, rather than by reading the balance first and updating it a moment later:<\/p>\n\n\n\n
UPDATE inventory\nSET reserved_quantity = reserved_quantity + 4\nWHERE product_id = 101\n\u00a0\u00a0AND warehouse_id = 3\n\u00a0\u00a0AND on_hand_quantity - reserved_quantity >= 4;<\/code><\/pre>\n\n\n\nThat last condition does the important work. If another request has already reserved the remaining stock, the update won’t match the row. The application still has to check the affected-row count; otherwise, it might report a successful reservation when nothing was changed.<\/p>\n\n\n\n
There is a catch when the reservation belongs to an order. The stock update might succeed, then the order write fail. Both changes need to live in the same transaction, so a failure doesn’t leave the database halfway through the operation.<\/p>\n\n\n\n
Things get messier when an order draws stock from more than one warehouse. Updating three balances isn’t the same as updating one: the first two writes might succeed before the third hits an error. You cannot leave the database in that half-finished state, so all the changes belong in one transaction. Locks on individual rows can keep two requests from altering the same balance simultaneously. The downside shows up when traffic picks up, because requests start queuing behind one another. With optimistic concurrency control, the application checks for a clash when it tries to save the change. A conflict means another attempt, or an error for the caller. If the same rows are being fought over repeatedly, those retries can become a problem of their own.<\/p>\n\n\n\n
Indexes tend to outlive the queries they were added for. Someone adds one to speed up a balance lookup, then another for a report, and both stick around as the table grows. A composite index on product_id and warehouse_id is useful when stock is looked up by that combination. The timestamp index is less of a given; it earns its keep only if the queries make use of it. Each stock movement also means index entries have to be maintained, adding to the work on a table that’s already busy with writes. Before adding another index, inspect the execution plan and see where the query actually spends its time. Otherwise, you may be carrying extra index overhead for little gain.<\/p>\n\n\n\n
<\/span>Getting changes onto the screen<\/span><\/h2>\n\n\n\nThe database can be up to date while the page isn’t. A reservation commits successfully, but a warehouse employee still sees the previous quantity on screen. If the employee picks against that figure, the interface has become a liability even though the database itself is correct.<\/p>\n\n\n\n
Committing the stock change doesn’t mean every part of the application knows about it yet. An event can carry the news to a dashboard, a cache, or the service handling orders, each of which may need to do something different with it. WebSockets and server-sent events can keep a browser supplied with fresh figures, but the connection between server and screen is another place where things go sideways. A laptop goes offline; a message arrives after a newer one; the connection drops before the update gets through. The screen can end up showing yesterday’s truth, so to speak, even if the database is already up to date. That stale figure must never decide whether an order gets reserved. The application has to check the live inventory record and apply the reservation rules when the request comes in.<\/p>\n\n\n\n
There is a failure case that deserves attention. The database commits, then the application crashes before publishing the event. The stock change is saved, but other components never hear about it.<\/p>\n\n\n\n
The transactional outbox pattern deals with that gap by recording the event in an outbox table as part of the same transaction as the inventory change. A worker picks up pending events and publishes them. If the worker fails and retries, an event may be delivered more than once, so consumers should use idempotent processing or deduplication based on stable event IDs. Without that safeguard, a repeated event can trigger the same business action twice.<\/p>\n\n\n\n
There is another detail worth keeping in mind when designing the event payload. The receiving service usually needs the product and location identifiers, an event ID, and enough information to understand what changed. Sending over a whole customer record or unrelated pricing details just creates extra exposure and more data to maintain.<\/p>\n\n\n\n
WebSockets and server-sent events can provide real-time updates to browser interfaces. They don’t, however, decide whether an order can be fulfilled. A screen is a view of inventory, not the authority on its availability. Before confirming a reservation, the application must check the current authoritative state and enforce the relevant business rules.<\/p>\n\n\n\n
<\/span>Data types and constraints<\/span><\/h2>\n\n\n\nThe server’s timestamp isn’t always the one the business cares about. A delivery might be scanned at 09:15, then sit on an offline handheld until the device reconnects at 11:40. Both times are correct, but they describe different things. If the database keeps only the upload time, the original scan time is gone. Give the two timestamps separate columns instead of making one do double duty. UTC is usually the least troublesome choice for storing them consistently. Warehouse operations still run on local time, though: shift handovers, end-of-day counts and cutoff rules may depend on the clock on the wall, not the one in the server room.<\/p>\n\n\n\n
There are other ways for bad data to slip in. An imported SKU duplicates an existing one. A balance turns up for a warehouse that was never registered. A stock correction produces a negative quantity, although that operation shouldn’t permit one. The database can catch these cases with unique constraints, foreign keys, and CHECK constraints. It’s worth doing even when the application already validates input: imports and background jobs don’t necessarily pass through the same checks as a form used by warehouse staff.<\/p>\n\n\n\n
Schema changes have a habit of turning up old assumptions. A new constraint might fail on records that have been sitting in the database for years. Change a quantity column’s numeric type and some report, integration, or bit of application code may stop handling it as expected. So the migration needs more than a quick check that the SQL runs: inspect the existing data, find the rows that won’t fit, and work out how to deal with them before deployment. There’s another wrinkle when releases overlap. If the old application is still running while the new version rolls out, both versions need to cope with the schema during that window.<\/p>\n\n\n\n
The awkward part is changing these decisions after the tables have filled up. Add a constraint and the migration may stop because existing rows violate it. Change a quantity column and an integration may fail because it expects the old type. Before deploying, inspect the actual data and decide how to handle records that don’t fit the new rules. A migration also needs to account for the application versions running during the rollout; not every service will necessarily switch over at once.<\/p>\n\n\n\n
<\/span>Test what happens when things go wrong<\/span><\/h2>\n\n\n\nCreating the tables and getting a few successful queries back proves very little. Try reserving the same last units from two sessions. Cancel an order after its stock has been allocated. Receive only part of a purchase order, transfer goods between warehouses, then process a return against a closed order. These aren’t exotic edge cases for an inventory application; they’re ordinary operations that tend to expose gaps in the model.<\/p>\n\n\n\n
After a test fails, the database is worth a look before anything else. Maybe the order row is there, but the reservation never got written. Or the balance changed and no movement explains why. Compare the records rather than relying on the test result alone. If several writes belong to one operation, a failure halfway through shouldn’t leave the successful ones sitting there on their own.<\/p>\n\n\n\n
A few hundred products won’t put much strain on a database. Millions of movement records might. Test with enough data to make the usual queries work for their living, and look at the execution plans to see where the time goes. Read speed is only part of it. An index can make a report quicker, yet every incoming stock movement has to maintain that index too. Under a busy write load, the extra work adds up. Check both before deciding the index is worth keeping.<\/p>\n\n\n\n
Migrations can break a working system in quieter ways. Suppose a new uniqueness rule is introduced after duplicate product-location records have accumulated. The constraint won’t simply tidy them up; it may fail to apply at all. The records need to be reconciled first, with a clear decision about which quantity is correct. For changes to live tables, plan the rollout so that the old and new application versions can coexist for as long as deployment requires.<\/p>\n\n\n\n
A schema can look fine on paper and still leave awkward questions once real orders start coming through. Two customers want the last few units. A return comes in weeks after the sale. Someone changes a balance, but the movement records don’t explain the difference. These aren’t edge cases the database can safely leave for staff to sort out later. The tables and constraints need to make each change traceable and prevent competing operations from quietly overwriting one another.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
A warehouse can have 40 units of a product on its shelves while the application says 43 are available. Perhaps a return was recorded twice. Perhaps an order reserved stock without updating the balance correctly. Or two requests reached the database almost simultaneously, and both were approved. Reliable inventory tracking starts with separating product details […]<\/p>\n","protected":false},"author":1,"featured_media":2865,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_sitemap_exclude":false,"_sitemap_priority":"","_sitemap_frequency":"","footnotes":""},"categories":[2],"tags":[],"class_list":["post-2864","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts\/2864","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/comments?post=2864"}],"version-history":[{"count":1,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts\/2864\/revisions"}],"predecessor-version":[{"id":2866,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts\/2864\/revisions\/2866"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/media\/2865"}],"wp:attachment":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/media?parent=2864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/categories?post=2864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/tags?post=2864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}