How Python solves the problem of inventory under high concurrency __python

A simple use of the scene: a product inventory of only 5 pieces, while a user bought 5, b users bought 5, are submitted data, as the problem of inadequate inventory.

Logic: According to the General electrical business model class, the order generally includes order and Order Details class (Detailorder), the two tables are related to the foreign key order_id, so is the relationship between the die and die, so we use the transaction to control. So how does python solve the inventory problem?

Python provides 2 ways to solve the problem: 1, pessimistic lock; 2, optimistic lock

Pessimistic Lock: Lock select_for_update when querying the store () when a commit of a transaction or a rollback of a transaction occurs, the lock is automatically released so that other users can then query the product.

Optimistic Lock: Optimistic lock is not a real lock, before creating an order, check the inventory of the merchandise before creating the Order Details table, update the query data, if two times the inventory of the query to create a table of details, and less inventory, otherwise, the cycle three times, if it is different, the occurrence of rollback.

Usage Scenario: Use pessimistic lock when concurrent volume is high, disadvantage: lock consumes resources

Use optimistic locks when concurrency is low, disadvantage: optimistic lock cycle time consuming.

Code:

Pessimistic Lock:

@transaction. Atomic
Def post (self, request):
"" Accept data Modify shopping cart Data add order and order items plus pessimistic lock to resolve concurrency problem "" "
Sku_ids = Request. Post.get (' Sku_ids ')
addr_id =request. Post.get (' addr_id ')
Pay_method = Request. Post.get (' Pay_method ')
Transit_price = Request. Post.get (' Transit_price ')
user = Request.user
If not user.is_authenticated (): # To determine whether to log in
Return Jsonresponse ({' res ': 0, ' errmsg ': ' User not logged in, please login '})
Try: # To determine if an address exists
Address = Customer.objects.get (id=addr_id)
Except Customer.doesnotexist:
Return Jsonresponse ({' res ': 1, ' errmsg ': ' Address Not present '})

# to determine whether the payment method exists
If Pay_method not in OrderInfo.PAY_METHODS.keys ():
Return Jsonresponse ({' res ': 2, ' errmsg ': ' payment method does not exist '})
Total_price = 0
Total_count = 0
order_id = DateTime.Now (). Strftime ('%y%m%d%h%m%s ') + str (user.id)
# SET TRANSACTION Save point
tip = Transaction.savepoint ()
Try
New_order =orderinfo.objects.create (order_id=order_id,
User=user,
Addr=address,
Pay_method=pay_method,
Total_count=total_count,
Total_price=total_price,
Transit_price=transit_price)
Except Exception as E:
# Roll Back to Tip
Transaction.savepoint_rollback (TIP)
Return Jsonresponse ({' res ': 3, ' errmsg ': ' Generate Order failed '})
Cart_key = ' cart_%d '% user.id
conn = get_redis_connection (' default ')
SKUs = Conn.hgetall (Cart_key)
For sku_id in Eval (sku_ids): # Loop to get the product information each data generates an order merchandise data
# get the number of each item in the shopping cart
Sku_count = Conn.hget (Cart_key, sku_id)
If not sku_count: # To determine if there is a product in the cart
# Roll Back to Tip
Transaction.savepoint_rollback (TIP)
Return Jsonresponse ({' res ': 4, ' errmsg ': ' The item is not in the shopping Cart '})
Try: # Find the SEL in the merchandise list
SKU = GoodsSKU.objects.select_for_update (). Get (id=sku_id, is_show=1)
Except Goodssku.doesnotexist:
# Roll Back to Tip
Transaction.savepoint_rollback (TIP)
Return Jsonresponse ({' res ': 5, ' errmsg ': ' The product has been shelves '})
Price = Sku.price
sku_count = Int (sku_count)
If sku_count > Int (sku.count): # Judging Inventory
# Roll Back to Tip
Transaction.savepoint_rollback (TIP)
Return Jsonresponse ({' res ': 6, ' errmsg ': ' Insufficient stock '})
Price = Int (price)
Sku_price = Sku_count * Price
Total_price + + Sku_price
Total_count + + Sku_count
# Add Order Merchandise table
Try
OrderGoods.objects.create (Order=new_order,
Sku=sku,
Count=sku_count,
Price=sku_price,
)
Except Exception as E:
# Roll Back to Tip
Transaction.savepoint_rollback (TIP)
Return Jsonresponse ({' res ': 7, ' errmsg ': ' Order item creation failed '})
# Reduce commodity inventories and increase sales
Sku.count-= Sku_count
Sku.sale_count + + Sku_count
Sku.save ()
# Delete Modify Shopping cart data
Conn.hdel (Cart_key, sku_id)
Total_price + = Int (transit_price)
# Modify order Quantity total postage
New_order.total_count = Total_count
New_order.total_price = Total_price
New_order.transit_price = Transit_price
New_order.save ()
#释放保存点

Transaction.savepoint_commit (TIP)

Optimistic Lock:

@transaction. Atomic
Def post (self, request):
"' Order creation '"
# Determine if the user is logged in
user = Request.user
If not user.is_authenticated ():
Return Jsonresponse ({' res ': 0, ' errmsg ': ' User not logged in '})
# Receive Parameters
addr_id = Request. Post.get (' addr_id ')
Pay_method = Request. Post.get (' Pay_method ')
Sku_ids = Request. Post.get (' Sku_ids ') # 1,4
# Check Parameters
If not all ([addr_id, Pay_method, Sku_ids]):
Return Jsonresponse ({' res ': 1, ' errmsg ': ' Incomplete Data '})
# Verify Address
Try
Addr = Address.objects.get (id=addr_id)
Except Address.doesnotexist:
# address does not exist
Return Jsonresponse ({' res ': 2, ' errmsg ': ' Address information error '})
# Check Payment method
If Pay_method not in OrderInfo.PAY_METHODS.keys ():
# payment method is invalid
Return Jsonresponse ({' res ': 3, ' errmsg ': ' Invalid Payment method '})
# Business process: order creation
# Organization Parameters
# Order ID (order_id): 20171211123130+ ID of the user
order_id = DateTime.Now (). Strftime ('%y%m%d%h%m%s ') +str (user.id)
# freight
Transit_price = 10
# Total number and total amount
Total_count = 0
Total_price = 0
# Set Save Point
Sid = Transaction.savepoint ()
Try
# TODO: Add a record to the Df_order_info table
Order = OrderInfo.objects.create (order_id=order_id,
User=user,
ADDR=ADDR,
Pay_method=pay_method,
Total_count=total_count,
Total_price=total_price,
Transit_price=transit_price)
conn = get_redis_connection (' default ')
Cart_key = ' cart_%d '%user.id
# TODO: When a user's order contains several items, a few records should be added to the Df_order_goods
Sku_ids = Sku_ids.split (', ') # [1,4]
For sku_id in Sku_ids:
For I in range (3):
# according to SKU_ID to get information about the goods
Try
# select * from Df_goods_sku where id=sku_id;
SKU = GoodsSKU.objects.get (id=sku_id)
Except Goodssku.doesnotexist:
Transaction.savepoint_rollback (SID)
Return Jsonresponse ({' res ': 4, ' errmsg ': ' Commodity Information error '})
# get the number of items the user wants to buy
Count = Conn.hget (Cart_key, sku_id)
# TODO: Judging merchandise inventory
if int (count) > Sku.stock:
Transaction.savepoint_rollback (SID)
Return Jsonresponse ({' res ': 6, ' errmsg ': ' Insufficient stock of goods '})
# TODO: Reduce inventory of goods and increase sales
Origin_stock = Sku.stock
New_stock = Origin_stock-int (count)
New_sales = sku.sales + int (count)
# print (' user:%d i:%d stock:%d '% (user.id, I, Origin_stock))
# import Time
# Time.sleep (10)
# returns the number of rows updated
# update Df_goods_sku set Stock=new_stock, Sales=new_sales
# where id=sku_id and Stock=origin_stock
res = GoodsSKU.objects.filter (id=sku_id, Stock=origin_stock). Update (Stock=new_stock, Sales=new_sales)
If res = 0:
# Update failed
if i = = 2:
# has tried 3 times, the next single failed
Transaction.savepoint_rollback (SID)
Return Jsonresponse ({' res ': 7, ' errmsg ': ' Next single failed 2 '})
Continue
# TODO: Add a record to the Df_order_goods
OrderGoods.objects.create (Order=order,
Sku=sku,
Count=count,
Price=sku.price)
# TODO: Total number of items and total amount calculated
total_count = Int (count)
Total_price + = Sku.price*int (count)
# jump out of loop after successful update
Break
# TODO: Update order of Total_count and Total_price
Order.total_count = Total_count
Order.total_price = Total_price
Order.save ()
Except Exception as E:
Transaction.savepoint_rollback (SID)
Return Jsonresponse ({' res ': 7, ' errmsg ': ' Next single failure '})
# Release Save Point
Transaction.savepoint_commit (SID)
# TODO: delete cart corresponding record information
Conn.hdel (Cart_key, *sku_ids) # 1,4
# return Answer
Return Jsonresponse ({' res ': 5, ' message ': ' Order creation succeeded '})

Tip: When using optimistic locks, remember to modify the isolation level of MySQL: vi/etc/mysql/mysql.conf.d/mysqld.cnf in mysqld the bottom line: transaction-isolation = Read_ Committed.