SELECT LOCK IN SHARE MODE

Question

i have read this article from dev.mysql.

in that page is a example that when use select for update and dont use lock in share mode and says

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter

but first line of this page says

SELECT ... LOCK IN SHARE MODE: The rows read are the latest available, ** so if they belong to another transaction ** that has not yet committed, the read blocks until that transaction ends.

is there a paradox?

i mean two users dont read the counter at the same time beacause if they belong to another transaction the read blocks until that transaction ends.

Answer 1

If there is another transaction, that has modified the row, the SELECT ... LOCK IN SHARE MODE waits. If the row is not modified, it does not wait. Which leads to the first situation, that 2 transaction can SELECT ... LOCK IN SHARE MODE, but none of them can update the record (deadlock)