Hi all,
I was wondering if anyone here would be able to offer some insight to a problem I am experiencing.
I am helping work on a game which has a player-based market system. Each player has a store, and that store contains a listing of their items (obviously). This is a basic run-down of how items are stored in the database:
table item_details
id
name
description
This table holds the main information for an item. Example:
323, 'Red Scarf', 'This is a red scarf.'
table user_shop_stock
user_id
item_id
quantity (unsigned)
price
This table holds the information for an individual player's shop. Example:
84, 323, 12, 100
Player #84 has 12 Red Scarves for sale for 100 monies.
Here is where the problem comes in. When player #84 only has 1 Red Scarf in stock and two players attempt to buy it at the same time, they both pass the original quantity check (item must be above 0 quantity). The first sale goes through, dropping the quantity to 0. Since the second sale has already checked the quantity, it also goes through.
Being an unsigned field, the quantity then rolls around to 16777215 -- which as you can imagine has a very negative impact on the game's economy.
The temporary solution was to disable the use of items with a quantity higher than 16,000,000 (the way the game is set up it would be impossible to get that many of any item). Unfortunately, it does not stop one person from basically generating an item that does not exist. Especially problematic with rare items.
Aside from storing every Red Scarf as individual objects instead of the current setup, does anyone have any recommendations or advice?
Many thanks in advance. 🙂
