infimum,supremum鎖二
the infimum record can be a dummy target for temporary record locks. Stores on the page infimum record the explicit locks of another record. This function is used to store the lock state of a record when it is updated and the size of the record changes in the update. The record is moved in such an update, perhaps to another page. The infimum record acts as a dummy carrier record, taking care of lock releases while the actual record is being moved. */ void lock_rec_store_on_page_infimum( /*===========================*/ const buf_block_t* block, /*!< in: buffer block containing rec */ const rec_t* rec) /*!< in: record whose lock state is stored on the infimum record of the same page; lock bits are reset on the record */ /* Predicate lock always on INFIMUM (0) */ if (is_predicate_lock(mode)) { rec_lock.n_bits = 8; memset(&lock[1], 0x0, 1); supremum上的鎖 For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value. 頁在重組分裂的時候,也有處理infimum,supremum上的鎖 https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/
針對infimum的鎖,經過測試,只要更新的欄位長度改變,增加或減小,也是悲觀更新,都會出發複製鎖到 infimum,長度沒有改變那麼不會觸發,那如果有併發的請求,update一個頁中的多行記錄,那怎麼辦?infimum這種複製也會出現爭用
mysqld!lock_rec_store_on_page_infimum(buf_block_t const*, unsigned char const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:3629) mysqld!btr_cur_pessimistic_update(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, mem_block_info_t*, big_rec_t**, upd_t*, unsigned long, que_thr_t*, unsigned long long, mtr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/btr/btr0cur.cc:4496) mysqld!row_upd_clust_rec(unsigned long, upd_node_t*, dict_index_t*, unsigned long*, mem_block_info_t**, que_thr_t*, mtr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:2703) mysqld!row_upd_clust_step(upd_node_t*, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:2957) mysqld!row_upd(upd_node_t*, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:3054) mysqld!row_upd_step(que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:3200) mysqld!row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:2582) mysqld!row_update_for_mysql(unsigned char const*, row_prebuilt_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:2672) mysqld!ha_innobase::update_row(unsigned char const*, unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:8257) mysqld!handler::ha_update_row(unsigned char const*, unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:8134) mysqld!mysql_update(THD*, List<Item>&, List<Item>&, unsigned long long, enum_duplicates, unsigned long long*, unsigned long long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:894) mysqld!Sql_cmd_update::try_single_table_update(THD*, bool*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:2906) mysqld!Sql_cmd_update::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:3037) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:3616) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
另外針對這種非原地更新的,走的是悲觀更新,跟mysql內部更新主鍵一樣,將舊的記錄標記刪除,然後在插入新的記錄。
為什麼針對唯一索引的範圍 dml會鎖下一個記錄?
有興趣學習原始碼的加群一起學習啊 QQ: 700072075
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25719946/viewspace-2911178/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於INNODB SYSTEM RECORD infimum和supremum的學習和實驗研究REM
- supremum pseudo-record鎖影響REM
- Mysql innodb引擎(二)鎖MySql
- MySQL死鎖案例二(自增列導致死鎖)MySql
- RAC 鎖管理與鎖問題的定位(二)
- Java鎖之ReentrantLock(二)JavaReentrantLock
- 【MySQL】死鎖案例之二MySql
- InnoDB事務鎖之行鎖-insert二級索引加鎖原理圖索引
- InnoDB儲存引擎鎖機制(二、 鎖的型別)儲存引擎型別
- 分散式鎖實現(二):Zookeeper分散式
- 【MySQL】InnoDB鎖機制之二MySql
- 深入理解Java中的鎖(二)Java
- Go 互斥鎖 Mutex 原始碼分析(二)GoMutex原始碼
- 多執行緒(二)、內建鎖 synchronized執行緒synchronized
- 日常運維之TX鎖處理(二)運維
- Oracle中的死鎖Dead Lock(二)Oracle
- MySQL優化篇系列文章(二)——MyISAM表鎖與InnoDB鎖問題MySql優化
- Oracle多粒度封鎖機制研究二(zt)Oracle
- Redis分散式鎖(二):鎖超時後導致多個執行緒獲得鎖的解決方案Redis分散式執行緒
- MySQL鎖(讀鎖、共享鎖、寫鎖、S鎖、排它鎖、獨佔鎖、X鎖、表鎖、意向鎖、自增鎖、MDL鎖、RL鎖、GL鎖、NKL鎖、插入意向鎖、間隙鎖、頁鎖、悲觀鎖、樂觀鎖、隱式鎖、顯示鎖、全域性鎖、死鎖)MySql
- 資料庫基礎知識詳解二:樂觀/悲觀鎖、封鎖級別、三級封鎖協議以及兩段鎖協議資料庫協議
- 深入分析synchronized原理和鎖膨脹過程(二)synchronized
- Redis應用(二) --分散式鎖以及壓測介紹Redis分散式
- Linux下關於互斥鎖及同步的移植(二)Linux
- 自旋鎖、阻塞鎖、可重入鎖、悲觀鎖、樂觀鎖、讀寫鎖、偏向所、輕量級鎖、重量級鎖、鎖膨脹、物件鎖和類鎖物件
- 【鎖機制】共享鎖、排它鎖、悲觀鎖、樂觀鎖、死鎖等等
- Java鎖?分散式鎖?樂觀鎖?行鎖?Java分散式
- Java鎖最全詳解:樂觀鎖/悲觀鎖+公平鎖/非公平鎖+獨享鎖/共享鎖Java
- 給智慧網聯二手車個人資訊加把鎖BGH
- 給智慧網聯二手車個人資訊加把鎖VQ
- MySQL鎖(二)表鎖:為什麼給小表加欄位會導致整個庫掛掉?MySql
- Java 中15種鎖的介紹:公平鎖,可重入鎖,獨享鎖,互斥鎖,樂觀鎖,分段鎖,自旋鎖等等Java
- 全域性鎖、表鎖、行鎖
- MySQL鎖(樂觀鎖、悲觀鎖、多粒度鎖)MySql
- java 中的鎖 -- 偏向鎖、輕量級鎖、自旋鎖、重量級鎖Java
- InnoDB常用鎖總結(行鎖、間隙鎖、臨鍵鎖、表鎖)
- MySQL/InnoDB中,樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解MySql
- InnoDB事務鎖之行鎖-insert加鎖-隱式鎖加鎖原理