| Bug #101465 | mysql doesn't release index sx latch properly in page split operation | ||
|---|---|---|---|
| Submitted: | 5 Nov 2020 1:03 | Modified: | 6 Nov 2020 13:10 |
| Reporter: | wei wang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Nov 2020 13:22]
MySQL Verification Team
Hi Mr. wang, Thank you for your performance regression report. However, in the latest 8.0.22 micro transactions source there is no source code that is OR-ing those two types of locks ....... Grateful in advance for your feedback.
[6 Nov 2020 3:43]
wei wang
from the latest 8.0.22 source code on github: https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/btr/btr0btr.cc, there still are some places which early release sx latch, like below: if (!srv_read_only_mode && !cursor->index->table->is_intrinsic() && insert_will_fit && page_is_leaf(page) && !dict_index_is_online_ddl(cursor->index)) { mtr->memo_release(dict_index_get_lock(cursor->index), MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK); /* NOTE: We cannot release root block latch here, because it has segment header and already modified in most of cases.*/ }
[6 Nov 2020 3:49]
wei wang
below are 3 places: 1. function btr_page_split_and_insert in https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/btr/btr0btr.cc if (!srv_read_only_mode && !cursor->index->table->is_intrinsic() && insert_will_fit && page_is_leaf(page) && !dict_index_is_online_ddl(cursor->index)) { mtr->memo_release(dict_index_get_lock(cursor->index), MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK); 2. function btr_cur_pessimistic_update, btr_cur_pessimistic_delete in https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/btr/btr0cur.cc if (!srv_read_only_mode && !big_rec_vec && page_is_leaf(page) && !dict_index_is_online_ddl(index)) { mtr_memo_release(mtr, dict_index_get_lock(index), MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK); /* NOTE: We cannot release root block latch here, because it has segment header and already modified in most of cases.*/ }
[6 Nov 2020 13:10]
MySQL Verification Team
Hi Mr. wang, Thank you for your feedback. You are correct. Verified as reported.
Description: Sometimes, it's safe to release index sx latch before mtr commit. mysql calls below statement to release sx latch: mtr->memo_release(dict_index_get_lock(cursor->index), MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK); but in mtr->memo_release, the type is MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK = 128 + 256 = 384. Function mtr::memo_release uses Find::operator() to find the sx latch, and in Find::operator() it uses below condition: struct Find { /** Constructor */ Find(const void *object, ulint type) : m_slot(), m_type(type), m_object(object) { ut_a(object != nullptr); } /** @return false if the object was found. */ bool operator()(mtr_memo_slot_t *slot) { if (m_object == slot->object && m_type == slot->type) { m_slot = slot; return (false); } return (true); } It uses m_type == slot->type to find the index lock object, that is slot->type == MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK = 128 + 256 = 384, which doesn't exist. It should use conditon m_type & slot->type. So, all the places with calls mtr->memo_release(dict_index_get_lock(cursor->index), MTR_MEMO_X_LOCK | MTR_MEMO_SX_LOCK); to release index sx latch doesn't succeed. How to repeat: You can add a breakpoint to that function and do a lot of insert to a table. Suggested fix: diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc index 41334ba0ce6..b348e38fe8a 100644 --- a/storage/innobase/mtr/mtr0mtr.cc +++ b/storage/innobase/mtr/mtr0mtr.cc @@ -99,7 +99,7 @@ struct Find { /** @return false if the object was found. */ bool operator()(mtr_memo_slot_t *slot) { - if (m_object == slot->object && m_type == slot->type) { + if (m_object == slot->object && (m_type & slot->type)) { m_slot = slot; return (false); }