mysqlinnodblock機制原理

一.lock 和 latch
latch 一般稱為閂鎖，目的是用來保證并發(fā)線程操作臨界資源的正確性，無死鎖檢測機制。分為：mutex(互斥量)，rwlock(讀寫鎖)
lock 的對象是事務(wù)，用來鎖定的數(shù)據(jù)庫中的對象。
mysql innodb lock機制原理

二.lock 種類
1.行級鎖
共享鎖(S lock)：允許事務(wù)讀取一行數(shù)據(jù)
排他鎖(X lock)：允許事務(wù)修改或刪除一行數(shù)據(jù)
2.表級鎖
意向共享鎖(IS lock):在對行加S鎖之前，先對其表追加IS鎖
意向排他鎖(IX lock):在對行加X鎖之前，先對其表追加IX鎖

表級意向鎖和行級鎖的兼容性：
mysql innodb lock機制原理

三.鎖的應(yīng)用場景
1.一致性非鎖定讀
mysql innodb lock機制原理
2.一致性鎖定讀
通過select * from table for update; 或 select * from table lock in share mode; 來鎖定讀取數(shù)據(jù)，在數(shù)據(jù)讀取過程中其他事務(wù)不能修改該數(shù)據(jù)。

3.自增長與鎖
含有自增長值的表，都有一個自增長計數(shù)器，當(dāng)對該表進(jìn)行插入操作時，執(zhí)行如下語句來得到計數(shù)器的值。
select max(auto_inc_col) from table for update;
該鎖不是事務(wù)完成之后才釋放，而是insert命令執(zhí)行完成后就釋放該鎖。

MySQL5.1.22之前，該模式對于有自增值列的表的并發(fā)插入性能較差。
mysql5.1.22開始，innodb提供了一種輕量級的互斥量的自增長實現(xiàn)機制，這種機制大大提高了自增長值的插入性能。
相關(guān)參數(shù)，innodb_autoinc_lock_mode,默認(rèn)值為1
mysql innodb lock機制原理

4.外鍵和鎖
在innodb存儲引擎下，外鍵列如果沒有顯式的建立index，mysql會為該列自動添加index，避免發(fā)生表鎖。
對于外鍵值的插入和更新，會先select父表，但該select操作并不是一致性非鎖定讀，而是一致性鎖定讀（對父表追加S鎖）。因此當(dāng)父表被其他事務(wù)加上X鎖時，子表的操作會被阻塞。

  • --主表
  • CREATE TABLE `wwj`.`t1` (
  •   `deptno` INT NOT NULL,
  •   `deptname` VARCHAR(45) NOT NULL,
  •   `address` VARCHAR(45) NOT NULL,
  •   PRIMARY KEY (`deptno`));

  • --子表
  •  CREATE TABLE `wwj`.`t2` (
  •   `empno` INT NOT NULL,
  •   `empname` VARCHAR(45) NOT NULL,
  •   `age` INT NOT NULL,
  •   `deptno` INT NOT NULL,
  •   PRIMARY KEY (`empno`),
  •   INDEX `deptno_idx` (`deptno` ASC),
  •   CONSTRAINT `deptno`
  •     FOREIGN KEY (`deptno`)
  •     REFERENCES `wwj`.`t1` (`deptno`)
  •     ON DELETE NO ACTION
  •     ON UPDATE NO ACTION);

  • insert into wwj.t1 values(1,'it','北京');
  • insert into wwj.t1 values(2,'product','天津');
  • insert into wwj.t1 values(3,'haha','上海');
    • mysql innodb lock機制原理

  • mysql> select * from information_schema.innodb_locks\G;
  • *************************** 1. row ***************************
  • lock_id: 1303:26:3:2
  • lock_trx_id: 1303
  • lock_mode: S
  • lock_type: RECORD
  • lock_table: `wwj`.`t1`
  • lock_index: PRIMARY
  • lock_space: 26
  • lock_page: 3
  • lock_rec: 2
  • lock_data: 1
  • *************************** 2. row ***************************
  • lock_id: 1298:26:3:2
  • lock_trx_id: 1298
  • lock_mode: X
  • lock_type: RECORD
  • lock_table: `wwj`.`t1`
  • lock_index: PRIMARY
  • lock_space: 26
  • lock_page: 3
  • lock_rec: 2
  • lock_data: 1
  • 2 rows in set, 1 warning (0.00 sec)

    • 四.鎖的算法
    行鎖的三種算法：
    1.record lock
    單個記錄上的鎖

    2.gap lock
    間隙鎖，鎖定一個范圍，但不包含記錄本身

    3.next-key lock
    record lock+gap lock 鎖定一個范圍，但不包含記錄本身

    --場景模擬
  • CREATE TABLE `wwj`.`t3` (
  •   `idt3` INT NOT NULL,
  •   `idt4` INT NOT NULL,
  •   PRIMARY KEY (`idt3`),
  •   INDEX `idx-1` (`idt4` ASC));

  •  insert into wwj.t3 values(1,10); 
  •  insert into wwj.t3 values(3,30); 
  •  insert into wwj.t3 values(5,50);

  • mysql> select * from wwj.t3;
  • +------+------+
  • | idt3 | idt4 |
  • +------+------+
  • | 1 | 10 |
  • | 3 | 30 |
  • | 5 | 50 |
  • +------+------+
    • 唯一索引的鎖定范圍
    mysql innodb lock機制原理
    因為idt3上有唯一索引，因此鎖定的只是idt3=3這個值，而不是(1,3)這個范圍，即鎖定由next-key lock降級為record lock

    輔助索引的鎖定范圍
    mysql innodb lock機制原理

    五.一條sql的加鎖范圍
    對于各種情況下加鎖的分析
  • mysql> show full processlist;
  • +----+------+-----------+------+---------+------+----------+-----------------------+
  • | Id | User | Host | db | Command | Time | State | Info |
  • +----+------+-----------+------+---------+------+----------+-----------------------+
  • | 11 | root | localhost | NULL | Sleep | 99 | | NULL |
  • | 12 | root | localhost | NULL | Sleep | 81 | | NULL |
  • | 13 | root | localhost | NULL | Query | 0 | starting | show full processlist |
  • +----+------+-----------+------+---------+------+----------+-----------------------+
  • 殺掉線程:
  • KILL [CONNECTION | QUERY] thread_id
  • kill 13 --殺掉線程連接
  • kill query 13 --殺掉正在執(zhí)行的語句，保留連接

  • mysql> show engine innodb status\G;
  • ------------
  • TRANSACTIONS
  • ------------
  • Trx id counter 1296
  • Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
  • History list length 2
  • LIST OF TRANSACTIONS FOR EACH SESSION:
  • ---TRANSACTION 421324408397424, not started
  • 0 lock struct(s), heap size 1136, 0 row lock(s)
  • ---TRANSACTION 1295, ACTIVE 396 sec inserting
  • mysql tables in use 1, locked 1
  • LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
  • MySQL thread id 12, OS thread handle 139848225883904, query id 61 localhost root executing
  • insert into wwj.t3 select 4,20
  • ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
  • RECORD LOCKS space id 26 page no 4 n bits 72 index idx-1 of table `wwj`.`t3` trx id 1295 lock_mode X locks gap before rec insert intention waiting
  • Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  •  0: len 4; hex 8000001e; asc ;;
  •  1: len 4; hex 80000003; asc ;;

  • ---------------------
  • TRANSACTION 1294, ACTIVE 449 sec
  • 4 lock struct(s), heap size 1136, 3 row lock(s)
  • MySQL thread id 11, OS thread handle 139848226150144, query id 50 localhost root
  • Trx read view will not see trx with id >= 1294, sees < 1294


  • mysql> select * from information_schema.INNODB_LOCK_WAITS;
  • +-------------------+-------------------+-----------------+------------------+
  • | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
  • +-------------------+-------------------+-----------------+------------------+
  • | 1302 | 1302:26:4:3 | 1301 | 1301:26:4:3 |
  • +-------------------+-------------------+-----------------+------------------+
  • 1 row in set, 1 warning (0.00 sec)

  • mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from information_schema.INNODB_LOCKs;
  • +-------------+-------------+-----------+-----------+------------+------------+
  • | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index |
  • +-------------+-------------+-----------+-----------+------------+------------+
  • | 1302:26:4:3 | 1302 | X,GAP | RECORD | `wwj`.`t3` | idx-1 |
  • | 1301:26:4:3 | 1301 | X | RECORD | `wwj`.`t3` | idx-1 |
  • +-------------+-------------+-----------+-----------+------------+------------+
  • 2 rows in set, 1 warning (0.00 sec)

  • SELECT      
  •         p2.`HOST` Blockedhost,  #被阻塞方host
            p2.`USER` BlockedUser,  #被阻塞方用戶
            r.trx_id BlockedTrxId,  #被阻塞方事務(wù)id   
            r.trx_mysql_thread_id BlockedThreadId,      #被阻塞方線程號
            TIMESTAMPDIFF(     
                SECOND,     
                r.trx_wait_started,     
                CURRENT_TIMESTAMP     
            ) WaitTime,   #等待時間  
            r.trx_query BlockedQuery,         #被阻塞的查詢   
            l.lock_table BlockedTable,        #阻塞方鎖住的表  
            m.`lock_mode` BlockedLockMode,    #被阻塞方的鎖模式
            m.`lock_type` BlockedLockType,    #被阻塞方的鎖類型(表鎖還是行鎖)
            m.`lock_index` BlockedLockIndex,  #被阻塞方鎖住的索引
            m.`lock_space` BlockedLockSpace,  #被阻塞方鎖對象的space_id
            m.lock_page BlockedLockPage,      #被阻塞方事務(wù)鎖定頁的數(shù)量
            m.lock_rec BlockedLockRec,        #被阻塞方事務(wù)鎖定行的數(shù)量
            m.lock_data BlockedLockData,      #被阻塞方事務(wù)鎖定記錄的主鍵值
            p.`HOST` blocking_host,           #阻塞方主機
            p.`USER` blocking_user,           #阻塞方用戶
            b.trx_id BlockingTrxid,           #阻塞方事務(wù)id
            b.trx_mysql_thread_id BlockingThreadId,  #阻塞方線程號
            b.trx_query BlockingQuery,        #阻塞方查詢
            l.`lock_mode` BlockingLockMode,   #阻塞方的鎖模式
            l.`lock_type` BlockingLockType,   #阻塞方的鎖類型(表鎖還是行鎖)
            l.`lock_index` BlockingLockIndex, #阻塞方鎖住的索引
            l.`lock_space` BlockingLockSpace, #阻塞方鎖對象的space_id
            l.lock_page BlockingLockPage,     #阻塞方事務(wù)鎖定頁的數(shù)量
            l.lock_rec BlockingLockRec,       #阻塞方事務(wù)鎖定行的數(shù)量
            l.lock_data BlockingLockData,     #阻塞方事務(wù)鎖定記錄的主鍵值     
           IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx #阻塞方事務(wù)空閑的時間               
        FROM     
            information_schema.INNODB_LOCK_WAITS w     
        INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id     
        INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id     
        INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id` 
          INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id` 
        INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id    
     INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id  
        ORDER BY     
            WaitTime DESC;

    • 參考書籍:
    MySQL技術(shù)內(nèi)幕：InnoDB存儲引擎

    成都創(chuàng)新互聯(lián)公司從2013年成立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元豐潤做網(wǎng)站,已為上家服務(wù),為豐潤各地企業(yè)和個人服務(wù),聯(lián)系電話:028-86922220

    當(dāng)前標(biāo)題：mysqlinnodblock機制原理
    本文路徑：http://bm7419.com/article32/jjiopc.html

    成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站收錄、定制開發(fā)、面包屑導(dǎo)航、定制網(wǎng)站、網(wǎng)頁設(shè)計公司、標(biāo)簽優(yōu)化

    廣告

    聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

    網(wǎng)站建設(shè)網(wǎng)站維護(hù)公司