Data Confllict in MySQL

[sircalleba66]

Hello,

How to manage a data conflict if more than one users write at the same time of a particular record(s).

Thanks

 

[sircalleba66]

Oh btw I am using VFP and MySQL is my back end.

 

[Olaf Doschke]

Then that's a question for MySQL. You don't use mechanisms typical for VFP, as locking, but use transactions. Transactions will be queued, if users write to the same table. Users having a transaction involving a table thereby writelock that table to others, and if that's done as short as possible, MySQL will - as said - queue other transactions needing that query. So pn the rough scale it's MySQL handling that, with manual or automatic transactions.

Bye, Olaf.

 

[sircalleba66]

Hello Sir actually here is my code:
SQLSETPROP(_SCREEN.oApp.nConnectionHandle,"transaction",2)
TEXT TO lcSql_UpdateInventory NOSHOW
UPDATE detailinv SET
Quantity = ?m.lnQty
where prod_id = ?m.lcProd_id
and lotno = ?m.lcLotno
and expdate = ?m.lcExpdate
ENDTEXT
IF SQLEXEC(_SCREEN.oApp.nConnectionHandle,lcSql_UpdateInventory) < 0
Aerror(laerr)
messagebox(laErr[2])
SQLRollback(_SCREEN.oApp.nConnectionHandle)
else
SQLCOMMIT(_SCREEN.oApp.nConnectionHandle)
endif
SQLSETPROP(_SCREEN.oApp.nConnectionHandle,"transaction",1)

Thanks Sir

 

[Olaf Doschke]

Looks ok. You don't need to go for manual transactions, but I see why you ask, as lowering inventory quantity is critical, if done in the wrong order and with wrong values.

Actually this is the weak point in your sql. You have a new value of m.lnQty computed, before you even start the transaction. Instead you should set m.lnQtyOrdered to the number of items you want to remove from stock and do Set Quantity = Quantity - ?m.lnQtyOrdered to get consistent results, no matter in what sort order the Updates are really done.

Bye, Olaf.

 

[Olaf Doschke]

On a larger scale of the process: You will perhaps precheck if there is enough stock in inventory to pull from. But a user then can still not be sure the inventory will already be lower, when he submits his order, so you need to check for sufficient inventory quantity both before the update and after the update. That also means you should check for a negative Quantity afterwards are only subtract, if the result is >=0. If Quantity get's negative or you don't subtract in the first place, the user must be informed the order failed due to a concurrent order already having subtracted form the initial quantity.

Even though you don't show where you do compute ?m.lnQty it's clear to see you do that outside and before the transaction starts and that can easily lead to wrong quantity info, if two users start their order with the same quantity, you only subtract one users order quantity from the inventory. But that's not a problem coming from VFP or MySQL.

Bye, Olaf.

 

[sircalleba66]

Thanks Sirs, I will recheck my code.

 

[Olaf Doschke]

Or put into technical terms: You are virtually starting a transaction the moment you determine the initial Quantity, from which you subtract the amount a user orders and compute lnQty, and that's a problem, because you don't yet technically start a transaction and lock that Quantity for concurrent users. You'd also not do that, if you just start a transaction, the table or record only get's a write lock once you write, other users will still read the Quantity value. So donm't compute update values with values you retrieve before you save your change of data. In the worst case that makes "concurrent" meaning all the minutes users need to put together their order, search items and put them into a shopping cart instead of meaning the few ms a save needs to update the current stock data to it's next state. See? You're artificially highering the need of lock times, turn your logic around to only need the few ms you really change data from it's current to the next state, that lowers the problems you may get with concurrent changes.

Bye, Olaf.

 

[sircalleba66]

Thank you Sir Olaf, my Purchase Entry works, your explanations helps me a lot.

Cris