Menu
I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.
I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.
What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?
358
Shared and Exclusive Locks A shared ( S ) lock permits the transaction that holds the lock to read a row. An exclusive ( X ) lock permits the transaction that holds the lock to update or delete a row.
There are two models for locking data in a database: Optimistic locking , where a record is locked only when changes are committed to the database. Pessimistic locking , where a record is locked while it is edited.
Row-level locking systems can lock entire tables if the WHERE clause of a statement cannot use an index. For example, UPDATES that cannot use an index lock the entire table. Row-level locking systems can lock entire tables if a high number of single-row locks would be less efficient than a single table-level lock.
MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself.
Read up on database transactions. That's probably a better way to handle what you need than running LOCK TABLES.
184
Manually locking tables is the worst think you could ever do. What happens if the code to unlock them never runs (because the PHP fails, or the user next clicks the next step, walks away from the PC, etc).
One way to minimize this in a web app, and a common mistake devs do, is to have a datagrid full of text boxes of data to edit, with a save button per row or on the whole table. Obviously if the person opens this on Friday and comes back Monday, the data could be wrong and they could be saving over new data. One easy way to fix this is to instead have EDIT buttons on each row, and clicking the button then loads an editing form, this way they are hopefully loading fresh data and can only submit 1 row change at a time.
But even more importantly, you should include a datetime field as a hidden input box, and when they try to submit the data look at the date and decide how old the data is and make a decision how old is too old and to warn or deny the user about their action.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
