I'm developing a PHP/MySQL Intranet application. Since it is very data-entry oriented, I'm wondering if it were possible to implement pessimistic locking in such a scenario, where a record is locked with a "select...for update" when it is accessed, then a user can make their changes, then the lock is released once they hit submit.
The problem that I see is since HTTP is a stateless protocol, the PHP connection to the database ends as soon as the execution of the script that gets the record from the database ends. I'm assuming that when that happens, the lock dissolves automatically and the whole scheme is rendered useless. Am I right on this? If so, is there a way to persist the lock even after the loading PHP script ends, so that it is released on a COMMIT issued by the PHP script that handles the saving?
Or is optimistic locking the only way to go?