I intend to write a SQL transaction to insert an order if a product has enough inventory (make sure no two customers both success to buy a product with only 1 inventory at the same time), like this:
START TRANSACTION;
UPDATE products SET inventory=inventory-1 WHERE product_id=1 AND inventory>=1;
if (update succeeded) INSERT order ...;
COMMIT;
How can I check whether the products' inventory update succeeded. Do I have to use the procedure?
You can implement an after-update trigger that inserts an order. You can also select the products.product_id into a variable and check whether that's greater than 0. Of course, this variable approach only works if there is a single products record to be updated. If there are plural such records, then you can use a cursor instead of a variable. I believe that the simplest approach is to implement a trigger, so you will have a single visible operation in the transaction and the insertion would be performed by the trigger under the hood.