I know that select_for_update locks the records that are being queried if we have more than one concurrent user. But does it also affect the evaluation of the queried records.
For example, if user A and user B Query the table 'Jobs' at the same time based on a single variable 'Available' which is a Boolean. Both users would like to access records of available jobs (where Available = True).
User A managed to be queued first and the records he is accessing are now locked for user B.
If user A changes all records to False, does user B not get any records? Or does he get the same records as user A but with Availability equal to False?
Also, does select_for_update() work with MySQL? Or does it have a different solution for concurrent queries?
From documentation regarding select_for_update
Usually, if another transaction has already acquired a lock on one of the selected rows, the query will block until the lock is released. If this is not the behavior you want, call select_for_update(nowait=True). This will make the call non-blocking. If a conflicting lock is already acquired by another transaction, DatabaseError will be raised when the queryset is evaluated. You can also ignore locked rows by using select_for_update(skip_locked=True) instead. The nowait and skip_locked are mutually exclusive and attempts to call select_for_update() with both options enabled will result in a ValueError.
it basically means that only one query goes through at the time, when it is processed other one gets turn ( mutex behaviour )
in your example considering for instance
...filter(available=true).select_for_update()
if first one changes records other request will get new state ( without records first set to false ) as that particular thread waits for querying on the database