MySQL Forums
Forum List  »  InnoDB

Re: Minimize locking on UPDATE with multiple WHERE columns
Posted by: Noha Taha
Date: April 05, 2013 01:38PM

Here is the output of show innodb status (after changin some extra unecessary info):
---TRANSACTION A1D97DD7, ACTIVE 13 sec fetching rows
mysql tables in use 1, locked 1
568 lock struct(s), heap size 63928, 45240 row lock(s)
MySQL thread id 7513, OS thread handle 0x4beb7940, query id 296174616 ip-xxx xxxx init
UPDATE table1 USE INDEX(index_table1_on_row2_and_row1) SET row1=xxx WHERE row2 <= xx AND row1 IS NULL ORDER BY row2 LIMIT 100

The transaction is holding lock on the primary key even after I told it which index to use. Notice that it's holding 45k locks while it only needs to update 100.
I 've tried selecting and then updating by id but that creates a concurrency issue even if I try to use optimistic locking mechanism in my application (Rails) so I need to use locking here.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.