SELECT LOCK IN SHARE MODE

mysql locking innodb
user006779 picture user006779 · Jul 7, 2011 · Viewed 15.4k times · Source

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

Maxim Krizhanovsky picture Maxim Krizhanovsky · Jul 7, 2011

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)

Related questions

How to improve INSERT INTO ... SELECT locking behavior

In our production database, we ran the following pseudo-code SQL batch query running every hour: INSERT INTO TemporaryTable (SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true) Now this query itself does not need to be fast, but I noticed it was …

Read More
"The total number of locks exceeds the lock table size" Deleting 267 Records

I'm trying to delete 267 records out of about 40 million. The query looks like: delete from pricedata where pricedate > '20120413' pricedate is a char(8) field. I know about adjusting innodb_buffer_pool_size, but if I can do select …

Read More
When exactly MySQL locks a row on updating InnoDB table?

If I have this multiupdate query UPDATE user u INNER JOIN user_profile up ON up.user_id = u.id SET u.name = 'same_name_i_already_had', up.profile.age = 25 WHERE u.id = 10 Let's suppose the row 10 in user …

Read More