Use savepoint in a business transaction

Use savepoint in a business transaction

Medium SQL Transactions 20 views
Explanation Complexity

Problem Statement

While placing an order, keep a savepoint after reserving stock. If payment fails, rollback to savepoint.

Input Format

SQL script

Output Format

Statements

Example

Inventory, Payments
Safe rollback

Constraints

Use savepoint

Input / Output Format

Input Format
SQL script
Output Format
Statements
Constraints
Use savepoint

Examples

Input:
Inventory, Payments
Output:
Safe rollback

Example Solution (Public)

SQL 
BEGIN; UPDATE Inventory SET qty = qty - 2 WHERE product_id=10 AND qty >= 2; SAVEPOINT stock_reserved; INSERT INTO Payments(order_id, amount, paid_at) VALUES (7001, 1200, CURRENT_TIMESTAMP); ROLLBACK TO stock_reserved; COMMIT;

Official Solution Code

BEGIN; UPDATE Inventory SET qty = qty - 2 WHERE product_id=10 AND qty >= 2; SAVEPOINT stock_reserved; INSERT INTO Payments(order_id, amount, paid_at) VALUES (7001, 1200, CURRENT_TIMESTAMP); ROLLBACK TO stock_reserved; COMMIT;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.