Order concurrency this problem I think we all have a certain understanding, here I say some of my shallow view, I will try to let you know how to solve this kind of problem.
Before explaining how to solve the order concurrency problem, it’s important to understand what database transactions are. (I use mysql database, mysql as an example)
- The transaction concept
A set of mysql statements, either executed or not executed at all.
- Mysql transaction isolation level
Read Committed
If the version is later than Django2.0, you need to change to this isolation level; otherwise, the modified data cannot be read during optimistic lock operations
RepeatableRead
This is the default isolation level for Mysql and can be changed in the Mysql configuration file;
transcation-isolation = READ-COMMITTED
Copy the codeAdd this line to the mysql configuration file and restart mysql to change the transaction isolation level to Read Committed
I don’t want to waste time on other things that I’m not going to use here.
Pessimistic lock: start transaction and add for update to end mysql query.
What’s going on here? In case you don’t understand, adding a mutex to a resource is the same as adding a mutex to multiple threads to ensure that no other transaction can operate on the data until the end of a transaction.
The following is the code for pessimistic locking. Locking and unlocking are CPU intensive, so optimistic locking is a better choice in cases of low order concurrency.
"Have a problem and no one to answer it? We have created a Python learning QQ group: 857662006 to find like-minded friends and help each other. There are also good video tutorials and PDF e-books in the group. ' ' '
class OrderCommitView(View):
""" Pessimistic lock """
Open the transaction decorator
@transaction.atomic
def post(self,request):
""" Order concurrency ———— pessimistic lock """
Get the product ID
goods_ids = request.POST.getlist('goods_ids')
# check parameter
if len(goods_ids) == 0 :
return JsonResponse({'res':0.'errmsg':'Incomplete data'})
# Current time string
now_str = datetime.now().strftime('%Y%m%d%H%M%S')
# Order Number
order_id = now_str + str(request.user.id)
# address
pay_method = request.POST.get('pay_method')
# Payment method
address_id = request.POST.get('address_id')
try:
address = Address.objects.get(id=address_id)
except Address.DoesNotExist:
return JsonResponse({'res':1.'errmsg':'Wrong address'})
# Quantity of goods
total_count = 0
# Total commodity price
total_amount = 0
Get the Redis connection
conn = get_redis_connection('default')
# joining together the key
cart_key = 'cart_%d' % request.user.id
#
# create savepoint
sid = transaction.savepoint()
order_info = OrderInfo.objects.create(
order_id = order_id,
user = request.user,
addr = address,
pay_method = pay_method,
total_count = total_count,
total_price = total_amount
)
for goods_id in goods_ids:
# Try to query the item
# Here consider the order concurrency problem,
try:
# goods = goods.objects.get (id=goods_id) # goods.objects.get (id=goods_id) #
goods = Goods.objects.select_for_update().get(id=goods_id) # add mutex query
except Goodsgoods.DoesNotExist:
Rollback to savepoint
transaction.rollback(sid)
return JsonResponse({'res':2.'errmsg':'Product information error'})
# Take out the quantity of goods
count = conn.hget(cart_key,goods_id)
if count is None:
Rollback to savepoint
transaction.rollback(sid)
return JsonResponse({'res':3.'errmsg':'The item is not in the cart'})
count = int(count)
if goods.stock < count:
Rollback to savepoint
transaction.rollback(sid)
return JsonResponse({'res':4.'errmsg':'Understock'})
# Increased sales of goods
goods.sales += count
# Reduced inventory of goods
goods.stock -= count
Save to database
goods.save()
OrderGoods.objects.create(
order = order_info,
goods = goods,
count = count,
price = goods.price
)
# Add the number of items
total_count += count
# Add the total price of goods
total_amount += (goods.price) * count
Update the total number of items in the order information
order_info.total_count = total_count
Update the total price in the order information
order_info.total_price = total_amount + order_info.transit_price
order_info.save()
# transaction commit
transaction.commit()
return JsonResponse({'res':5.'errmsg':'Order created successfully'})
Copy the codeThen there is optimistic lock query, compared to pessimistic lock, optimistic lock is not actually called a lock, so what is it doing.
Are you asked for database operations to query a database goods inventory, and then you want to update the database in the inventory, will you a began to query the inventory quantity and commodity ID as a condition of update together, when the number of rows affected returns 0, show that no modification is successful, so no other process to modify the data, You can then roll back to the time when no database operation was performed, re-query the database, repeat the previous operation a certain number of times, and return error results if you can’t change the number of times you set.
This method applies only to cases where orders are not concurrently placed. If the number of failures is too many, users will experience bad experiences. In addition, the isolation level of the database must be set to Read Committed.
It is best to check the isolation level of the database before using optimistic locks
select @@global.tx_isolation;
Optimistic locking code
class OrderCommitView(View):
""" Optimistic Lock """
Open the transaction decorator
@transaction.atomic
def post(self,request):
""" Order concurrent ———— optimistic lock """
Get id #
goods_ids = request.POST.get('goods_ids')
if len(goods_ids) == 0 :
return JsonResponse({'res':0.'errmsg':'Incomplete data'})
# Current time string
now_str = datetime.now().strftime('%Y%m%d%H%M%S')
# Order Number
order_id = now_str + str(request.user.id)
# address
pay_method = request.POST.get('pay_method')
# Payment method
address_id = request.POST.get('address_id')
try:
address = Address.objects.get(id=address_id)
except Address.DoesNotExist:
return JsonResponse({'res':1.'errmsg':'Wrong address'})
# Quantity of goods
total_count = 0
# Total commodity price
total_amount = 0
# Order freight
transit_price = 10
# create savepoint
sid = transaction.savepoint()
order_info = OrderInfo.objects.create(
order_id = order_id,
user = request.user,
addr = address,
pay_method = pay_method,
total_count = total_count,
total_price = total_amount,
transit_price = transit_price
)
Get the Redis connection
goods = get_redis_goodsection('default')
# joining together the key
cart_key = 'cart_%d' % request.user.id
for goods_id in goods_ids:
# Try to query the item
# Here consider the order concurrency problem,
# redis fetch quantity of goods
count = goods.hget(cart_key, goods_id)
if count is None:
Rollback to savepoint
transaction.savepoint_rollback(sid)
return JsonResponse({'res': 3.'errmsg': 'The item is not in the cart'})
count = int(count)
for i in range(3) :# If there are concurrent orders, try to place the order three times
try:
goods = Goodsgoods.objects.get(id=goods_id) # query without lock
# goods = Goodsgoods. Objects. Select_for_update () get (id = goods_id) # add mutex queries
except Goodsgoods.DoesNotExist:
Rollback to savepoint
transaction.savepoint_rollback(sid)
return JsonResponse({'res':2.'errmsg':'Product information error'})
origin_stock = goods.stock
print(origin_stock, 'stock')
print(goods.id, 'id')
if origin_stock < count:
Rollback to savepoint
transaction.savepoint_rollback(sid)
return JsonResponse({'res':4.'errmsg':'Understock'})
# # Increased sales of goods
# goods.sales += count
# # Merchandise inventory reduced
# goods.stock -= count
Save to database
# goods.save()
# Inventory after successful order
new_stock = goods.stock - count
new_sales = goods.sales + count
res = Goodsgoods.objects.filter(stock=origin_stock,id=goods_id).update(stock=new_stock,sales=new_sales)
print(res)
if res == 0:
if i == 2:
# the rollback
transaction.savepoint_rollback(sid)
return JsonResponse({'res':5.'errmsg':'Order failed'})
continue
else:
break
OrderGoods.objects.create(
order = order_info,
goods = goods,
count = count,
price = goods.price
)
Delete the record from the shopping cart
goods.hdel(cart_key,goods_id)
# Add the number of items
total_count += count
# Add the total price of goods
total_amount += (goods.price) * count
Update the total number of items in the order information
order_info.total_count = total_count
Update the total price in the order information
order_info.total_price = total_amount + order_info.transit_price
order_info.save()
# transaction commit
transaction.savepoint_commit(sid)
return JsonResponse({'res':6.'errmsg':'Order created successfully'})
Copy the code