最近在一个项目中，我们遇到了一个事实，那就是有时会出现DB僵局错误。经过一些实验，我们意识到这是由于我们的交易太大，并且可以混合搜索创建和更新操作。
代码示例（在实际项目中，交易更大）：
ActiveRecord::Base.transaction do
# 1. Creating a new order
order = Order.create(user_id: current_user.id, total_price: 0)
# 2. Adding products to the order
products = Product.where(id: [1, 2, 3])
products.each do |product|
order_items = OrderItem.create(order_id: order.id, product_id: product.id, quantity: 1, price: product.price)
order.total_price += order_items.price
end
# 3. Updating user balance
user = User.find(current_user.id)
user.balance -= order.total_price
user.save!
# 4. Updating product quantity in stock
products.each do |product|
product.update(quantity: product.quantity - 1)
end
# 5. Creating a new record in user's purchase history
purchase_history = PurchaseHistory.create(user_id: current_user.id, order_id: order.id)
# 6. Sending a notification to the user about a successful purchase
message = "Thank you for your purchase! Your order №#{order.id} for the amount of #{order.total_price} rubles has been successfully placed."
Notification.create(user_id: current_user.id, message: message)
end
如果我们在交易开始之前执行查询以搜索或检索数据，那么我们将减少将表锁定在数据库中并加快事务的可能性。
# Get products that user wants to buy
products = Product.where(id: [1, 2, 3])
# Get user who is making the purchase
user = User.find(current_user.id)
# Check if user has enough balance to make the purchase
if user.balance < products.sum(:price)
# If not, display an error message
flash[:error] = "You don't have enough balance to make this purchase"
redirect_to root_path
else
ActiveRecord::Base.transaction do
# 1. Creating a new order
order = Order.create(user_id: user.id, total_price: 0)
# 2. Adding products to the order
products.each do |product|
order_items = OrderItem.create(order_id: order.id, product_id: product.id, quantity: 1, price: product.price)
order.total_price += order_items.price
end
# 3. Updating user balance
user.balance -= order.total_price
user.save!
# 4. Updating product quantity in stock
products.each do |product|
product.update(quantity: product.quantity - 1)
end
# 5. Creating a new record in user's purchase history
purchase_history = PurchaseHistory.create(user_id: user.id, order_id: order.id)
# 6. Sending a notification to the user about a successful purchase
message = "Thank you for your purchase! Your order №#{order.id} for the amount of #{order.total_price} rubles has been successfully placed."
Notification.create(user_id: user.id, message: message)
# Display a success message
flash[:success] = "Your purchase has been successfully placed!"
redirect_to root_path
end
end
让我们仔细研究交易中的第四个动作。我们可以在这里应用一个技巧，并在一个请求中完成所有操作。
UPDATE products
SET quantity = CASE
WHEN id = 1 THEN 5
WHEN id = 2 THEN 6
WHEN id = 3 THEN 11
END
WHERE id IN(1,2,3)
我们可以使用以下功能生成这样的查询：
def update_products_quantities(products)
quantities = products.map do |index, quantity|
"WHEN id = #{index} THEN #{quantity}"
end.join(" ")
ActiveRecord::Base.connection.execute(
<<-SQL
UPDATE products\
SET quantity = (CASE #{quantities} END)\
WHERE id IN(#{products.map(&:first)})
SQL
)
end
现在让我们在交易中使用新功能。
# Get products that user wants to buy
products = Product.where(id: [1, 2, 3])
# Get user who is making the purchase
user = User.find(current_user.id)
# Check if user has enough balance to make the purchase
if user.balance < products.sum(:price)
# If not, display an error message
flash[:error] = "You don't have enough balance to make this purchase"
redirect_to root_path
else
ActiveRecord::Base.transaction do
# 1. Creating a new order
order = Order.create(user_id: user.id, total_price: 0)
# 2. Adding products to the order
products.each do |product|
order_items = OrderItem.create(order_id: order.id, product_id: product.id, quantity: 1, price: product.price)
order.total_price += order_items.price
end
# 3. Updating user balance
user.balance -= order.total_price
user.save!
# 4. Updating product quantity in stock
update_products_quantities(products.pluck(:id, :quantity))
# 5. Creating a new record in user's purchase history
purchase_history = PurchaseHistory.create(user_id: user.id, order_id: order.id)
# 6. Sending a notification to the user about a successful purchase
message = "Thank you for your purchase! Your order №#{order.id} for the amount of #{order.total_price} rubles has been successfully placed."
Notification.create(user_id: user.id, message: message)
# Display a success message
flash[:success] = "Your purchase has been successfully placed!"
redirect_to root_path
end
end
我相信，通过使用这些技巧，您将能够进一步改善此交易，例如预测订单的总成本。