![]() *** (2) WAITING FOR THIS LOCK TO BE GRANTED: Supremum record looks very different from any real index record in this respect.ĭeadlock happens because at INSERT step both transactions try to set "insert intention waiting" X lock on supremum record: So, if it is intended and can not be easily changed, it should be clearly and explicitly documented. There can NOT be two exclusive lock on one record, or these locks should have some other name, not "exclusive". This behavior not only leads to deadlock in the scenario described, but is against common understanding of X (exclusive!) lock. At least, nothing like this is documented at. But(!) second transaction was able to set X lock on supremum record even after that same supremum record was already X locked by the first transaction. ![]() Record lock, heap no 1 PHYSICAL RECORD: n_fields 1 compact format info bits 0Ġ: len 7072656d756d asc supremum TABLE LOCK table `test`.`tt` trx id 0 13826 lock mode IX MySQL thread id 3, query id 188 localhost root Record lock, heap no 1 PHYSICAL RECORD: n_fields 1 compact format info bits 0 0: len 7072656d756d asc supremum RECORD LOCKS space id 0 page no 52 n bits 72 index `PRIMARY` of table `test`.`tt TABLE LOCK table `test`.`tt` trx id 0 13827 lock mode IX MySQL thread id 6, query id 215 localhost root TRANSACTION 0 13827, ACTIVE 9 sec, OS thread id 2957455360Ģ lock struct(s), heap size 320, 1 row lock(s) FOR UPDATE on empty table we have the following locks set: After enabling lock monitor one can see that after execution of SELECT. Valeriy Kravchuk Thank you for the problem report. Or even better, always do exclusive table lock on SELECT FOR UPDATE. > ERROR 1213 (40001): Deadlock found when trying to get lock try restarting transactionĭo exclusive table lock on SELECT FOR UPDATE when the table is empty. SESSION 2: select * from tt where id=1 for update SESSION 1: select * from tt where id=1 for update SESSION 1: create table tt (id int primary key) engine=InnoDB In such case threads can deadlock on INSERT. The problem is that when the table is empty, MySQL doesn't do exclusive lock. This works when both threads select same record. We're trying to solve this by using SELECT FOR UPDATE, in order to get exclusive lock instead of shared lock. In this case there is great possibility of a deadlock (when two threads get shared lock with SELECT, and then none of them can do UPDATE). In our system there are many concurrent worker threads.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |