Lock timeout and safe retry idea

Lock timeout and safe retry idea

Hard SQL Transactions 17 views
Explanation Complexity

Problem Statement

While processing orders, sometimes you get stuck waiting on locks. Set a short lock timeout, pick one NEW order with a row lock, update it to PROCESSING, then commit.

Input Format

SQL script

Output Format

Statements

Example

Orders
Safe processing

Constraints

Assume PostgreSQL lock_timeout and row locking

Input / Output Format

Input Format
SQL script
Output Format
Statements
Constraints
Assume PostgreSQL lock_timeout and row locking

Examples

Input:
Orders
Output:
Safe processing

Example Solution (Public)

SQL 
SET LOCAL lock_timeout = '3s'; BEGIN; SELECT order_id FROM Orders WHERE status='NEW' ORDER BY order_id FETCH FIRST 1 ROW ONLY FOR UPDATE; UPDATE Orders SET status='PROCESSING' WHERE order_id = 7001; COMMIT;

Official Solution Code

SET LOCAL lock_timeout = '3s'; BEGIN; SELECT order_id FROM Orders WHERE status='NEW' ORDER BY order_id FETCH FIRST 1 ROW ONLY FOR UPDATE; UPDATE Orders SET status='PROCESSING' WHERE order_id = 7001; COMMIT;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.