I'm using DBAL in my project because it is easier to convert the database statements in an already written project that I'm converting to Symfony v2.8 and MySQL than going with full-on Doctrine, but now I need to implement "read-only row locks" to prevent data changes by other users while a pair of tightly coupled but separate SELECT statements are consecutively executed, and I'm thinking that I should use Transactions and SELECT FOR UPDATE statements. However, I don't see that DBAL supports SELECT FOR UPDATE statements in it's documentation. I do see that Transactions are supported, but as I understand it, these won't prevent other users from UPDATE-ing or DELETE-ing the data in the same data row that the SELECTs statements are using.
Specifically, the two SELECTs share data retrieved in one row by the first SELECT with a second SELECT that retrieve multiple rows from the same tables based on the first SELECT. The two SELECTs are somewhat complex, and I don't know if I could combine them into a super-sized single SELECT, nor do I really want to as that would make the new SELECT harder to maintain in the future.
The problem is that other users could be updating the same values retrieved by the first SELECT and if this done between the the two SELECTs, it would break the second SELECT of the pair and either prevent the second from returning data or at least return the wrong data.
I believe that I need to use a SELECT FOR UPDATE to lock the row that it retrieve to temporarily prevent other users from performing their updates and deletes on the single row retrieved by the first SELECT of the pair, but since I'm not actually performing an update, but rather two SELECTs, how do I release the lock on the one row locked by the first SELECT without performing a 'fake' update, say by UPDATE-ING a column value with the same value it already had?
Thanks
For the transaction you want repeatable results for:
SELECT ...
{some processing}
SELECT {that covers the same rows} [will return the same result]
COMMIT
note: READ ONLY is optional
Experiment by running two mysql client connections and observer the results. The other connection can modify or insert rows covering the first selects criteria and the first transaction won't observe them.