There is an unpaid invoice. After payment, a payment in the amount of N is created for the payment system callback. + The invoice status is set to "Paid". The transaction starts only if the account is in the "waiting for payment" state.

Problem: The payment system sends 2 requests by callback with a difference of 1 second. And sometimes a situation arises that the 2nd invoice payment transaction is launched in parallel. This leads to 2 results: 1. 70% - a deadlock occurs. 2. 30% - both transactions are successful and the payment is recorded 2 times in the database.

How to deal with this correctly?

    1 answer 1

    The transaction starts only if the account is in the "waiting for payment" state.

    Check the status inside the transaction, while grabbing the necessary lock. Then competitive requests will be queued for blocking.

    begin; select .. from tablename where uid = ? and status = 'wait_payment' for update;

    If there are 0 rows in the result, then you commit (or rollback) and fail "there is no such payment."

    If 1 line is found, then update the status on the one that is needed and in the same transaction you take other necessary actions.

    All competitive requests will be queued for blocking for update ; only one transaction can hold this blocking at a time. If the blocking transaction is canceled, the next pending thread will capture the lock and only one will be executed. If the transaction commits and changes the status of the row - then select .. for update will read the updated version of the row and, accordingly, return 0 rows - which will again be a regular response, without re-processing the action.

    Depending on the implementation of the payment system, it may be necessary to check the status of payment from the DBMS on the application — in order to correctly generate the replies "transaction processed" instead of "transaction not found" for repeated requests from the payment system. Those. if the order is already recorded in the database as paid, then reply with the payment "ok", but do nothing else.