PHP + MySQL/MariaDB + avoid race condition : <p>I've developed a web application using Apache, MySQL and PHP.</p>

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

<p>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.</p>

<p>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.</p>

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

<ol>
<li><p>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...</p></li>
<li><p>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.</p></li>
</ol>

<p>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.</p>

<p>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...) </p>

<p>Your comments based on your experience would be highly appreciated!!!</p>

<p>Thank you.</p>
