In a web service, how to record an order and deduct stock so that either both operations are saved or none? What SQL commands are used to frame the set of queries, and how to act in case of an error before commit?

To ensure atomicity of order recording and stock deduction operations in SQL, transactions are used. The main commands for wrapping a set of queries are:

• BEGIN or START TRANSACTION — starts a transaction.
• Execute the necessary queries (e.g., INSERT order and UPDATE stock).
• COMMIT — commits the changes if all operations were successful.
• ROLLBACK — cancels all changes in the transaction if an error occurs before commit.

Example in SQL:

BEGIN;
INSERT INTO orders (...) VALUES (...);
UPDATE stock SET quantity = quantity - ? WHERE product_id = ?;
-- if no errors
COMMIT;
-- if error
ROLLBACK;

In code, upon detecting an error before calling COMMIT, ROLLBACK should be called to revert all changes and maintain data integrity.