Locking a table at a time when it is possible another connection might have read it PHP/MYSQL
Locking a table at a time when it is possible another connection might have read it PHP/MYSQL
The problem I have here is that I don't want to lock a mysql table at the start of my PHP script for obvious performance gains. I want to implement locking only at the point when I'm sure I may need to write to the table. Example, maybe I need to update account balance.
But the problem is, what if I lock at that point when I need to credit some numbers to the account balance field, and there is another connection that has already read this row from this table before the lock and update was made. Yes that other connection will have to wait until I am done before it can do anything else in the table. But that other connection already has an old data (which might be wrong account balance) and any write that it does means it is working with an incorrect old balance. Let us assume it wants to add 1 to the old balance. It is supposed to add this 1 to the most updated balance.
But the first connection has changed the account balance and this second connection is going to do the same which results in unwanted results.
Is there an alternative to this locking only when I am sure I will do a write or do I have to fully implement table locking at the start of the script to avoid this racing scenario.
UPDATE balanceTable set balance = balance + 1
@Adyson I get your point. I never knew I could mention a column name and use it as a variable to perform maths operation. I understand that your solution solves the mathematical problems. How about text? In fact the problem is way more complex than just adding 1 to a value. I just used it as an example. It could involve a whole lot if other updating.
– Ekene
Aug 31 at 6:27
Well if you just want to overwrite text with another piece of text then you literally just replace one with the other. No need to know the previous value.
– ADyson
Aug 31 at 7:32
However I think your concern is that someone would try to update a row with data which contains values in some fields which have already been overwritten by another user? If so, one solution is to have a timestamp column in your table indicating the time of the last update. Make your app supply the timestamp when it tries to update a row. If the timestamp supplied doesn't match the current timestamp then the user must be trying to update based on an out of date version of the row.
– ADyson
Aug 31 at 7:40
If you want to make it a bit more sophisticated you could look into some more complex application design patterns such as Event Sourcing and CQRS (which are often employed together) which can help with these kinds of versioning issues - as well as various other benefits which you may or not may not need. They also introduce some complexity however, so make a careful choice.
– ADyson
Aug 31 at 7:50
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
"Let us assume it wants to add 1 to the old balance"...why do that? Just use an UPDATE to add 1 to whatever the balance is now e.g.
UPDATE balanceTable set balance = balance + 1. No need to know the current or previous value, just let SQL look it up at the time of execution. Combine that with correct usage of Transactions (rather than explicit locking) and I think you'll probably be ok, at least for that particular example anyway.– ADyson
Aug 30 at 21:55