my question is: How can I get a certain row to update and on the other side, block that row to be selected by other sessions. I don't want a simple SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE. I want the second session not to be able to select the row at all. Maybe some isolation level, I tried them all but nothing. Thanks
Unless you set the transaction level to
read uncommitted, a transaction will see the unmodified row. The second session only has to acknowledge the existence of the first session when both sessions want to lock that row, otherwise they will act as if the other transaction doesn't exist (as that session could rollback at any time) - which is the whole point of using transactions.
In MySQL, you can get a locking read by either explicitly using
SELECT .. FOR UPDATE or
SELECT .. LOCK IN SHARE MODE, or by using the "serializable"-isolation level for the second transaction, which implicitly adds the
LOCK IN SHARE MODE for you to every select in that transaction:
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)
So if you can force the second transaction to use that transaction level and to not enable autocommit, you can lock that row for this session; but keep in mind that this mode can slow down concurrent queries, as every read locks, so you might want to use this mode only when you need it.
This behaviour complies with the requirements of the sql standard. The exact implementation is a design choice, and other databases will do this differently. E.g. the MSSQL Server will not read your modified, uncommitted row (unless the isolation level is