Locking data that is read and modified during posting
It may seem that you completed the task. However, there is still a very important issue you have not taken care of.
Now the procedure is executed as follows:
- The first query (Query) is executed. It generates a temporary table with the list of document materials.
- The second query (Query2) is executed. It reads the cost and balance of materials available in the document tabular section.
- The RegisterRecords.Write() line is executed. It writes the register records.
- The third query (Query3) is executed. It performs the check for negative balances.
Note that from the beginning of the second query execution and till the end of the procedure you need to ensure that the product cost and balance values accessed in the procedure remain unchanged and are not even available for other transactions to read. Obviously the platform locks this data from modification but the lock is only applied when the register records are written.
But the following scenario is possible. During the second query execution you read that there are 2 units of some material available. Another transaction (another user) that intends to spend the materials also reads that 2 units of the material are available. Then the platform writes the register records and locks the data. The other transaction waits for the platform to release the data. You post the document, withdraw 2 units of the material, and release the data. The other transaction also attempts to withdraw 2 units of the material but they are not available any more.
A similar situation can occur between steps 3 and 4. So the balance check might be performed incorrectly.
To avoid such collisions, you need to lock balance values from reading by other transactions even before the second query is executed. Before reading any data that you intend to modify, you also need to prevent other transactions from reading the data until your modifications are completed (or until the document posting is canceled).