Menu
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.
265
LOCK IN SHARE MODE. Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits.
A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.
Oracle uses two modes of locking in a multiuser database: Exclusive lock mode prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
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)
167
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
