The application (PHP) at the request of the buyer selects the best options for the price and issues an invoice for payment.

In the base table offers:

id kolvo price certificate 1 10 800 cert12345 2 5 300 cert23456 3 20 200 cert34567 4 15 500 cert23456

The application gets single-handedly, starting with the cheapest offer:

 SELECT * FROM offers WHERE kolvo>0 ORDER BY price ASC LIMIT 1 OFFSET 0;

then verifies the certificate (separate operation, request to a third party) and if it is satisfied with the certificate, it creates a position in the generated invoice for payment:

 INSERT INTO pokupki (kolvo,offer_id) VALUES (соответствующие значения);

And reduces the amount of goods found in the sentence:

 UPDATE offers SET kolvo=(новый_остаток) WHERE id=(id);

Then, if the number is not typed in full or the certificate is not satisfied, proceeds to the next iteration (OFFSET ++):

 SELECT * FROM offers ORDER BY price ASC LIMIT 1 OFFSET 1;

Naturally, this is all wrapped in a transaction.

There was a problem to solve the issue of competitive access to data in offers. The fact is that the certificate verification operation takes some time, while all parallel requests are directed to the same lines (because of the ORDER BY price ASC in the select) and it’s very likely that the same lines will be given set of transactions. As a result, transactions are likely to successfully publish and cause confusion in the balances.

After reading the MVCC documentation, I wondered if the MVCC was justified in this situation? The fact is that with this approach each transaction creates a snapshot of data for each transaction and in the future as we execute, we either get success or failure, if we refuse, we naturally try to fetch again. But still the same ORDER BY price ASC and a long wait for certificate verification can greatly complicate this process.

Tell me please, is it possible to block the selected line exactly so that no one else is guaranteed to read it? That is, while the certificate verification process is in progress, it remains inaccessible for reading, and if the parallel process starts sampling, it simply does not see this line, but selects the next one, which it also blocks in turn. And is it possible in this case, after checking the certificate (if something did not suit) to forcibly remove this lock for reading , so that while this invoice is being formed, parallel processes could see this line, which did not fit this process specifically?

I use Postgres 9.2, but the creation process is in progress, so it doesn't matter.

Thank you

    1 answer 1

    Pulling a long external call with an open transaction is not a good idea at all.

    Is this separate operation on a third party static? It can be called repeatedly and only on its side to decide whether to place an order? Or does it change the state of this outside, for example, reserves the goods there?

    If the multiple call is valid, then first perform the normal non-blocking select. Then make a request to the external system. If arranged for the answer, then start the transaction, execute the query:

     UPDATE offers SET kolvo=kolvo - :needCount WHERE id=:id and kolvo >= :needCount;

    If affected_rows is equal to one, then continue to place an order, write to pokupki and what else should be recorded immediately. If affected_rows == 0, then this position is not in the required quantity. Do rollback and return to the beginning.

    If the third party does not allow simply to pull the method without a purchase, then the logic will have to make a waiting list for confirmations.

    Add a table to which you will write a log of waiting for confirmations with the fields id bigserial, offer_id, wait_count, created_at default now (). I will explain later why. First do

     begin; UPDATE offers SET kolvo=kolvo - :needCount WHERE id=( SELECT id FROM offers WHERE kolvo >= :needCount ORDER BY price ASC LIMIT 1 OFFSET 0 FOR UPDATE; ) RETURNING *; insert into offers_log (offer_id, wait_count) values (:offerId, :needCount) returning id; commit;

    Effectively, as a result, you get a sample of your select. Information is copied to the log when, which offer and how many positions are reserved.

    Perform a request to a third party. In the case of receiving a satisfactory answer, delete (or mark completed) the corresponding line in the offers_log. In the event of an error or failure of a third party

     begin; UPDATE offers SET kolvo=kolvo + :needCount WHERE id=:id; delete from offers_log where id=:logId; commit;

    Those. return reserve back. Attention to the affected_rows removal from the log in both cases. If there is 0, then a global error has occurred.

    And now why the log is needed: if for some reason the script falls, then the reserve will be recorded, but will never be returned to the available ones. According to the log, for example, the crown task can select all records from the log that exceed a certain time interval and return them to the available ones.

    • Yes you are right, this is a multiple call to each operation "booking". On the third side, our requests do not change anything, but make a decision after receiving the data. I'll think about the options you offer, set status later, thanks! - phpcoding