-4

I've developed a web application using Apache, MySQL and PHP.

This web app allows multiple users' to login to the application. Then, through the application, they have access to the Database.

Since race conditions may apply when two or more users try to SELECT/UPDATE/DELETE the same information (in my case a row of a Table), I am searching for the best way to avoid such race conditions.

I've tried using mysqli with setting autocommit to OFF and using SELECT .... FOR UPDATE, but this fails to work as -to my understanding- with PHP, each transaction commits automatically and each connection to the db is being auto released when the PHP -->html page is provided/loaded for the user.

After reading some posts, there seem to be two possible solutions for my problem :

  1. Use PDO. To my understanding PDO creates connections to the DB which are not released when the html page loads. Special precautions should be taken though as locks may remain if e.g. the user quits the page and the PDO connection has not been released...

  2. Add a "locked" column in the corresponding table to flag locked rows. So e.g. when an UPDATE transaction may only be performed if the corresponding user has locked the row for editing. The other users shall not be allowed to modify.

The main issue I may have with PDO is that I have to modify the PHP code in order to replace mysqli with PDO, where applicable.

The issue with the scenario 2 is that I need to also modify my DB schema, add additional coding for lock/unlock and also consider the possibility of "hanging" locked rows which may result in additional columns to be added in the table (e.g. to store the time the row was locked and the lockedBy information) and code as well (e.g. to run a Javascript at User side that will be updating the locked time so that the user continuously flags the row while using it...)

Your comments based on your experience would be highly appreciated!!!

Thank you.

Tassos Pan
  • 23
  • 5
  • One of the first things you should decide is if you want pessimistic concurrency or optimistic concurrency. Let's say for example two users are trying to modify a record at a given time. User A opens the record, User B opens the record, one of them saves, then the other one saves. How should this scenario play out in your user experience? When the second user tries to open, should they get a message saying someone else opened it? When the second user tries to save, should they get a message that someone else saved and they need to refresh? Something else? – David Dec 01 '18 at 22:00
  • If the users are editing the same cell in the database at the same time, what do you expect to be? What will you show the other users then? – Taha Paksu Dec 01 '18 at 22:01
  • @David same time same thoughts :D – Taha Paksu Dec 01 '18 at 22:02

1 Answers1

0

It might be an opinion instead of a technical answer, but too long to write it as a comment.

I want to think it like booking a seat in a movie or a flight: When an user selects a seat and presses next, the seat will be reserved for that user for a certain amount of time, and when user doesn't finish in the given time, it gets a timeout exception without processing further. You can use an edit button besides the row, and when the user clicks it, on the server side, you check if the row is reserved to someone else, and if not, reserve it to the user. Other users won't get an edit form when they also click the edit button after that user. I don't know how database systems handle this though.

But, one way to make it sure, re-read the row after user edits and commits it to display the user. If any lock mechanism prevented the row from being updated, the user will also know it by not seeing the change in the row.

Taha Paksu
  • 15,371
  • 2
  • 44
  • 78
  • Hello and thank you for your feedback. In my case the second (or third or...) user should not be able to access that record for editing. So they would get a message that the record is currently locked by User 1 (eg via a JavaScript popup window). But the row must be somehow protected so that if e.g user 1 closes his tab/window editing that row, the row should be unlocked. The same if his browser e.g. crashes or hangs... – Tassos Pan Dec 03 '18 at 06:29
  • You need to check and lock with ajax before enabling the row editor then. – Taha Paksu Dec 03 '18 at 06:38