Lock an Order to Prevent Duplicate Payments using RDBMS
It is a common concern for an ecommerce website that customer might paid twice for a certain order, probably due to user's unawareness, front end bugs or browser unresponsiveness during the transaction, which hurts user experience. Thus, backend needs to prevent duplicate payments for an order.
If the website is backed by an RDBMS, we can use the database lock to ensure this. Suppose the order is stored in database in the following structure:
Column | Type |
---|---|
id (PK) | integer |
amount | numeric |
paid | boolean |
In a naive approach, in the transaction to make a payment, we can lock the order in the following way:
BEGIN;
SELECT * FROM order WHERE id = <order_id> FOR UPDATE;
# Check the order `paid` status:
# If `paid` is equals to false,
# process the payment,
# and update `paid` to true upon successful payment
COMMIT;
# Otherwise, a payment has been made,
# so no further action is required.
ROLLBACK;
If the order has no payment processed yet and multiple payment requests (for example, 2), run into the transaction block, one of the transactions (Transaction A) obtains the lock and the other (Transaction B) is blocked at SELECT ... FOR UPDATE
statement. Then, Transaction A continues to perform the payment, updates paid flag of the order after the payment is successful, and commits the transaction. As this time, Transaction B obtains the lock, and it sees that order is already paid, so it just rolls back without further actions.
This method solves the duplicate payments problem. However, since the requests blocked by the database lock occupy server resources forever until they successfully obtained the lock, it might become a performance bottleneck. The user also needs to wait for a long time if his/her payment request is contended for the lock, which results in a bad user experience.
Therefore, the following method aims to return earlier when payment contention is detected. Another table, order_mutex
, is required:
BEGIN;
# try
INSERT INTO order_mutex VALUES (<order_id>);
# Catch duplicate key error
ROLLBACK;
# Otherwise, continue
COMMIT;
# If order_mutex is inserted successfully
BEGIN;
# If `paid` is equals to false,
# process the payment,
# and update `paid` to true upon successful payment
COMMIT;
# Otherwise, a payment has been made,
# so no further action is required.
ROLLBACK;
BEGIN;
DELETE order_mutex WHERE order_id = <order_id>;
COMMIT;
# Otherwise, return an error response to notify user
# that another payment request is being processed.
When multiple payments requests are executed concurrently, only one of them can successfully obtain the order_mutex
by inserting a new row, and it can continue to process the payment. The rest requests understand that they failed to obtain the lock immediately after they fail to insert into the order_mutext
table. Therefore, they will not be blocked for a long time, and they are able return appropriate responses to users earlier than the first method.