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.
Subject
Views
Written By
Posted
1763
March 17, 2013 09:07AM
1055
March 21, 2013 09:28AM
984
March 21, 2013 02:34PM
918
March 23, 2013 12:23AM
937
March 25, 2013 08:13AM
969
March 28, 2013 12:04AM
Re: Minimize locking on UPDATE with multiple WHERE columns
1000
April 05, 2013 01:38PM
914
April 05, 2013 07:05PM
960
April 05, 2013 07:32PM
984
April 05, 2013 10:16PM
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.